To ensure your success in this course, you should have experience working with Excel and Pivot Tables. You should already understand spreadsheet concepts and be comfortable creating and analyzing basic Pivot Tables.
What You Will Learn
In particular you will be able to:
Prepare data for Pivot Table reporting and create Pivot Tables from various data sources
Analyze Data Using PivotTables
Work with Pivot Charts
About This Course: Technology advances have made it possible to store ever increasing quantities of data. Along with this, the need to analyze that data and gain actionable insight is greater than ever. You already have experience working with Excel and creating basic Pivot Tables to summarize data. However, Excel is capable of doing a lot more. Being able to harness the power of advanced Pivot Table features and create Pivot Charts will help you to gain a competitive edge. You will be able to summarize data for you to analyze, and organize the data in a way that can be meaningfully presented to others. This can result in data-driven business decisions that have a better chance for success for the organization.
Preparing Data and Creating PivotTables (2010, 2013 2016)
Excel is a powerful application for analyzing data; however, interpreting information from large datasets can be tricky. In this lesson, you will learn about more effective ways to interact with data through easy-to-read formats and the Excel’s ability to summarize large amounts of data quickly to enable analysis of items such as cost, performance, and general reporting. You will find out how to perform data analysis by reorganizing and summarizing data by using PivotTable. You will learn how to create it from any data source to achieve powerful and efficient data analysis in one report.
It is crucial that you take time to prepare your data before you create a PivotTable. That way you’ll save yourself time and effort when it comes to actually analyzing that data. Only clean,transactional data for your PivotTable allows you to be confident in your PivotTable results. This topic will show you how to ensure your data is in a format that will yield the desired results by effectively answering your questions. To create a PivotTable, you can use local or external data sources. Local data sources are within the current workbook, while external data sources are outside of the current workbook, such as other Excel workbooks, text files, Access tables, or SQL Server tables.
Create a PivotTable from a Local Data Source
You have now carefully prepared your data and are ready to move on to creating a PivotTable. This topic will show you how to do so by using local data sources within the current workbook which can include either tables or ranges of cells. To create a useful PivotTable, you might start by thinking about the types of questions you would like your raw data to answer. They will help you to determine how to design the structure of your PivotTables. Suppose your manager wants you to measure performance of a sales department, and he would like to know the total sales per project type for each sales rep. Excel takes your source data you have for Date, Rep, Region, Customer Number, Sale, and Project Type and turns it into an interactive layout of columns, rows, values, and filters, which allows you to manipulate data to meet your business analysis needs. With a PivotTable’s powerful functionality, you can drag and drop fields to look at information from different perspectives to get a fresh view of statistics. As you move fields around in the PivotTable, Excel automatically adjusts the report layout and recalculates totals, which can save you hours of analysis work.
Create a PivotTable from Multiple Local Data Sources
You now know how to create a PivotTable from one local data source, but often you have data in multiple spreadsheets that you’d like to summarize. And yes, you can accomplish this with a PivotTable. In this topic, you will create a PivotTable from multiple local data sources. You can use the Excel Data Model to integrate data from multiple tables. You will see how this enables you to build a relational data source directly in a workbook. Instead of having to use formulas to consolidate data, the Data Model contains a behind-the-scenes analytical cube. You can add data to the Data Model when you insert a PivotTable.
Create a PivotTable from an External Data Source
So far, you’ve been using Excel ranges or tables within a workbook as the source data for your PivotTables. You won’t always have source data in Excel. Many times, your source data is stored outside of Excel and you have to work with data from a variety of sources to create a PivotTable. In this topic, you will create a PivotTable from an external data source like other Excel workbooks, text files, and relational databases, such as Access and SQL Server.
Analyzing Data Using PivotTables
In many cases, a basic PivotTable will give you enough data summaries for your analysis, but what if you need to perform a more complex analysis? What if you need to filter your PivotTable, group certain data together, alter its formatting, or analyze data from multiple sources? This lesson will show you a variety of methods for presenting data in a user-friendly format to make interpreting data easier. In addition, you will gain the ability to quickly summarize and successfully analyze large sets of data.
Summarize PivotTable Data
When you create a PivotTable, Excel summarizes the data in certain ways. If you’re looking for more than a sum or a count of the items in your fields or want to total only certain data in your PivotTable, you can customize how your data is presented and calculated. Excel makes it easy to adjust the display of PivotTable data to meet your business analysis needs. In this topic, you will summarize PivotTable data in various ways.
Organize PivotTable Data
A PivotTable can be really useful when you need to be able to read it and make sense of it quickly, so that you can make meaningful decisions from the data. It does a great job of summarizing raw data when the data is presented in the best way for your analysis. If the data is not presented in the best way, then you might end up searching through rows of data to find the largest or smallest value. It would be quicker and easier to simply sort the data. In this topic, you will organize PivotTable data.
Filter PivotTable Data
By now you can summarize and organize your PivotTable data in different ways, so you are ready to move onto the next level – to filter your PivotTable data. This comes in handy when you are analyzing a subset of your data and want to ignore all data that is not relevant to your current analysis. Imagine you’re the owner of a spa and you’d like to see the data for just one of your therapists, Sue. You could drag the Therapist field to the FILTERS area of the PivotTable Fields task pane, and then use the drop-down menu to select Sue from the list of therapists. The PivotTable would then reflect the data for the appointments for which Sue was the therapist.
Format a PivotTable
After you’ve manipulated your PivotTable data, you’re ready to present it in the best way. When you format a PivotTable, it’s important to align the design efficiently to meet the business needs. Often, a well-constructed PivotTable can draw attention to issues and help springboard solutions. In this topic, you will format a PivotTable.
Refresh and Change PivotTable Data
You’ve already come so far by learning about different features of your PivotTable so that you can deliver an insightful analysis. But what if your source data changes? You may have new data from recent transactions, or maybe the data has been updated due to customers updating their addresses or phone numbers. How do you make sure that your PivotTable data is as up-to-date as your source data? By learning how to refresh and change your PivotTable data.
Working with PivotCharts
By presenting PivotTable data graphically through PivotCharts, you can help your audience tell at a glance what the data is showing. PivotCharts help you complete more effectively your tasks when you are analyzing data in similar ways and presenting that data at a high level to larger groups of people. Also, you are able to quickly see the data visually when compared to a PivotTable. The graphical interface makes it easy to notice patterns and trends. This can greatly reduce the time it takes to make important business decisions. In this lesson, you will learn how to create a PivotChart to represent data graphically based on an underlying PivotTable, as well as manipulate its data and format the chart itself. PivotCharts help you present data in a professional, user-friendly format and speed up the time needed for data analysis.
Create a PivotChart
When your PivotTable grows too big or when you’d like to present the information in your PivotTable to others, it can be helpful to turn that data into a chart. A PivotChart is an interactive, graphical representation of numeric values and relationships among those values. When you create a PivotChart from a PivotTable, you’re creating a graphical interface of the PivotTable. Examining data visually helps you to easily see the story in the data. Excel pulls the entire PivotTable layout into the chart, allowing you to interact with the data in a variety of ways, such as adding, removing, or filtering data.
Manipulate PivotChart Data
Instead of working with the PivotTable to alter your PivotChart, you could just work directly in the PivotChart. When you create a PivotChart, Excel automatically opens the PivotChart Fields task pane. It enables you to select the fields you want in your PivotChart and to organize its structure. Just like PivotTables, PivotCharts are dynamic. This means you can drag fields among the various areas of the PivotChart Fields task pane as necessary and your PivotChart will update automatically.
Format a PivotChart
With PivotCharts, you are summarizing your data visually, so it’s important to use layouts and colors that represent your data in ways that make it easy for users to interpret what the charts are portraying. In this topic, you will format a PivotChart. Chart elements are the individual objects that can appear on charts that convey some level of information about the chart’s data to a viewer. Chart elements can include axes, axis titles, a chart title, data labels, gridlines, trend lines, and a legend, among others. Each chart element serves a different role in visually communicating information about the data and trends.
Microsoft Excel Pivot Tables was last modified: February 2nd, 2017 by darrell