pivot table Archives - Page 2 of 2

How to Make Dashboards in Google Sheets

June 29, 2021

by howtoexcel with No Comment charts dashboard formatting google sheets pivot chart pivot table

A big advantage of using Google Sheets is that the data is readily accessible online and you don’t need to worry about if people are running different versions of it like you would with Excel. One of the areas where it may be lacking is in creating dashboards. Although you can incorporate slicers, they’re not as user-friendly or nice looking as what you would get in Excel. But in this post, I’ll go over how to make dashboards in Google Sheets quickly and easily.

Here is a sample of what my data set looks like. If you want to view the data plus the dashboard I created here, you can check out the Google Sheets file here.

Step one is to create some pivot tables. Like with Excel, I prefer to create a pivot table for each view that I want. I will set up four pivot tables, categorizing sales by:

Store
Salesperson
Product
Date

To keep things simple, you can put each one of those fields in the ROW section while the sales can be in the VALUES section:

When creating the pivot tables, be sure to un-check the option to Show totals (this is so that they don’t show up in the charts):

What you may want to do is create one pivot table and then copy and paste others, and just change the rows. One additional step you will need to do for the pivot table that contains the dates is to also group them by month. To do that, right-click on any of the dates and select Create Pivot Date Group:

Creating a pivot date group in Google Sheets.

Then, from the following menu, select Year-Month:

Different pivot date groups in Google Sheets.

This is how your pivot tables might look like once you are done:

Where you put these pivot tables isn’t important. The key is leaving enough space between them so that they don’t potentially overlap should your data get bigger. Otherwise, you will run into errors and have difficulty updating your data. Since my pivot tables won’t get any wider based on the selections I’ll make, there doesn’t need to be any extra columns between any of them.

Now that the pivot tables are set up, the next step is to set up the different charts for each of them. For the sales by store, I will create a pie chart to show the split among the stores:

Pie chart showing sales by store in Google Sheets.

The one thing you will want to pay attention to for each chart is the range. Since your pivot table could expand, it’s a good idea to make the range bigger than it needs to be, even if it will contain blank values. For example, changing this:

Default date range for chart in Google Sheets.

To this:

Expanded data range for chart in Google Sheets.

This will ensure that additional data gets picked up by the chart should your pivot table get bigger. This is also why it is important to ensure you don’t place any other pivot tables below one another. Ideally, you’ll want to keep them side by side rather one on top of the other.

For the pivot table that shows sales by salesperson, I’ll use a bar chart since the names can be long:

For the product sales, I’ll mix it up and have those as column charts:

And for the sales by date, I will set those up as a line chart:

I will also add a scorecard chart, using any of the pivot tables. For this, I just want to pull the total sales:

Now that these charts all set up, it’s just a matter of organizing how you want to see them on your worksheet:

The one thing missing to make this dynamic: slicers. To add slicers to all these pivot tables, click on any of them and click on the Data tab and select the Add a Slicer button:

Adding a slicer to a Google Sheets pivot table.

Then, select the columns you want to filter by:

Selecting the column to use in a slicer.

As long as you are referencing the correct data range, then the slicer will apply to all the pivot tables correctly. And now, if I add a slicer for the stores and only select stores A and B, my dashboard updates as follows:

One thing to remember when you are applying changes: don’t forget to click on the green OK button on the bottom, otherwise your selections won’t be applied:

Applying filters for slicers in Google Sheets.

If you liked this post on How to Make Dashboards in Google Sheets, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Convert a Summary Table in Excel Into a Pivot Table

June 6, 2020

by howtoexcel with No Comment advanced data entry macros pivot table vba

Often times, when you download a data table from somewhere it’s not in the format you need it to be. Tables are often in a summary format where you have months going down and years going across, or vice versa. It’s the end result of what you want a pivot table to look like, but you can’t easily turn that into a pivot table itself. Below, I’ll show you how to turn a summary table in Excel that looks like this:

Into this:

This format is much more Excel-friendly and one that you can easily convert into a pivot table.

Converting the table

The data I’m using is the same one that I used in an earlier post that went over transposing data. Transposing data, unfortunately, isn’t enough to make data workable if you want to convert it into a pivot table. You’ll want data to be in a tabular format so that there’s a header for the month, year, and value.

You could manually transpose one year at a time and copy the data one by one. But of course, that isn’t optimal at all. The good news is I’ve got a macro that can help you flip that data in one click. It will go through the painstaking process of reorganizing the data for you.

Here’s the code for the macro. You can just put it into a module (I’ll leave a template to download below if you aren’t comfortable doing this step yourself):

Sub flipdata()

Dim cl, nxtcl As Range
Dim lastcol, lastrow, firstcol, firstrow As Integer


'get total number of rows and columns in range
lastcol = Selection.End(xlToRight).Column
lastrow = Selection.End(xlDown).Row

'get first column and row
firstcol = Selection.Column
firstrow = Selection.Row

'assign output starting point
Set nxtcl = Cells(lastrow + 2, firstcol)

nxtcl = "Header 1"
nxtcl.Offset(0, 1) = "Header 2"
nxtcl.Offset(0, 2) = "Value"

Set nxtcl = nxtcl.Offset(1, 0)


'cycle through data

For yr = (firstrow + 1) To lastrow

    For mth = (firstcol + 1) To lastcol

        nxtcl = Cells(firstrow, mth)
        nxtcl.Offset(0, 1) = Cells(yr, firstcol)
        nxtcl.Offset(0, 2) = Cells(yr, mth)
        Set nxtcl = nxtcl.Offset(1, 0)

    Next mth

Next yr

End Sub

It will output the data a couple of rows below where your data ends. It’s important to select the entire range of data before running the macro since it will go through the range that you’ve selected, nothing else. And if there’s data below your selection, it will overwrite that.

After you’ve selected the data, then you run the macro. In my template, I’ve got a button that you can press that will do the job for you and then you’ll get something that looks like this:

Once in this format, you can easily create a pivot table:

If you’d like to download the file that contains the macro, it’s available here.

If you liked this post on how to convert a summary table in Excel into a pivot table, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

How to Make a Pivot Table in Google Sheets with Slicers

January 19, 2020

by howtoexcel with No Comment google sheets intermediate pivot table slicers

Google Sheets has come a long way in being a formidable alternative to Excel. While it may not have all of the same features as Excel, Google is adding to its functionality. Creating a pivot table with slicers is now a possibility in Google Sheets, and below, I’ll show you how you can do that with the online spreadsheeting program.

The basics: creating a pivot table in Google Sheets

To create a pivot table in Google Sheets involves about the same steps as it does in Excel: compiling and organizing your data set, and then creating the pivot table. Here’s a quick look of my sample data that I have ready to use:

Then, on the Data menu, select the option to create Pivot Table:

The next step is selecting where you to put your pivot table:

Menu to select where to create the new pivot table.

The default, a new worksheet, will often work the best. Although the layout looks a little different, the process remains the same with a blank pivot table being your starting point:

new pivot table created in google sheets

On the right-hand side of the page, you’ll see options to put fields into columns and rows, which is what you’re used to with Excel. Again, the main difference is the layout but the logic remains the same:

adding fields to a pivot table in google sheets

When clicking on the Add button, you’ll see options as to which fields to add, even having the option for a calculated field as well:

Adding a field to the values section of a pivot table on google sheets.

In my example, I’m going to select Total Sales so that I can summarize my data based on sales:

Next up, let’s add fields for both the row and column sections of the pivot table. If you have a lot of dates in your data set, you’ll want to put the field into the Row section. Otherwise, Google Sheets may give you an error where there are too many columns.

Even if you want to use dates in the column section, you’ll better of first putting it under Rows. Then, right-click on one of the dates and select Create pivot date group:

From here, you can group your dates so that you don’t have too many entries. In my example, I’m going to use Month as my breakdown. After that, I can move the Date field back into the Column section:

The problem here is that even if you have multiple years, it’ll group it into the same month. For example, I have one entry for Dec. 31, 2018, and it has not separated that out from the 2019 values. In order to fix this, I need to change the grouping from Month to Year-Month. Then my pivot table looks as follows:

Now the data from December 2018 is broken out. Next up, I’ll add another field for the Row section. Here, I’ll add the Store field. And now my pivot table is looking more like what I’d expect it to:

Google Sheets pivot table row and column data filled in.

Next, let’s also add the Salesperson field as well so that we have more of a breakdown:

Google Sheets pivot table with multiple columns and rows.

One of the things that stands out right away is that in Google Sheets the layout of the pivot table is much more intuitive. One of the annoyances of pivot tables in Excel is they’re not in a tabular format by default. With Google Sheets, it’s not something you need to worry about.

It still doesn’t have the repeating rows for the Store field, but that’s a quick fix: simply click the option to Repeat row labels:

Repeat row labels option in Google Sheets is easily accessible within the field settings.

And then, voila:

google shets pivot table with multiple columns and rows

Just like with a regular pivot table you can also drill down into the individual cells to the detail. In Google Sheets, it also gives you a specific name as to what cell you’ve drilled down on, making it easier to refer back to when looking at many different tabs:

Adding slicers to the pivot table

You can also add slicers to your pivot table to make it easier to make changes to it and update it on-the-fly. To add a slicer, just click on the Data tab while you’re on the pivot table and click on Slicer:

adding a slicer to a pivot table in google sheets

Then that will generate the slicer, where you’ll be prompted to select a column to filter by:

Click on the filter icon and then on the right-hand side you’ll see the option to select a field from a drop-down list. In this example, I’m going to select Salesperson:

Then, on the slicer you can filter by the values in the column:

selecting the values to filter in a slicer in google sheets

If I hit the clear button and select only Rep A, Rep B, and Rep C, this is what my pivot table now looks like:

google sheets pivot table filtered by a slicer

The slicer shows the number of items selected and as you can see, it only has the sales reps that I selected in the data. You can add more slicers for other columns but the process remains the same. The big difference you can see from Excel is that your selections are how you’d make the selections in a normal filter; you don’t have buttons for each slicer option the way you do in Excel.

There are changes that you can make to the font and color of the slicer but other than that, visually, there aren’t many changes to make. So if you’re looking to replicate a similar Excel-type dashboard in Google Sheets with many options available for how slicers look then you may be disappointed here. However, in terms of functionality, the slicers work in much the same way that they do in Excel.

A good start, but Google Sheets is still lacking

Google Sheets still has a ways to go in being a real replacement for Excel. While it does have some unique functions that Excel doesn’t, adding pivot tables and slicers is a significant step forward.

However, one area that still needs more improvement is charting. For instance, creating a chart from the pivot table is not an easy task and doesn’t look like Google Sheets is designed yet to create easy-to-use pivot charts. And until that happens, there’s still going to be a big gap between the type of dashboard you can create with Google Sheets and what you can make in Excel.

The good news is that Google Sheets has made a lot of progress and it’ll likely be even better in the future.

If you liked this post on How to Make a Pivot Table in Google Sheets with Slicers, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

big data excel pivot tables getpivotdata

How to Use GetPivotData

September 21, 2019

by howtoexcel with No Comment advanced pivot table

For many users, the GETPIVOTDATA function in Excel is a nuisance and people are often looking for how to turn it off, rather than to actually use it. It can be a bit of a clunky formula, to say the least, but it can be very useful once you’ve learned how to use it effectively, which is what I’ll show you to do in this post.

Using GETPIVOTDATA to extract data using a single criterion

I’ll start with a simple pivot table that just shows stores and sales:

If I select the cell that has the sales for Store A, Excel populates the following formula:

=GETPIVOTDATA(“Total Sales”,$B$4,”Store”,”Store A”)

Let’s break down the different parts of this formula:

“Total Sales” is the first argument, and it is the name of the data field that I’m pulling my numbers from.

$B$4 is where my pivot table is located. However, this specific cell doesn’t matter, as long as the range is somewhere on your pivot table. For example, I could use B5 or B6; as long as the cell is located on the pivot table, the result will remain the same. The cell also doesn’t have to be frozen. However, if the range refers to a cell that isn’t on the pivot table, you’ll get a #REF error.

“Store” is the third argument, and it’s the field that relates where the “Store A” item is found, which is the last argument of the formula.

In essence, the GETPIVOTDATA starts with selecting the field you want to pull data from, the second argument pointing to somewhere on the pivot table, and with the third and fourth arguments relating to the relevant criteria. Think of it similarly to how you might use a SUMIFS function where you first specify what you want to sum, and where you can keep adding criteria to it.

If I only used the first two arguments of the GETPIVOTDATA function, it would return the total for the entire pivot table:

=GETPIVOTDATA(“Total Sales”,$B$4).

This would give me a value of 394,380 – which is the total of everything in the pivot table.

Using multiple criteria in the GETPIVOTDATA function

Let’s make this calculation a bit more complex and add both a product field and one for the sales rep as well:

If we want to pull the sales that Rep A had for Product A in Store A, that means we now have three criteria instead of just one. The good news is that all we have to build off the previous formula:

=GETPIVOTDATA(“Total Sales”,$B$4,”Store”,”Store A”)

To follow the pattern, what we’ll want to do is add the field name followed by the item in the field that’s our criteria. That means we need to add the “Product” field followed by “Product A” for the item:

=GETPIVOTDATA(“Total Sales”,$B$4,”Store”,”Store A”,”Product”,”Product A”)

Now, if we want to make it more complex and add Rep A as another filter, then it’s just a matter of adding the “Salesperson” field and “Rep A”:

=GETPIVOTDATA(“Total Sales”,$B$4,”Store”,”Store A”,”Product”,”Product A”, “Salesperson”,”Rep A”)

As you can see, you can add in as many criteria as you like. The key is just making sure that the field names you use match exactly what the fields are called on your pivot table. Otherwise, if the field cannot be found or if it is misspelled, you’ll get a #REF error.

Making the GETPIVOTDATA dynamic

Entering in all these fields is not optimal, and the real value in using GETPIVOTDATA is by being able to make the results dynamic and using variables to do the work for us.

To do this, I’ll set aside cells for both the field names as well as the criteria. Here’s how the new, dynamic formula would look now:

What you’ll notice is the GETPIVOTDATA now has nothing hardcoded that you’d have to change in the formula. Instead, you could change the fields in rows 4 and 5 instead. Let’s breakdown the formula in a bit more detail to see how it works:

=GETPIVOTDATA(TEXT(I7,””),$B$4,I4,I5,J4,J5,K4,K5)

The first argument, TEXT(I7,””), references the Total Sales field in cell I7. The reason I use the TEXT function here is that the value needs to be forced into a text format, and by using “” as the second argument in that function, it will keep everything the same.

The following argument, $B$4 still just has to point to somewhere on the pivot table. Unless you move your pivot table, you won’t need to change this argument.

The following arguments: I4,I5,J4,J5,K4,K5 all relate to the cells that have the field and criteria data. Change the values in those cells and the formula will automatically update, rather than having to fumble around and make changes in the actual formula.

Limitations

There are some important limitations that you should be aware of when using GETPIVOTDATA.

Blank values will cause errors

If in the example above you want to use fewer than three criteria, you’ll have to modify the formula, otherwise, you’ll get a #REF error if you simply clear the variables and make them blank.

Data must be visible

Another important thing to remember: if your pivot field isn’t showing the data, the GETPIVOTDATA won’t be able to use it.

Since I’ve removed the Salesperson field from the pivot table, I can no longer use that as part of the criteria in my GETPIVOTDATA formula, and hence is why there is now a #REF error in my result. Even though it is still in the dataset, GETPIVOTDATA will only be able to extract from information that is visible on your pivot table.

Order is important

The order that the fields show on the pivot table will impact how you can use GETPIVOTDATA. For example, if I have Store, then Product and then Salesperson in my pivot table, I have to take that into account when creating the GETPIVOTDATA formula; I can’t just use GETPIVOTDATA to use criteria from the Store and Salesperson fields and skip over Product. I can, however, pull the totals for Store and Product and then just not include Salesperson, since I’m not following a different hierarchy than what’s shown in the pivot table.

Too many fields will make it difficult to summarize totals at lower levels

The GETPIVOTDATA function can be very particular, especially when it comes to hierarchy. For instance, you won’t be able to total sales by rep for everything in the pivot table if you’ve got the store and product fields shown higher up in the hierarchy; you’ll need to incorporate those fields into your calculation. That means adding all those combinations where the sales rep is found, which isn’t optimal. I can’t just say I want to see the total sales this rep made, forget all the product and store combinations that come above it in the hierarchy. The easiest way would be to simplify the pivot table to remove those fields or make the Salesperson field at the top of the hierarchy, and then it would be possible to do that by just using one criterion.

Why formulas may be a better option

GETPIVOTDATA has its strengths, but as you can see, it also has many weaknesses and limitations. This is where using formulas like SUMIFS can be a lot more useful if you’re comfortable using them. GETPIVOTDATA can be useful for pivot tables that aren’t going to move or change and it could also be a bit quicker than a lookup or other function. If you’ve got a lot of cells that you need to populate, SUMIFS may just end up slowing down your spreadsheet too much, and you may find it easier just having multiple pivot tables with different views instead.

At the end of the day, if you hate GETPIVOTDATA and prefer a simpler approach like just using lookup functions to get your data, this post will show you a simple way to get rid of it when selecting cells on a pivot table.

If you liked this post on How to Use GetPivotData, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

4 Biggest Annoyances of Pivot Tables and How to Fix Them

November 12, 2018

by howtoexcel with 2 Comments beginner pivot table tips & tricks

Pivot Tables are great tools, especially for quickly summarizing data and allowing you to avoid entering in complex formulas to do so. However, they aren’t perfect and definitely have their quirks about them.

Here are four things that most users aren’t crazy about, and how to fix them.

The layout

When you create a pivot table it puts the table into a layout that is less than optional for spreadsheets. It’s not only hard to use for any other purpose, but it’s also not the easiest format to read. I always prefer a format that resembles more of a spreadsheet itself, in a grid format, without the indented rows.

To change the format, go to the Design section of the PivotTable Tools and select Report Layout and select Show in Tabular Form

This is what the pivot table looks like after the change:

This might still look a little cluttered, but what you can also do is get rid of any subtotals by simply right-clicking on the Total and unchecking the Subtotal button, and then it’s a bit cleaner:

The table still needs a little work for me, and that brings me to the second issue:

Labels do not repeat

You’ll notice that the value in the date field only shows up once. And so if I had many entries for a given date there would be a lot of blank values in the first column. Again, this is not ideal for a large data set and so this is where I’d like to make a change as well.

I go back to the Report Layout section, except this time I select the option to Repeat All Item Labels

Now my table looks like this:

This is what I would hope the pivot table looks like right off the bat. Unfortunately, Microsoft does not agree and opts for the indented version with subtotal at every section and no repeating values.

But that’s not all, there’s another little nuance you may notice when it comes to pivot tables:

Formatting won’t stay consistent

You’ll notice that in the sales data, I have a total of 2158 for January 4th. Anytime you’re dealing with numbers in the thousands, you’ll probably want some formatting that shows a comma in there just to make the numbers more readable. However, if you simply select the entire column and change the formatting, it will revert back to the default if you refresh the pivot table after adding more data.

What you need to do is actually change the field settings. To do this, right click on any of the numbers in that column and select Value Field Settings.

From there, select Number Format and then select the format you want it to show up as. Doing this will ensure the formatting won’t change when you refresh the data.

Tip: if you routinely make these changes like I do, you may want to check out the add-in I created which will make all these changes for you at the click of a button.

This brings me to the fourth most irritating item when it comes to pivot tables:

GETPIVOTDATA

By default, when you reference a number in a pivot table, it will auto-generate a formula called GETPIVOTDATA. In most cases, you probably won’t find this helpful, especially if you just want to reference some pivot table values elsewhere in your workbook. If you just want to reference cell E4 and copy the formula down, you’ll need to adjust the pivot table settings, as the GETPIVOTDATA will make it a bit painful to accomplish this.

Back under the PivotTable Tools section, select the Analyze tab and on the Options menu on the left-hand side, uncheck where it says Generate GetPivotData

Now you can reference values from a pivot table the way you would any other cell in your workbook.

If you have any other frustrations with pivot tables, please share them in the comments.

Creating a Dynamic Dashboard in Excel

January 27, 2018

by admin with 2 Comments dashboard intermediate pivot chart pivot table

Do you want to create a dashboard that will update all of your charts simultaneously based on what filters your users select? Follow the steps below and you can create a professional-looking dashboard without having to use any complex formulas or programming.

Preparing the Data

If you have data in Excel that you want to use to create a dashboard, there are a couple of things you’ll want to do first to make sure everything goes well.

1. Ensure your data is free of error cells, as this will result in errors.

2. Have proper headings setup so that you know what you are referencing in your dashboard.

3. Setup a named range for your data, ideally a dynamic one. This will make it easier to link everything to your data quickly and easily.

Making sure your data is clean and ready to go is the most important step, but unfortunately the one that is easily overlooked. After all, if you’re data is no good, your outputs won’t be either. Garbage in, garbage out.

You can follow along with my sample data, which can be downloaded here.

Setting up the Pivot Table

First up, let’s look at creating a Pivot Table (see this post for an into into pivot tables).

I’ve assigned a name of Dataset1 to my data, and this is what I will referencing when I create a pivot table. With a named range, I don’t have to worry about selecting the data before clicking the create pivot table button, I can do it from anywhere.

Once I’ve got my pivot table ready to go, the next thing to do is to select my fields. The fields that I have to choose from in my data set include: date, store, salesperson, and product.

For my first pivot table I’ll want to look at the date because I want to start from a high level and work my way down. No sense in starting from the detail when I don’t have any context yet.

For the rows, I’ll select Dates, and in the values I’ll select Total Sales. My table now looks like this:

Ultimately, it doesn’t really matter if you want to select columns or rows for this as it’s going to be in a chart anyway. In Excel 2016, my dates were automatically grouped into months, which is what I wanted. If you want to change the grouping, simply right-click on the dates values and click Group.

Then select the how you want the dates to be grouped

Next, I want to clean up my formatting so that my total sales have commas and so that the data is easier to read. To do this, I’ll right-click on that field and choose Value Field Settings.

Then click on Number Format and then select Accounting.

Creating a Pivot Chart

Now it’s time to convert this into a chart. Select the Insert tab and in this example I’m going to select a regular Column Chart.

What you have now is a simple column chart that shows your sales by month. The only thing special about a pivot chart is that you’re able to filter it based on your pivot table.

You’ll notice there are drop downs on your pivot chart that you select to modify your data. I can select only certain months to look at.

The amount of options you have on here depends on how many fields you added to your pivot table. Whether you make the changes on your pivot chart or pivot table doesn’t matter, the chart will update all the same.

However, for the purposes of a dashboard, I’m going to get rid of these ugly filter buttons on my chart. To do this, click on your chart and click the button for the Field Buttons and this will remove the buttons.

Now that the pivot chart is ready to go, you can now go about and format it how you like.

Formatting and Tidying Up

Once you’re done formatting the chart, move it on to another tab. Because you’re creating a dashboard you probably won’t want your original pivot table to show up along with it. For this reason I usually move all the charts onto a separate tab.

Lastly, you’ll probably want to format your chart so that it is more appealing to your users.

Rinse and Repeat

For a dashboard, you’ll want to create multiple charts and so you’ll likely want to create another pivot chart following the same steps as above. In the next chart you can focus on a more detailed analysis, such as sales by store or rep.

In my example, I added three more charts in total and decided to mix it up by using a column chart, a pie chart, a stacked chart, and a bar chart. Mixing it up a little will keep your dashboard more interesting for your users.

Adding Slicers

Once you’ve added your charts, the next thing you’ll want to do is add slicers. Note that slicers are new to Excel 2010 and if you have an older version you will not be able to utilize these features.

For an overview on slicers, refer to this post.

The real advantage of using slicers is that they can be linked to multiple pivot tables and pivot charts. This allows you to now turn your dashboard into a dynamic one that will update as the user selects options from the slicers.

Once you’ve inserted slicers, you want to make sure that each slicer is connected to every pivot table. To do this, simply right-click on the slicer and select Report Connections.

On the next screen you can see all the pivot tables and charts that the slicer is connected to. Ensure that you have ticked off all the ones you want it connected to and then click on OK.

By doing this your slicer will now update all those charts and tables automatically. Repeat this step for every slicer you create.

Your dashboard is now ready to use and anyone that makes a selection on one of your slicers will see all the charts update immediately.

Using Slicers in Pivot Tables

August 8, 2017

by admin with 2 Comments intermediate pivot table slicers

I am going to use the same pivot table that I left off with in my last post and now I am going to add slicers to show you how they can make your life easier.

***Please note slicers are a new feature in Excel 2010 and you will not be able to use them on older versions***

To insert a slicer is very simple, while having selected a cell on your pivot table, go to the Insert tab and click on Slicer.

Then you will be prompted to select which field you want to use a slicer for:

I am going to select the first four fields: Date, Customer, Store, and Sales Rep. Once you’ve made your selections, click OK. Now you have four different slicers that are on top of your pivot table:

This is of course not ideal, so the first thing you will want to do is move them. You can easily re-size the slicers as you see the current one I have selected for Sales Rep shows circles along the edges that I can use to re-size it.

First what I am going to do is insert a column into column A so that my pivot table pushes over into the next column. I will also insert a few rows above it to make room as well.

I am doing this so it is easier to move over a slicer to the left of the pivot table.

For slicers that have long filters (for example customer names), I prefer to put those above the pivot table, otherwise the names might get cut off. For shorter names, such as the three letter month abbreviations, those can go into the column to the left of the pivot table. Because they are short, they won’t need a big column to accommodate them. However, this is just my preference and you can put slicers wherever you think is most convenient.

I’ve re-arranged my slicers so the months are to the left of the pivot table and everything else is above it:

The slicer to the left of my pivot table looks fine, but the problem is the slicers at the top only show a couple items each, and would require me to scroll to find my selection. You may need to scroll, but there is a lot of empty space that can be used otherwise.

What I want to do is add another column in the slicer. If I select the Customer slicer, under the Options tab under the Slicer Tools section, on the right-hand side there is a field for columns. The default is set to 1, but if I change it to 2 my slicer now looks like this:

This is already a big improvement since now instead of seeing just two items I see four. I can add more columns but in this case without expanding my slicer horizontally it will truncate the names. You can stretch out the slicer as you see fit and adjust the columns accordingly. After adjusted my slicers all to be two columns, my pivot table and slicers now look like this;

If you are finding it hard to line up your slicers so they are even, select the slicer and under Slicer Tools and Options, click the Align button and check off Snap to Grid and Snap to Shape. Doing this will make it easier for your slicer to lock on to other slicers and make it easier to line them up. Do this for each slicer you are having issues lining up. Or you could select all your slicers (using ctrl) and apply the settings to all of them at once.

Now that the slicers are setup they are ready to use. Slicers effectively are filters in a pivot table, but the key difference is their ease of use for any user.

Whatever options I want to filter by I can just click on in the individual slicers. In the below example I am going to select all the sales to Customer ABC and Store 1 for Rep 01. My selections below reflect these selections and now my data table has filtered this data out:

If I want to remove any filters then all I can just click on the filter icon and the red x in the top right corner of the filter. This will reset the selections for that slicer.

Based on my selections I cannot choose any option besides Oct for the Date slicer. This is because based on the criteria I have selected in the other slicers there are no other months that are available. The unavailable selections are indicated by the faded light blue selections.

How to Create a Pivot Table in Excel

July 20, 2017

by admin with No Comment intermediate pivot table

In this post, I’ll show you how to create a pivot table. It’s a key skill in Excel that all users, even beginners, should be familiar with. It can make your analysis a lot easier to do while also presenting your findings in a very easy-to-read format for users of your data.

Why should you use a pivot table?

One of the biggest benefits of using pivot tables is that you can double-click on any total to see the individual items that make it up. This is something that’s not possible with formulas and can sometimes involve a lot of digging. But with pivot tables, the information is only a few clicks away.

Not only is the information easier to drill-down into, but it’s also a lot easier for the person making the report and summarizing the data to create it as well. With formulas, you may have to use many different summation formulas which could get complex very quickly, but a pivot table can take care of all that if your data is organized.

Pivot tables are also good when you’re dealing with lots of data. If you’ve ever used a formula to analyze thousands of rows of data you know that it can start to slow down your spreadsheet, and even your computer. With pivot tables, the data is all stored as a snapshot and a recalculation is only necessary if you add data to it and refresh it. Otherwise, the information will remain there in the background. That saves your spreadsheet the need from always doing calculations in the background.

The one significant downside of pivot tables is that they’re not often as flexible as formulas are it can be difficult to manipulate them to look exactly how you’d like them to.

What this post will cover

In this introductory post I’ll go over a variety of different topics relating to pivot tables, including the following:

How to create a pivot table
How to setup the pivot table’s rows and columns
Grouping dates into months, years, and quarters
Filtering data in a pivot table
Changing the formatting of fields
Changing pivot table values to averages
Show values as a % of a column or row
Adding more fields and changing the view to tabular
Viewing the contents that make up a cell

If you want to follow along with my example you can download my sample file here

1. How to create a pivot table

Actually creating a pivot table is a very simple process. The only requirement is that your columns should have headers and there shouldn’t be gaps in your data to make sure it picks everything up. If your data doesn’t need adjusting, then simply elect any cell on your data table and click on the Insert tab and click on the Pivot Table button (you can also use shortcut ALT+N+V)

That will pop up the following screen:

Excel automatically determines the table range based on the active cell when the create pivot table action was triggered. If the cell wasn’t on the dataset, Excel may not be able to pull this information accurately. As long as you make sure you click the insert pivot table button when you have your data selected then usually Excel will likely get the correct range. Of course, if the information is incorrect you can change the range at this screen as well.

You can select whether you want the pivot table in a new worksheet or the existing one. In most cases, you’ll want a new worksheet, which is what it will default to. Since you can’t overlap data with a pivot table, it’s usually cleaner to just start on a brand new sheet.

2. Setting up the pivot table

When the pivot table is generated you will see the following:

This pivot table is blank and not terribly useful right now. On the right-hand side you will see this:

This is where you will select where you want your data to show. This is going to be the nuts and bolts of your pivot table. If you organize your pivot table well, it’ll display the results that you want. But if you don’t, you could end up with a confusing and useless table.

Suppose for example, that you want to see a summary of sales by store, broken down by month. First, you’d find the Total Sales field from the above list and drag it into the Values section of the pivot table. Immediately, the pivot table will show you the total of all the sales for everything:

However, let’s see this broken down further by store. To do that, move the Store field into the Rows section. Now, you’ll see the totals by store:

Normally you’ll want the field with the most amount of items to go into the rows section. Otherwise, if you put that field into the columns section you will have to do a lot of scrolling to the right to see the entire table, which isn’t ideal; normally you want to be scrolling up and down instead.

Lastly, let’s also move the Date field into the Columns section. This is what the layout will look like now:

Your pivot table should look something like this:

You’ll notice that the grand total is the same from when you dropped in Total Sales and had nothing else. Now, however, the data has just been split by store and by date. Excel has also automatically formatted the dates into months, but that’s not always a guarantee.

3. Grouping Dates by Month, Year, Quarter, etc…

If you want to group your dates or want to change the grouping, select one of the dates on your pivot table and under the PivotTable Tools -> Options (this could be Analyze or PivotTable Analyze, depending on which version of Excel you have) tab, click on Group Field button.

After clicking that button you will see the following options:

Excel has automatically divided the data into days and months. However, you can group this however it makes sense to do so. If you had multiple years, you could split it accordingly. For the sake of breaking down the data even further, let’s also select quarters. Then the pivot table looks as follows:

pivot table showing sales by quarter and by store

This is a good overview of the entire year. However, if you only want a report that looks at a specific quarter, then this might be more than what is needed. Below, I’ll show you how to filter the pivot table to show only certain periods.

4. Filtering data in a pivot table

To filter the data in the pivot table, you’ll need to click on the Column Labels button since the dates are in columns. Then you will see a drop-down option to select the field you want to filter:

Pivot table showing option to select certain periods to filter by.

What you can do now is to select the specific quarter that you’re looking for. If you select just the first quarter, then the pivot table will update accordingly:

Pivot table showing sales sales data for the first quarter only.

You’ll notice now the grand total has been updated to include only the data that has been filtered for, rather than everything in the pivot table.

5. Changing the way fields are formatted in a pivot table

The pivot table looks okay so far except that the way the numbers are formatted is far from optimal. It doesn’t show dollar signs nor is there a comma separating thousands; it’s just not very readable for users at this point. But it can easily be fixed.

While you could just select the entire columns and make changes like you might normally do to ordinary cells but the problem with that is that it’s only a temporary solution; if you refresh the pivot table, the formatting will go back to what it was before. In order to change the formatting for a field permanently, you’ll need to right-click on one of the data points and click on Value Field Settings.

Next, click on the Number Format in the below screen:

Then it’s just a matter of selecting the number format that you want to use. For dollar signs and a comma after the thousands, Accounting format is the best option. Selecting that, the pivot table will now show the following:

6. Changing pivot table values to look at averages

Currently, the pivot table shows the total sales, but it can be adjusted to show the average sales — this will be made up of the average of each individual line item. This can be useful if you want to see the average transaction size per location. To switch to average, right-click on any of the dollar amounts and again go back and select Value Field Settings.

From there, change from Sum to Average. If you click on OK now the table will show averages

You will notice at the top now instead of Sum of Total Sales it now says Average of Total Sales.

7. Show values as a % of a column or row

Rather than averages, now let’s show the values as a percentage of the total month. Again, go back to Value Field Settings and change the calculation back to Sum and then click over on the tab on the right called Show Values As.

In the drop-down selection, select % of Column Total. This will give show the data as a % of the total for the month. Since the dates are in the columns, you would select columns here. After clicking OK, the pivot table looks like this:

Now it’s easy to see the proportion of sales for the month came from each store. Alternatively, you can also see which month made up most of a store’s sales by using % of Row Total rather than the column total. For now, let’s revert back to just showing totals. To do this, just go back to Value Field Settings and under the Show Values As tab select No Calculation.

8. Adding more fields and changing views to tabular

Next up, let’s also add another field, Salesperson, to the rows section which will group the data even further. If you want the pivot table to first be sorted by Store and then Salesperson, the Salesperson field should be dragged under the Store field. If you want the pivot table to first sort by Salesperson and then by Store, then the Salesperson field will need to be above the Store field.

In this example, let’s put the Salesperson field underneath the Store field since it’s might be a more logical hierarchy in this case. After adding the field, you’ll get to a common pivot table problem: it’s in a format that’s just not ideal.

If you want to use the pivot table in a formula or copy it as values somewhere, then this format, Compact, it’s not very helpful since it doesn’t have all the information on one line. Ideally, the store field should show on every line. To do this, go under the PivotTable Tools section again and under Options/Analyze (or the Design tab) and click on Report Layout and select Show in Tabular Form

The pivot table should now look like this:

The layout now has store and salesperson on the same line, but only for the first line. The store field is still showing blank for many of the items.

To fix this, go back to the Report Layout options and this time select Repeat All Item Labels

Here’s the updated pivot table:

This is an easier format to follow since it has all the relevant data on one line and it is easier to read. If you don’t want to see all the stores and the salesperson detail, you can collapse the field by pressing on the – button next to the store name at the top. This will change it to a + and collapse the field. If you want to do this for all stores, right-click on any store and select Expand/Collapse and select Collapse Entire Field.

This will now give you a tidier pivot table:

9. Viewing contents that make up a cell

Lastly, let’s go over how to see the contents of a cell. As mentioned earlier, one of the benefits of a pivot table is that you can simply double click on a cell to see what makes up its amount.

For instance, by double-clicking on the cell in the first row for $3,735, it will open a new sheet with the following data:

What this tells you is that the cell was made up of all of these entries. If you were to double click on the grand total, you would see all of the transactions in the entire pivot table. Once you’re done looking with the tabs, you can modify them how you like or even close them out as they won’t impact the pivot table itself.