Hiding Columns In Numbers To Perform Complex Calculations

Formulas can get very large and complex. One way to simplify them is to use columns to perform parts of the calculation, and then hide those columns when you have your system working. In this example, we'll use columns to extract the year and month from a date, and then use that in a SUMIF formula that would be very complex otherwise.


Video Transcript CLICK TO EXPAND
So here I have a Numbers spreadsheet with two tables in it. The first one just has a bunch of records with dates and an amount. The second one performs calculations to figure out the total for each month. It looks for all the things in January 2017 over here and sums them up and puts the total there. Now looking at what I have here doesn't look like there's enough to actually calculate that. There is no simple way to just ask Numbers to give me a total of everything that has a date within a month and put the total there. You're right! It's too little here. That's because I've got hidden columns. There are columns here that you don't see. The secret to doing complex things in Numbers, or in any spreadsheet, is to hide columns. Hide your work. Only show what you need to actually see and use these columns to break problems up into smaller pieces.

So I'm going to select this first table here and go to Table, Unhide All Columns and you'll see what I've hidden. I've hidden three columns here. The first one is using a simple year formula to determine the year from the current date. So you can see here I've got 2017 and 2018 as the answer to all of those. I've got a similar thing here in this column using month. So it's grabbing the current month. Then I've got one here that's basically using Concatenate to put the year-month. So I've got that there. So this will actually give me an idea of what I want to add together. If I were to look for all the things that are 2018-8 in this list here and total them up, I would get everything from August 2018 here which is what I want.

Let's take a look at this table. I have hidden columns here as well. So I will unhide all these columns here and we'll take a look. You see here I've got the same thing here. I've got a year. I've got month. It combines them there. So now I'm able to use a formula here. The formula is to use Sum If to get everything from this column that matches this year-month over here. So it's comparing this to this and taking these numbers here and summing them up. So it's a basic Sum If formula but the trick is that I had no way to actually use a sum formula to say if the date is within this month. So I'm doing these hidden columns here to get here to get month, and to then calculate a little unique month identifier, year-month.

I could have done that in one column here. I could have actually used this year formula, or the year function, the month function, and put that all in here. So that would be the year and that would be the month and just have one extra column. But since I was creating extra columns I was going to hide anyway I figured I might as well break them up into smaller pieces. If you're not a genius, you know, in using spreadsheets then you have to go and break things up into smaller pieces else you're going to get formulas that are just huge and hard to debug and see what they're doing and all of that.

So breaking them up into these and hiding them in these columns really works well. Now I can select these columns here and I can click here and say Hide Selected Columns and I can do the same thing with these here and say Hide Selected Columns and now it looks like it just works. You can see all the calculations are hidden in the background. So for instance here is an entry for January of 2017. So it's this 540 is being added into this 2193 there. If I just add one to that you can see 2193 changes to 2194. So all of those things are updating in there. If I were to switch this to February 2018 you can see it moved from January to February there. This one here, if I moved this from January 11, 2017 to February 11, 2017 you can see it shifted that amount there.

So all those calculations are being done, in the background in those columns, and I have it nicely hidden. A really good spreadsheet has hidden columns everywhere. I use them all the time in order to make complex things look easy.

Related Posts:
Creating Dynamic Pop-Up Menus In Numbers With AppleScript, Using Numbers for Orbital Calculations, How Do I Find Identical Data In Two Columns In iWorks Numbers?, Hiding the Dock