Microsoft Excel 301 - Working With Tables and Pivot Charts - PowerPoint PPT Presentation

View by Category
About This Presentation
Title:

Microsoft Excel 301 - Working With Tables and Pivot Charts

Description:

Watch this recorded webinar session to master data analysis using Pivot tables and charts in Microsoft Excel. – PowerPoint PPT presentation

Number of Views:27
Slides: 30
Provided by: Username withheld or not provided

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: 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
(888) 563 8266
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
(888) 563 8266
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
(888) 563 8266
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
(888) 563 8266
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
(888) 563 8266
6
www.netcomlearning.com info_at_netcomlearning.com
(888) 563 8266
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
(888) 563 8266
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
(888) 563 8266
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
(888) 563 8266
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 (888) 563 8266 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
(888) 563 8266
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
(888) 563 8266
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
(888) 563 8266
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
(888) 563 8266
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
(888) 563 8266
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
(888) 563 8266
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
(888) 563 8266
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
(888) 563 8266
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
(888) 563 8266
20
www.netcomlearning.com info_at_netcomlearning.com
(888) 563 8266
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
(888) 563 8266
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
(888) 563 8266
23
About NetCom Learning
www.netcomlearning.com info_at_netcomlearning.com
(888) 563 8266
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
(888) 563 8266
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
(888) 563 8266
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
(888) 563 8266
27
Follow Us On
www.netcomlearning.com info_at_netcomlearning.com
(888) 563 8266
28
www.netcomlearning.com info_at_netcomlearning.com
(888) 563 8266
29
THANK YOU !!!
www.netcomlearning.com info_at_netcomlearning.com
(888) 563 8266
About PowerShow.com