Excel Mastery: Crush Your Spreadsheets Like a Pro!

Advanced Features in Excel

Part 5.1

Using PivotTables and PivotCharts

My introduction to PivotCharts

So, I am just going to be honest. Before starting this series, I had never used PivotCharts, like, not at all. Not even once. I had heard of them, but that was it. And by heard of them, I mean that I have heard the name. As far as what they did, for all I knew they could have been a fictional magic phrase from the mind of JK Rowling.

So, I started my research where everyone should when they are looking to learn something in Excel. For those of you who were not yet born at the turn of the millennia, forums are an online place where people go to share information and ask questions. A wonderful forum that is focused on Excel is one called Mr. Excel. I am not sponsored by them at all, but I do post on their platform quite frequently when it comes to areas that I am knowledgeable about (mainly VBA).

On Mr. Excel and asked people about PivotCharts, and, pretty much, everyone told me the same thing. Just open up, plug in some data, and see what you get.

So, today, I am going to cut out the middle man for you and give you just the raw information I have learned. What is a PivotChart? What kind of information do you need? How much data? How do you use a PivotChart? And, most importantly, why would you ever need one?

A Quick Overview: What is a PivotChart and What are they used for?

Pivot charts are Excels way to visualize and analyze data in an interactive way.

Its basically an interactive dashboard without VBA, though it can be quite difficult if you are wanting a certain look or feel that is not pre-cut for you.

Pivot charts are used to visually summarize, analyze and present large sets of data in a dynamic and interactive way. They allow you to quickly and easily create charts that show different aspects of your data, such as trends, patterns, and relationships.

How to Make a PivotChart?

The Data

As a bonus, I will be supplying us with some sample data that we can use to create our first Pivot Chart.

You can use this data and follow along.

–   –   –

Side note: This data was formatted as you see it with CelTools. CelTools has a feature that will allow you to select a range and convert that range directly into HTML and paste it into your website. This is, as far as I have found, the only way to get an exact copy of an Excel formatted cell range with colors, fonts and sizes into a webpage or blog post. If you would like to learn more about this feature, you can give CelTools a test run by clicking here.

–   –   –

This table includes four columns: “Month“, “Product Category“, “Sales Region“, and “Sales“. It contains data for three months (Jan, Feb, and Mar), two product categories (Electronics and Clothing), and two sales regions (North and South). The “Sales” column contains the sales figures for each combination of month, product category, and sales region.

We are going to use this data to create a pivot chart that shows the total sales by month and product category, or by sales region and product category, or any other combination that you find interesting or useful.

But, before you can use, or create, a Pivot Chart we need to have the data in a Pivot Table.

Making, or, Not Making a PivotTable

From my practice, I found that I get better results when I first create a table before trying to create a pivot… anything. Supposedly you can skip this step if you have no blank cells in the data by clicking on the PivotTable” button under the insert tab. This was hit-and-miss for me, sometimes it worked, most of the time not. For me, the best results was just to create a table first.

To create a table, select your range of data and click ‘Table’ inside of the insert tab. You will be given prompts to create the table, which, if you are using the data provided above, then you will want to select ‘data has headers’ and click OK.

If you would like some extra formatting options when it comes to tables, you can try the Table options inside of CelTools, which also includes lookup options as well.

Now that we have a table create, Excel will automatically know where the range is of the data we have. Click and select any cell inside the data table you just created and click the “Create PivotTable” or “Create PivotChart” dialog box inside of the Insert tab. The range will automatically populate for you, so now all you have to do is select where you want to place the PivotTable. I always choose to have it place in a new sheet, or you can simply click somewhere on an existing sheet and the address with automatically gather for you. Then click “OK“.

Authors note: If you are placing data in a sheet that already has a PivotTable or PivotChart, then you will want to have the data aligned either horizontally (as in left to right), or have it vertical in a way so data will not overlap.

Customize the Data

Once you have created the PivotTable, you can start to customize it by dragging and dropping fields into the “Rows”, “Columns”, and “Values” areas.

This is what took me a while to wrap my head around. You see, depending on the chart you want, you will want to have different variables in different areas.

Follow along with me and I will show you how to make a PivotChart like the one showed here:

The “Rows” and “Columns” areas are used to define the categories and subcategories of your data, while the “Values” area is used to calculate and summarize the data.

For this example we will drag-and-drop ‘Product Category’ into the Filters section, ‘Month’ into the ‘Legend (Series)’ and finally ‘Sales’ into the ‘Values’.

You should now have a column graph that shows the total amount of sales for each month, with January being the first column and March being the last.

I know what you are saying, ‘This is exactly the same as making a regular bar graph, but way more complicated? What is the point?’. Well, this is where PivotCharts get the name ‘Pivot’ from.

What Makes the PivotChart Better?

Inside the graph itself we want to have the ability for the user to change settings on the graph to update the data as needed. To do this, click on the ‘Product Category’ that is in the top left of the graph. Inside this window select ‘Select Multiple Items’ and then checkmark whichever sold items you would like to look at. When you are ready, select OK and watch as the graph updates to your new values.

Now this was a very small section of data. Just imagine this on a massive scale. Yearly data comprised side my side with adjustable variables. The possibilities with these charts are endless, at least for a presentation. You can actively change these graphs as needed during the presentation too.

Want to change the graph type? That is easy to do. Just right click on the chart and select ‘Change Chart Type’. From here you can select any chart that you can use with the current Pivot setup.

What about if you want more interactivity and functionality? Let me introduce you to Sliders and Timelines.

But wait, there’s more!

If you love this free cheat sheet, we are offering our complete PDF Cheat Sheet Collection here!

This collection contains all of our concise Excel cheat sheets as well as two more PDFs exclusive to the bundle. That is a total of 15 pages that outlines every think you need to know to supercharge your Excel experience!

This Bundle includes:

* The Top 25 Excel Keyboard Shortcut Cheat Sheet
* The Ultimate Excel Guide: Top 80 Functions
* The Top 25 Excel Conditional Formatting Cheat Sheet
* The Ultimate Excel Guide: Top 41 Math Functions
* The Top 20 Advanced PIVOTTABLE Techniques **EXCLUSIVE**
* The Ultimate Excel Guide: Top 50 Macro Calls
* The Complete Excel Chart Cheat Sheet **EXCLUSIVE**

Purchase your copy here! For less than a cup of coffee you can have all of Excel’s secrets at your finger tips!

$4.99 – Buy Now Checkout Added to cart

---

Remember, it’s not just a cheat sheet; it’s your journey to Excel efficiency.

 

Slicers and Timelines

Slicers are a rowed tabulation of data that you can toggle on and off based on your needs. So, but clicking on the ‘Insert Slicer’, you can select Month and Product Category to have an even more interactive graph that automatically updates based on what the user clicks. Using this you can select to show some months, some products, or a mixture of them.

Timelines are just like Slicers, except while Slicers mainly work on data that are arranged vertically (or along the Y axis on the charts), Timelines are aligned horizontally (or across the X axis). They both serve the same purpose, allowing functionality and more interactivity with your data!

More practice

The examples that I have listed above are great for getting your toes wet, but it is a far cry from what is possible. I am sure that you don’t want to gamble learning how to do the more advanced functions on something important like your business data. So, why not use it on someone else’s business data?

Here I have attached a completely fictional sales rep sheet for a company that sells 6 products for a variety of prices ranging from $50 to $250. All of this data was generated at random and contains sales figures for two and a-half years.

Use this data to goof around and let me know what you come up with! There are lots of things you can do to really make your graphs stand off the page. Don’t forget to format your pages and use images to really make things pop.

Sample Data for PivotCharts and PivotTable

Conclusion

So there you have it – PivotTables and PivotCharts in a nutshell. With these advanced features, you’ll be able to navigate the Excel landscape like a pro. And who knows – you may even discover hidden treasures along the way.