Microsoft Excel 301 - Working With Tables and Pivot Charts

0 %
100 %
Information about Microsoft Excel 301 - Working With Tables and Pivot Charts
Product-Training-Manuals

Published on July 20, 2018

Author: Netcomlearning

Source: authorstream.com

slide 1: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning Microsoft Excel 301: Working With Tables and Pivot Charts Richard Doelker NetCom Learning slide 2: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning Microsoft Excel 301: Working With T ables 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. slide 3: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning 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 slide 4: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning How to create tables What is an Excel Table • 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. slide 5: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning Preparing Your Data Before you create the formatted Excel Table follow these guidelines for organizing your data. 1. 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. 2. In the first row of the list each column should contain a short descriptive and unique heading. 3. Each column in the list should contain one type of data such as dates currency or text. 4. 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. 5. The list should have no blank rows within it and no completely blank columns. 6. 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. slide 6: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning slide 7: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning Creating workbooks and working with worksheets Creating an Excel Table After your data is organized as described above youre ready to create the formatted Table. 1. Select a cell in the list of data that you prepared. 2. On the Ribbon click the Insert tab. 3. In the Tables group click the Table command. 4. 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. 5. Click OK to accept these settings. slide 8: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning 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. slide 9: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning Create an Excel Table With Specific Style When you create a table with the Table command on the Ribbons 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: 1. Select a cell in the list of data that you prepared. 2.On the Ribbon click the Home tab. 3.In the Styles group click Format as Table 4.Click on the Style that you want to use slide 10: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning How to create charts using the table data 10 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 youll need to understand how different charts are used. In addition to chart types youll need to understand how to read a chart. Charts contain several different elements or parts that can help you interpret the data. slide 11: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning 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 well select cells A1:F6. 2. From the Insert tab click the desired Chart command. In our example well select Column. 3. Choose the desired chart type from the drop-down menu. slide 12: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning The selected chart will be inserted in the worksheet. If youre not sure which type of chart to use the Recommended Charts command will suggest several different charts based on the source data. slide 13: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning Understanding what are Pivot T ables 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 1. Check the Source Data 2. Set a Goal 3. Think about the Layout slide 14: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning 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. slide 15: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning 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. 1. Select any cell in the source data table. 2. On the Ribbon click the Insert tab. 3. In the Tables group click Recommended PivotTables. 4. In the Recommended PivotTables window scroll down the list to see the suggested layouts. Click on a layout to see a larger view. 5. Click on the layout that you want to use then click OK. slide 16: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning 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. slide 17: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning You can change the layout of the pivot table after its been created. Well 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. slide 18: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning 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: 1. Create the pivot table and then click any cell in the pivot table on which you want to base the chart. 2. Click the PivotChart button in the Tools group of the PivotTable Tools Options tab. slide 19: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning 3. Click the thumbnail of the type of chart you want to create. 4. 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. slide 20: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning slide 21: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning 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 you’ve added to the chart as well as select a new background color for your chart. slide 22: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning Recorded Webinar Video To watch the recorded webinar video for live demos please access the link: https://bit.ly/2uq2Zbb slide 23: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning About NetCom Learning slide 24: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning 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 slide 25: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning 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 slide 26: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning 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. slide 27: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning Follow Us On: slide 28: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning slide 29: www.netcomlearning.com | infonetcomlearning.com | 888 563 8266 ©1998-2018 NetCom Learning THANKYOU

Add a comment

Related presentations