Microsoft Excel 301 - Working With Tables and Pivot Charts

1
Microsoft Excel 301 Working With Tables and
Pivot Charts
Richard Doelker NetCom Learning
www.netcomlearning.com info_at_netcomlearning.com
[phone]
2
Microsoft Excel 301 Working With Tables And
Pivot Charts
Excel is the powerhouse of spreadsheet software
most commonly used for business applications and
thereby making your job easier and less
stressful. The tool that is capable of analyzing
data, performing calculations, presenting
information in advanced dashboards. It also
integrates information from different
programs. This webinar will help you gain the
necessary skills to create, edit, format Excel
Tables and Pivot Tables.
www.netcomlearning.com info_at_netcomlearning.com
[phone]
3
About The Course

• How to create tables
• How to add data and use layouts and table
  designer
• How to create charts using the table data
• Understanding what are Pivot Tables
• How to create and update Pivot Tables and
  generate Pivot Charts
• QA session with the speaker

www.netcomlearning.com info_at_netcomlearning.com
[phone]
4
What is an Excel Table?
How to create tables

• In Excel 2007, and later versions, you can use
  the Table command to convert a list of data into
  a formatted Excel Table.
• Tables have many features, such as sorting and
  filtering, that will help you organize and view
  your data.
• An Excel Table makes an excellent source for a
  pivot table, so you should use this feature if
  you plan to create a Pivot Table from the data.

www.netcomlearning.com info_at_netcomlearning.com
[phone]
5
Preparing Your Data
Before you create the formatted Excel Table,
follow these guidelines for organizing your data.

• The data should be organized in rows and columns,
  with each row containing
• information about one record, such as a sales
  order, or inventory transaction.
• In the first row of the list, each column should
  contain a short, descriptive and unique heading.
• Each column in the list should contain one type
  of data, such as dates, currency, or text.
• Each row in the list should contain the details
  for one record, such as a sales order. If
• possible, include a unique identifier for each
  row, such as an order number.
• The list should have no blank rows within it, and
  no completely blank columns.
• The list should be separated from any other data
  on the worksheet, with at least one blank row
  and one blank column between the list and the
  other data.

www.netcomlearning.com info_at_netcomlearning.com
[phone]
6
www.netcomlearning.com info_at_netcomlearning.com
[phone]
7
Creating workbooks and working with worksheets

• Creating an Excel Table
• After your data is organized, as described above,
  you're ready to create the formatted Table.
• Select a cell in the list of data that you
  prepared.
• On the Ribbon, click the Insert tab.
• In the Tables group, click the Table command.
• In the Create Table dialog box, the range for
  your data should automatically appear, and the
  My table has headers option is checked. If
  necessary, you can adjust the range, and check
  box.
• Click OK to accept these settings.

www.netcomlearning.com info_at_netcomlearning.com
[phone]
8
How to add data and use layouts and table designer

• By default an Excel table will expand
  automatically, and fill
• formulas down to the last row.
• Add new data in the row immediately below a
  table, or in the column to its immediate right,
  and the table expands automatically, to include
  that new data.
• Enter a formula in the first row of a blank
  column, that formula fills down to all the
  remaining rows, as soon as you press Enter
• If Excel tables are not expanding automatically
  on your computer, you can adjust the settings
  either manually, or with VBA programming.

www.netcomlearning.com info_at_netcomlearning.com
[phone]
9
Create an Excel Table With Specific Style

• When you create a table with the Table command on
  the Ribbon's Insert tab, the table retains any
  formatting that it currently has, and the
  default Table Style is applied.
• If you want to apply a specific table style when
  creating an Excel Table
• Select a cell in the list of data that you
  prepared.
• On the Ribbon, click the Home tab.
• In the Styles group, click Format as Table
• Click on the Style that you want to use

www.netcomlearning.com info_at_netcomlearning.com
[phone]
10
How to create charts using the table data
Understanding charts Excel has several different
types of charts, allowing you to choose the one
that best fits your data. In order to use charts
effectively, you'll need to understand how
different charts are used.
In addition to chart types, you'll need to
understand how to read a chart. Charts contain
several different elements, or parts, that can
help you interpret the data. www.netcomlearning.co
m info_at_netcomlearning.com [phone] 10
1998-2018 NetCom Learning
11
To insert a chart

1. Select the cells you want to chart, including
   the column titles and row labels. These cells
   will be the source data for the chart. In our
   example, we'll select cells A1F6.
2. From the Insert tab, click the desired Chart
   command. In our example, we'll select Column.
3. Choose the desired chart type from the drop-down
   menu.

www.netcomlearning.com info_at_netcomlearning.com
[phone]
12
The selected chart will be inserted in the
worksheet.
If you're not sure which type of chart to use,
the Recommended Charts command will suggest
several different charts based on the source data.
www.netcomlearning.com info_at_netcomlearning.com
[phone]
13
Understanding what are Pivot Tables

• Before You Build a Pivot Table
• A pivot table is a quick way to show a summary
  for many rows of data. It is a flexible
  alternative to a structured worksheet report that
  has typed headings, and formulas to calculate
  the totals.
• There are a few things to do though, before you
  build a pivot table. Being prepared can save you
  lots of time and troubleshooting later!
• Check the Source Data
• Set a Goal
• Think about the Layout

www.netcomlearning.com info_at_netcomlearning.com
[phone]
14
Preparing Your Pivot Table Data
Before you create a pivot table, organize your
data into rows and columns, and create an Excel
Table. In this example the source data contains
information about food sales, in two regions --
East and West.
www.netcomlearning.com info_at_netcomlearning.com
[phone]
15
Creating a Pivot Table

• After your source data is prepared, you can
  create a pivot table. First, see which pivot
  table layouts are suggested by Excel.
• Select any cell in the source data table.
• On the Ribbon, click the Insert tab.
• In the Tables group, click Recommended
  PivotTables.

• In the Recommended PivotTables window, scroll
  down the list, to see
• the suggested layouts. Click on a layout, to see
  a larger view.
• Click on the layout that you want to use, then
  click OK.

www.netcomlearning.com info_at_netcomlearning.com
[phone]
16
Modifying the Pivot Table
A pivot table is created in your workbook, on a
new sheet, in the layout that you selected. When
you select a cell within the pivot table, a
PivotTable Field List appears, at the right of
the worksheet.
www.netcomlearning.com info_at_netcomlearning.com
[phone]
17
You can change the layout of the pivot table,
after it's been created. We'll add the TotalPrice
field to the pivot table. 1. In the PivotTable
Field List, add a check mark to the TotalPrice
field. The TotalPrice field is automatically
added to the pivot table, in the Values area, as
Sum of TotalPrice.
www.netcomlearning.com info_at_netcomlearning.com
[phone]
18

• After you create a pivot table in Excel, you can
  create a pivot chart to display its summary
  values graphically. You also can format a pivot
  chart to improve its appearance. You can use any
  of the chart types available with Excel when you
  create a pivot chart.
• CREATE A PIVOT CHART
• Follow these steps to create a pivot chart based
  on an existing pivot table in a worksheet
• Create the pivot table and then click any cell in
  the pivot table on which you
• want to base the chart.
• Click the PivotChart button in the Tools group of
  the PivotTable Tools Options tab.

www.netcomlearning.com info_at_netcomlearning.com
[phone]
19

• Click the thumbnail of the type of chart you want
  to create.
• Click OK.
• MOVE A PIVOT CHART TO ITS OWN SHEET
• You may find it easier to customize and work with
  a pivot chart if you move the chart to its own
  chart sheet in the workbook.
• To do so, click the Move Chart button on the
  PivotChart Tools Design tab, click the
• New Sheet button in the Move Chart dialog box,
  and then click OK.

www.netcomlearning.com info_at_netcomlearning.com
[phone]
20
www.netcomlearning.com info_at_netcomlearning.com
[phone]
21

• FORMAT A PIVOT CHART
• As soon you create a pivot chart, Excel displays
  these items in the worksheet
• Pivot chart using the type of chart you selected
  that you can move and resize as needed
  (officially
• known as an embedded chart).
• PivotChart Tools contextual tab divided into four
  tabs Design, Layout, Format, and Analyze
• each with its own set of buttons for
  customizing and refining the pivot chart.
• The command buttons on the Design, Layout, and
  Format tabs attached to the PivotChart Tools
  contextual tab make it easy to further format and
  customize your pivot chart
• Design tab Use these buttons to select a new
  chart style for your pivot chart or even a brand
  new chart type.
• Layout tab Use these buttons to further refine
  your pivot chart by adding chart titles, text
  boxes, and gridlines.
• Format tab Use these buttons to refine the look
  of any graphics youve added to the chart as well
• as select a new background color for your chart.

www.netcomlearning.com info_at_netcomlearning.com
[phone]
22
Recorded Webinar Video
To watch the recorded webinar video for live
demos, please access the link https//bit.ly/2uq2
Zbb
www.netcomlearning.com info_at_netcomlearning.com
[phone]
23
About NetCom Learning
www.netcomlearning.com info_at_netcomlearning.com
[phone]
24
Recommended Courses
Excel Level 1 (2016/2013/2010) - Class
scheduled on Aug 13 Excel Level 2
(2016/2013/2010) - Class scheduled on Aug 14
Excel Level 3 (2016/2013/2010) - Class scheduled
on July 15 20778 Analyzing Data with Power BI
- Class scheduled on Aug 13 Excel 2016 Data
Analysis with PivotTables EDX11001 Analyzing
and Visualizing Data with Excel - DAT206x
EDX11014 Essential Statistics for Data Analysis
using Excel - DAT222x EDX11028 Introduction
to Data Analysis using Excel - DAT205x
www.netcomlearning.com info_at_netcomlearning.com
[phone]
25
Architecture Insights for Microsoft Azure
Windows Server 2016 Advanced Networking Features
SQL Server 2017 Demo Exciting New Features
Capabilities How to Hunt for Security Threats
Creating Social Media Graphics in Photoshop CC
Project Management Developing Project Schedules
and Budgets How to Configure Networking in
Windows 10 Devices ASP.NET Functions on
Microsoft Azure Getting Started With CompTIA
PenTest PowerPoint 2016 10 Tips to Master
Presentations Hands-On Power BI for Data
Visualization
www.netcomlearning.com info_at_netcomlearning.com
[phone]
26
Promotions
With options ranging from Cloud, Security,
Networking, Data AI, Design Multimedia,
Business Application, Application Development,
Business Process and eLearning we offer the
hottest training courses to help you advance your
skills in different areas. All classes are
delivered in in-person Instructor-led Classroom
or Live Online training modes.
www.netcomlearning.com info_at_netcomlearning.com
[phone]
27
Follow Us On
www.netcomlearning.com info_at_netcomlearning.com
[phone]
28
www.netcomlearning.com info_at_netcomlearning.com
[phone]
29
THANK YOU !!!
www.netcomlearning.com info_at_netcomlearning.com
[phone]