Welcome to Excel Magic

Trick number 1394. If you want to download

this Excel workbook, Excel Magic Trick 1393-94 so

you could follow along, click on the link below the video. Wow, we have a crazy video here. We’ve got to calculate formulas

for traditional pivot table calculations, such as

percentage of column total, percent of parent total, and

percent of parent row total. Now in this video, we’re

going to see formulas. It is so much easier to do

this with a pivot table. And in our last

video, we saw how to do all these calculations

with a pivot table. Why in the world would you

want to do it with formulas? Well, maybe you just

want the challenge of calculating the formulas. But the real reason is

if you want your solution to update instantly when

any source data changes without having to refresh, then

you’d have to use formulas. Now let’s look at

our pivot table. This column right

here is actually going to be quite tricky,

because these two totals are calculating a total from the

revenue column based on product criteria, but when

we get to here we need to get a total

in the same column, but from the

manufacturer column we have to get each

individual manufacturer. So let’s start with that. Now I’m just going

to build a SUMIFS formula based on

the product and that will be our starting point. So I’m going to use the SUMIFS. The sum range, I need

the entire revenue range. So I click in the top cell,

Control, Shift, Down arrow to highlight all the

way to the bottom. F4 to lock it, so it’s

locked when we copy down. And it also jumps

the screen back. Comma. Now criteria range. We need to look through

the entire Product column. So I click on the top. Control, Shift, Down

arrow, F4, comma. And then the criteria

is simply going to be relative cell reference. Now I can close

parentheses, Control, Enter, and copy it down. Now one thing it did right there

is it wrecked the formatting. So I’m going to

point to my Smart tag and say Fill Without Formatting,

and now it didn’t wreck it. Well, this formula

works for the products but obviously not for here. So we’re going to have

to run an IF formula and check to see whether the

cell in the product column is empty and then run

a different formula. Now I’m going to come

up to the top, F2. I’m just going to see if I

can get the If part of this to work, IF. And the logical test. Well, I need to check if

relative cell reference, one to my left, is empty. And the function in

Excel is ISBLANK– even though it should

be called ISEMPTY. There it is, relative cell

reference, close parentheses, comma. So back in logical

test argument, that delivers a true or false. Well, when we copy

down to Channel Craft total row is blank, we’ll

be delivering a true. So comma, the value of false

will be for the products. Now we just have to change

that little bit right there, Value If True. Well, it’s SUMIFS again. It’s going to be the same

sum range, Control, Shift, Down arrow, F4,

comma, criteria range. That’s the manufacturer. Control, Shift, Down

arrow, F4, comma. And then the criteria. Well, we’re going to try this. Because the actual

criteria is over here. It’s not going to work. Close parentheses. We’re almost there. But we have the concept down. There’s the SUMIFS

for manufacturer. There’s the SUMIFS

for the product. Now I need to close

parentheses on the end. Control, Enter. It’s still not going to

work but we will fix it. Double click and send it down. Look at that. It wrecked it again. I’m going to show

you a better way. The non-edited formulas through

the rest of the column– that is the edited format. So watch this. I’m going to highlight

the entire range. In the active cell at the

top I’m going to hit F2. If I use Control Enter to

populate this entire range with this formula,

the formatting does not get changed. So Control Enter. And you could actually

prove that to yourself. What’s going on? Well, right here,

of course, it can’t match Channel Craft, space,

total with any of the Channel Crafts over here. So I’m going to come

up to the top cell. And for criteria, I’m going to

use the SUBSTITUTE function. The SUBSTITUTE says,

hey, where is the text? Well, it’s right there. Comma. What’s the old text

you want to get rid of, or substitute for, double

quotes, space, total, and double quote, comma. What’s the new text

you want to substitute for that space, total? Well we use double

quote, double quote. And the formula will know

to put nothing there. Now I close parentheses on. SUBSTITUTE and you could

actually click criteria. And there is the

whole substitute. That will work. Control, Enter. Actually, I’m going to highlight

all the way down, F2, Control, Enter. And there we go, a single

formula to calculate total for the products. But when it gets to

this row right here, it’s calculating the

total for Channel Craft. Now I’m going to

come to the bottom, Alt Equals, that’s

adding it up, and Enter. Whoa, wait a second. That’s not going to work. Well, luckily we’ll use

an old accounting trick. F2, it’s exactly double,

because there’s subtotals. So I’m going to

divide by 2 and Enter. All right. Now we can simulate

percent of column total, each individual item

divided over total. So equals relative cell

reference divided by I16 F4. Control, Enter. Watch this. I have that cell selected. I’m going to hover, hold Shift,

click, F2, Control, Enter to populate that all the

way down without interfering with the formatting. And there’s our percent

of column total. That one’s easy. Now percent of parent total,

this is going to be tricky. Because for this

block right here, we’re going to have relative

cell reference divided by that total. But when we get down

to this next block, the numerator for each will

be relative cell reference. But in all four

cells, it’s going to have to have that total. Well, I’m going to use the

same trigger, that empty cell. And we’re going to use

an expandable range. I’m going to build this

formula from the inside out, starting with ISBLANK. Then we’re going to highlight

the entire product column. Now as I copy ISBLANK

in this range down, I need this range

to be shrinking. So every row, it

needs to be looking at a smaller and smaller range. So how we’re going

to do that is I’m going to double click the end

cell reference in the range and hit the F4 key and lock

that one, but not the H4. That way, as I copy

down it will shrink. Now I’m going to

close parentheses. And what we’re doing here is

that value argument in ISBLANK is expecting one single cell. We just gave it

a bunch of cells. If I click at the end and hit

the F9 key to evaluate this, you could see it’s given me

a series of TRUEs and FALSEs. Now I want you to

notice something here. The first TRUE is exactly

one, two, three down. Control Z. Now I’m going to

enter this and copy it down. I want to click F2 and then

F9, and notice now the TRUE is in the second position,

because it’s a contracting or shrinking range. When I come down here,

F2, F9, now the TRUE is in the first position. Those were all

for Channel Craft. Now when I get here,

F2, F9, notice now the TRUE is back in

the fourth position, meaning that’s going to be

our trigger for picking out, for one, two, three, four, if

there’s some way to always pick out the first TRUE

as our contracting range gets copied down, that

could be a trigger for us to pull this for

our denominator. So I’m going to come

back up to the top, F2. Now if I need to look for where

the first TRUE is and I really need the position, I’m going

to use the MATCH function. And what am I looking up? I want to look up TRUE, comma. That’s that expandable range. And the way you

look up the first, when there are duplicates,

is you use exact match. So I’m going to put a 0 here. Close parentheses. Now this is an array

formula right there. We’re doing a function

argument array operation. So I’m actually going to enter

this as an array formula. Control, Shift, and Enter. Now once we enter with

Control, Shift, Enter, we have to look up

to the formula bar and verify that those curly

brackets are entered in. Those curly brackets are entered

in automatically by Excel and they tell us that Excel

understood and calculated this as an array formula. That’s got the wrong

formatting, but that’s OK. I’m going to copy this down. And I’m going to apply general

number formatting home. Number, General, or

use the keyboard. Control, Shift, grave

accent, or tilde. Now how is that

going to help us? Well, 3, 2, 1, if I use the

index function with a shrinking or contracting range

here, as I go down, it will know to

get the third one. And then here, it’ll

know, get the second. And then here, it will

know to get the first. When it comes down

to the next row, it’ll get the fourth,

third, and so on. F2. I’m going to use the INDEX

function and the array. Those are the items

we’re trying to look up, the same exact range, comma. And that last cell

reference, we’re going to double click

to highlight it, F4. That is a contracting

or shrinking range that will work perfectly with MATCH. MATCH is delivering the relative

position for that shrinking or contracting range. Close parentheses. We have to enter this with

Control, Shift, and Enter. Now if I double click

and send it down, that is how we’re going

to get the denominator for each one of our

sections of percent of parent total calculations. Now I can come to the top, F2. Since I have the

denominator right, I simply, right before, INDEX

put the relative cell reference total revenue and use division. Control, Shift, Enter. Double click and send it down. And there’s our pattern of

our percentage for each parent total. Now we can highlight this,

Control 1, Number tab, Percentage, click OK or Enter. And there we have

percent of parent total, just as we had in

our pivot table. So the tricky part, of

course, was that denominator. Now I’ve see I’ve totally

messed up the formatting. Watch this. I’m just going to highlight

this column right here. Right click. And there over on the mini

toolbar, I have Format Painter. Then I’m going to

click at the top, and there that border is back. Now we could take

this one step further. Right here, that

is the array that’s causing us to have to use

Control, Shift, Enter. And I’m going to wrap that

inside a function that knows how to handle array

calculations without Control, Shift, Enter. So I’m going to use

the INDEX function. Now INDEX has– most

of the time we’re using the first one, array,

row number, and column number. It is the array

argument in INDEX that can handle array operations

without Control, Shift, Enter. Now the problem is, we

need all of the values that is blank or spitting out. Normally for INDEX, we’d give it

a row number or row and column number. But watch this. Because I need all

the rows, I’m going to type a comma to

get to row number. And you either put a

0 and then close off. INDEX because a 0 tells INDEX

to please give me all the rows, or leave it omitted. by putting a 0 or leaving

row number omitted, it says, hey, INDEX, get all

of the rows in that range. And now we don’t have to

use Control, Shift, Enter. I’m just going to use Enter. And then watch this. I’m going to use that

trick earlier, highlight the whole range. The top cell has

our edited formula. So I hit F2 and Control,

Enter to populate it all the way down. Notice there’s no curly

brackets up there, even though it’s

an array formula. It’s just able to

calculate without using that special keystroke. All right, now we

have one last formula, percent of parent row total. Well, as we talked

about in the last video, percent of parent row

total is a combination of percent of column total

and percent of parent total. So when we’re doing percent

of parent row total, the actual individual

amounts are being compared to its parent. But when we get down to the

total for the manufacturer, it doesn’t have a parent, so we

compare it to the column total. So notice that percentage

is really percent of column. This one is really

percent of parent total. We already have our

two calculations here. So I’m going to use

the same trigger, when that cell is empty equals

IF ISBLANK, right there. That many– relative

cell reference. Close parentheses. Well, if that comes out true,

that means we’re down here. And we need percent

of column total. So comma. The value of, TRUE

percent of column total. Comma. Value if false, percent

of parent total. Now notice, this formula

is dependent on these two other columns. If we didn’t have these

two other columns, we’d have to scoop that whole

formula up here and put it where the orange cell is,

scoop that whole formula and put it where K4 is. Now close parentheses,

Control, Enter. Hover over the last cell. Hold, Shift, click

F2, Control, Enter to populate that

all the way down. Whoops. That’s not going to work. We actually have to amend this. Because that is not empty. So we’re going to come

up to the top, F2. And instead of just ISBLANK,

I need to test two things. If it’s blank, of

course, I need this. That would be the trigger. But when I get down here, total. So I need to say if it’s blank

or if it’s equal to totals, then please give me that

percent of column total. So up in logical test, I’m

going to use an OR function. The OR will run an

OR logical test. That’s the first logical test. I click after ISBLANK, comma. Then I say relative cell

reference all the way over there, if you are equal

to, in double quotes, totals, in double quotes. And then close

parentheses on the OR. Or, if it gets either

one of these truths, it’ll take the orange

relative cell reference. Control, Enter, Shift,

click F2, Control, Enter. And there we go. Wow, that was a wild,

crazy formula to simulate what we normally do

in a pivot table. We saw a Total Revenue,

ISBLANK, and two SUMIFs, and even a substitute. Percent of column

total was easy. Percent of parent total,

it was the denominator part we had to get tricky with. And then of course, this last

formula, percent of parent row total. All right. We’ll see you next video.

Thanks Mike for your great efforts, so many tricky and smart ways been used in this video.

Is being able to change part of an array something new in later versions of excel? I'm using 2010 and it won't let me do it when calculating the % of parent total.

Wonderful trick,,,, good work…

I'm completely mystified by the thought process, Mike!

How do you begin to figure out what the innermost and the outermost functions should be, and in what order?!

Could you make a short video about the thought process that you go through to prepare? Or is there one already?

Thanks and chapeaux!!

Ibrahim

Thanks :-))

I think, the simpler formula for "% of Parent Total" can be like this below.

=I4/INDEX(I4:$I$15,MATCH("?*",G4:$G$15,0))

I want to sort whole data then on the basis manufacturer who sold maximum item should appear first and products also sorted in desc for manufacturer .

Thank you for showing the formula version of the Pivot. I like your version of the % parent row total as well (I watched part 2 before this one 🙂 ). Do you think the match(?*…) version will be faster in calculating?

Very nice. 🙂

Hi Mike,

Thanks for the video and showing the formulas.

I have actually used alternative approach by using in built Sub-total feature of excel and some other tricks could get the exact same summary table without using above complex formulas.

Here is link to my video for alternative approach:

https://www.youtube.com/watch?v=aqwiol-RVbY

Please have a look and share your thoughts.

Wow! You are simply a genius 🙂

Mike – not sure what impresses me most about your videos – your genius with Excel formulae or your ability to teach – both awesome, as is your generosity with imparting that knowledge. I continue to look forward to your posts for both the elegance of your approach to these problems as well as pure entertainment value. Well done and many thanks

No comment Mike absolutately great low hat

Beautiful staff!!! Good Sunday morning breakfast. : ) Amazing.

That video was filled with several neat tricks. Thank you Mike.