Running Total in Pivot Table

by | Feb 10, 2023 | Excel

Some­times, you need to cre­ate a run­ning total in piv­ot tables. How to do it properly?

Run­ning Total in Piv­ot Table

Let’s start with cre­at­ing a piv­ot table. Select one cell from our data and go to the Insert tab and select the Piv­ot Table com­mand. A Piv­ot table from table or range win­dow will appear, where we have our range. We need to select the Exist­ing work­sheet radio but­ton and write the loca­tion, which, in our case, will be cell F1. Let’s click the OK but­ton (Fig. 1).

Creating a pivot table
Fig. 1 Cre­at­ing a piv­ot table

And there we have it. In our piv­ot table we need to drag the Date to the Rows labels. 

Dragging the date
Fig. 2 Drag­ging the date

Now, depend­ing on the Excel ver­sion, we have an option of Group by dates. I’m going to group our date by months and years (Fig. 3).

Grouping
Fig. 3 Grouping 

We have our table. Now, I want to add Income so let’s press the Income check­box (Fig. 4). 

Income checkbox
Fig. 4 Income checkbox

Now, I want to have the income and the run­ning total for the income, so let’s drag the Income two times to the Val­ues area. We can see that we have Sum of Income and Sum of Income 2, where we actu­al­ly want to have our run­ning total (Fig. 5). 

Income and Income 2
Fig. 5 Income and Income 2

Let’s right click any val­ue from Income 2, then select the Show Val­ues as and then the Run­ning Total In option (Fig. 6). 

Selecting the Running Total option
Fig. 6 Select­ing the Run­ning Total option

Now, we have to decide whether we want to base our run­ning total on Date or Years Field. Let’s take Date (Fig. 7).

Date Field
Fig. 7 Date Field

Now, we can see that in the Income 2 col­umn we have big­ger num­bers. Let’s for­mat them by press­ing any cell in the col­umn and choos­ing the Num­ber For­mat option (Fig. 8). 

Number Format option
Fig. 8 Num­ber For­mat option

In the For­mat Cell win­dow, let’s choose the Cur­ren­cy cat­e­go­ry, in Dec­i­mal places let’s write 0 and press the OK but­ton. Let’s do the same in the Sum of Income col­umn (Fig. 9). 

Format cell window
Fig. 9 For­mat cell window

Now, we can see that we are work­ing with mon­ey. In each month we have big­ger and big­ger num­bers which means that we have the run­ning total in this col­umn. We can change the name of Sum of Income 2 into Run­ning Total. Since we are using the Date Field as our ref­er­ence point, we have the run­ning total in 2020. In 2021 Excel counts from the start, which means that we have the run­ning total only for 2021. The same is with 2022. If we change the Date field into the Years field in the Show Val­ues as (Run­ning Total) win­dow, we will have the same val­ue in the first year and in the Sum of Income col­umn (Fig. 10).

Years
Fig. 10 Years

How­ev­er, in the next year, we have the the sum from Jan­u­ary 2021 and Jan­u­ary 2020. In Feb­ru­ary, we have the sum from Feb­ru­ary 2021 and 2020. In 2022, we have sums from three Feb­ru­ar­ies (Fig. 11).

Sums from three Ferbuaries
Fig. 11 Sums from three Ferbuaries

Let’s drag Man­ag­er into the columns head­er (Fig. 12).

Dragging Manager into headers
Fig. 12 Drag­ging Man­ag­er into headers

By doing so, we can show val­ue from the Man­ager’s per­spec­tive, which means a hor­i­zon­tal view. 

Horizontal view
Fig. 13 Hor­i­zon­tal view

How­ev­er, this type of data isn’t a prop­er one to show it this way, so let’s get back by press­ing Ctrl + Z two times and see that we can also cre­ate a per­cent run­ning total by going to the % Run­ning Total In option and choose the Date Field (Fig. 14). 

Percentage Running Total
Fig. 14 Per­cent­age Run­ning Total

Now, we can see the val­ues as per­cent­age (Fig. 15). 

Results
Fig. 15 Results

https://www.youtube.com/watch?v=ikou6toX11g

Filtering multiple Pivot Tables at once using a slicer

by | Oct 20, 2022 | Excel

Today, we are going to talk about how to fil­ter many piv­ot tables at once. We have three piv­ot tables based on the same dataset from the left (Fig. 1).

Fil­ter­ing mul­ti­ple Piv­ot Tables at once using a slicer
Three Pivot tables
Fig. 1 Three Piv­ot tables

If we want to fil­ter many piv­ot tables at once, we have to con­nect then into one slice. We have to select one cell from the first piv­ot table, then find the Pivot­Table Ana­lyze tab and Insert Slicer com­mand, where we want to add the slicer by Coun­try (Fig. 2)

Slicer by country
Fig. 2 Slicer by country

After press­ing OK, we have the slicer. We can change its size and col­or (Fig. 3).

Color and size change
Fig. 3 Col­or and size change

Now, we can fil­ter the first piv­ot table with the slicer. When we click on the name of a coun­try, we can see changes in the first piv­ot table (Fig. 4). How­ev­er, the sec­ond and the third piv­ot table did not change because they are not con­nect­ed to the slicer. 

Slicer for the first pivot table
Fig. 4 Slicer for the first piv­ot table

In order to con­nect then, we select the slicer, then we click on the Slicer tab, then the Report Con­nec­tion com­mand, which opens up a win­dow, where we can see three piv­ot tables. They have gener­ic, default names, and when we aren’t sure if they are the right ones, we can look which sheet they are locat­ed in. Then, we check prop­er check box­es to con­nect all piv­ot tables and press OK (Fig. 5)

Connecting three pivot tables
Fig. 5 Con­nect­ing three piv­ot tables

Now, we can see that the slicer is fil­ter­ing all, three, con­nect­ed piv­ot tables (Fig. 6)

Slicer for three pivot tables
Fig. 6 Slicer for three piv­ot tables

When the piv­ot table name or the sheet name is not enough to iden­ti­fy the right one, we can change their names. When you select a prop­er piv­ot table, go to the Piv­ot­Table Ana­lyze tab, then select the Piv­ot­Table Name com­mand, and write the name you want. Let’s write Piv­ot­Date (Fig. 7). Giv­ing names to piv­ot table makes it eas­i­er to work with them. 

Name change
Fig. 7 Name change

Now, that we have the slicer, we can eas­i­ly fil­ter three piv­ot tables at once (Fig. 8).

Ready slicer for three pivot tables
Fig. 8 Ready slicer for three piv­ot tables

https://www.youtube.com/watch?v=6TDXBG-wdhM