Excel 2010

I have recently made the move to Excel 2010. Do I like it? I don't know yet. I'm getting older and change bugs me. I'm still getting used to it, and I'm not seeing a bunch of advantages yet. As soon as I learn a new 2010 trick, I will share!

Challenge

Someone recently asked me to help with an Excel problem he was trying to solve. I don't think I solved his problem, but I added a neat new trick to my bag.

The question (not necessarily the question asked) I found a solution to was this: is it possible to extract one precise word from a string no matter where it is in the string? We've already seen that it's possible to extract words or numbers based on their position in a string using mid, left and right.

The answer: the SEARCH Function!


You can read above to see what it does.

To get it to actually return the word, you have to nest this with an ISERROR



The formula itself is:
=IF(ISERROR(SEARCH("credit",A2,1)),A2,"credit")

In english, if the SEARCH function does not find the word "credit", return the value in A2, otherwise, return the word "Credit"

Here it is in action:

Mid

MID is very similar to LEFT and RIGHT in that it strips specified characters out of a string of text or numbers.

So, let's use the example we used yesterday:
110308/20/7R13504

Here, we know that the 20 in the middle represents a useful piece of information, like warehouse or factory or the number of pieces you need to produce. But, we also know that it's not useful stuck in that long string.

It is pretty simple to extract:
=MID(A2,8,2)

The formula says to go over 8 characters (including spaces) and return two characters starting with the 8th character.

The result of this formula is:
20

This is pretty easy stuff once you know about it. Definitely another good tool to have in your guru belt.

LEFT, RIGHT...Just Vote!

Let's say you've run a report and downloaded it to Excel. Let's also say that this report doesn't have a need by date field, but the need date is part of a reference code that looks like this:

110308/20/7R13504

Let's also say that you want to do a pivot table where all your records are summarized by need by date and you are OK with the format mmddyy (for now).

Here's what you do:

=left(a2,6)

This formula will return this result:
110308

The RIGHT function works the same way:
=right(a2,7)

Will return this result:
7R13504

These are definitely nice functions to have in your toolbox. And, there is more. I'll look at MID tomorrow.

Oh, and if you're in the US and you're on the left or on the right, I don't care. VOTE TODAY!

OR

I am going to try and keep this short and simple and to the point. If you knew me at all, you'd know that's a challenge, but I'm really going to try!

OR is a function like AND and ISERROR in that it returns a TRUE or FALSE result. So, like AND and ISERROR, OR is pretty worthless unless you couple it with an IF. It's like a Peanut Butter and Jelly sandwich without the bread.

If you create a formula like this:
=OR(B2="A",C2<=D2)
You will either get a TRUE or a FALSE result.

If that result is TRUE, you can use an IF statement to have it return a specific value, a result of a formula, or the value in a nearby cell. Or, 60 other things you might come up with. Here's an example where if at least one argument in my OR statement is TRUE, the formula returns the value found in H2. Otherwise, it returns a value of blank.

IF(OR(B2="A",C2<=D2),H2,"")

Regarding syntax, please note that your arguments in the OR statement are simply separated by commas. I often try to sneak one of these () in there, but that's just silly. Commas only. (The same is true for AND).

Finally, your OR statement is limited to only 30 arguments. Just 30. 31 is 1 too many, OK? (The same is true for AND).

Just in case I didn't make it clear enough, and because I think I've made my posts on AND and ISERROR too long and complex: OR, AND, ISERROR are all similar in that they all return TRUE or FALSE results and don't make much sense if you don't use them in conjunction with IF.

Page Setup Interface and printing the prettiest report in the office

These aren't all really tricks, but I used Excel for a year or two before I figured them out or learned them through "tribal knowledge."*

When you click File-->Page Setup, you will get this interface.


The thing I like to change here, and I've mentioned it before, is to change Scaling to Fit to and then choose 1 pages wide by BLANK tall. Do this and Excel will scale it so that it's not too wide for one page and will use as many pages as it needs.

I rarely do anything with Margins, so I won't cover it here. Basically, if you need to mess with Margins, do it here.

Header/Footer is very nice:

You can add a header and a footer to print on every page of your report here. Click the pull downs and see some existing options or choose the custom header or footer buttons to get this interface


Here's a tour of the buttons;

The first button allows you to change the font in your header or footer.

The second button shows the current page number and the third button shows the total number of pages in the report. I like to hit the button for page number type in the word "of" and then hit the button for total number of pages (Page 1 of 20)

The fourth button shows the date and the fifth button shows the time. Here, I like to show the date, hit enter and show the time below the date. Not sure why. I just do.

The sixth button shows the Path and File. This is really nice if you've buried your file in a complex file structure and need to find is 6 months later when the CEO realizes that it's the best report EVAH!

The seventh button shows just the file name. The eighth button shows the tab name. (For the love of VLOOKUP, If you use this, change the tab name from Sheet1!)

The eighth button allows you to insert a picture. A logo for example, you Excel fancypants.

That last gray button is grayed out. I'm not sure how to un-gray it.

The point with Header and Footer is don't try to make your own on the spreadsheet itself. Do it here, instead!

Finally, we have Sheet. I like this tab of the Page Setup Interface best:


Under Print titles, if you define rows to repeat at top, that row will repeat on each printed page of your report. Same is true for columns! This will make the report so easy to work with for those people who like printed copies. Do this for them!

Under print, I always check Gridlines. Because I like my gridlines to print without giving everything a border in the cell format.

I normally don't use anything else, so I won't bore you with the details because if you've gotten this far, you're a super champ as it is!

Good day!

*Does my corporate America speak impress??

IF and AND, together

If statements are a pretty powerful tool in your Guru belt. If you know IF, you probably know more than 50% of the people in your office. We've used IF here in conjunction with ISERROR to format your spreadsheet nicely. Today, I want to use IF with AND because I really like these two functions together.

Let's say you work for a company who only pays in increments of 15 minutes. So, if you show up at 2:03, you start getting paid at 2:15. And, if you leave at 5:03, you stop getting paid at 5:00. I know! Corporate America*, right? Geez.


Well, if you work in Corporate America and you're punching a time clock, all of that is already worked out in the payroll software, so you wouldn't really have any need to do this exercise. But, it's the best example I can think of...

The End Result:


Here's the formula that will do it:
=IF(AND(MINUTE(C2)>45,MINUTE(C2)<=59),TIME((HOUR(C2)+1),0,0),IF(AND(MINUTE(C2)>30,MINUTE(C2)<=45),TIME(HOUR(C2),45,0),IF(AND(MINUTE(C2)>15,MINUTE(C2)<=30),TIME(HOUR(C2),30,0),IF(AND(MINUTE(C2)>0,MINUTE(C2)<=15),TIME(HOUR(C2),15,0),C2))))

Well, as they say in corporate America, how do you eat an elephant? One bite at a time! So, let's take this in little elephant chunks.** (Who eats elephants, anyway?)

=IF(AND(MINUTE(C2)>45,MINUTE(C2)<=59),TIME((HOUR(C2)+1),0,0)

The IF Statement is:
IF(AND(MINUTE(C2)>45,MINUTE(C2)<=59)
IF this is true: the minute is greater than 45 AND less than 59
Return this value:
TIME((HOUR(C2)+1),0,0)
What a neat new function! With the TIME function, the first value represents the hour (in this case my starting hour + 1), the second position represents the minutes, and the third value represents seconds.

If my IF statement was FALSE, return this value:
IF(AND(MINUTE(C2)>30,MINUTE(C2)<=45),TIME(HOUR(C2),45,0)

Another IF statement! This is called Nesting IF statements, and you can only nest 7 IF statements in any one formula. But, you can add ANDs and ORs and test more than 7 conditions. Get creative, play with it!

In English, this says if my minute value is between 30 and 45, return the TIME of the hour of my original start time and 45 minutes.

If the value is false, another IF Statement! I think you get the drift. At the end, if none of the conditions are met in the 4 IF statements, then it will return the value of the original start time. If the formula is written right, it should always be the top of the hour so it falls in line with all the Corporate America BS guidelines. I hate that the man is always out to get me!

I did another formula for the end time:

IF(AND(MINUTE(D2)>=45,MINUTE(D2)<59),TIME(HOUR(D2),45,0),IF(AND(MINUTE(D2)>=30,MINUTE(D2)<45),TIME(HOUR(D2),30,0),IF(AND(MINUTE(D2)>=15,MINUTE(D2)<30),TIME(HOUR(D2),15,0),IF(AND(MINUTE(D2)>=0,MINUTE(D2)<15),TIME(HOUR(D2),0,0),D2))))

I actually just dragged over the original formula, and then changed things up a bit so that the workers don't get paid for any minutes they work past the quarter hour until the next quarter hour begins. Take a close read and if you need the English version, let me know...

*I kid about Corporate America! I love Corporate America! Corporate America loves me! I've never seen such a punishing time clock in Corporate America.
**I kid again! I love elephants, but I'm not so sure they love me back.

Grouping without the subtotal wizard

When you use the subtotal wizard, your data is grouped, and you can use buttons to expand and collapse your group:
Expanded:


Collapsed:


Read more about subtotals here

You can also group data in Excel without using the subtotal wizard. I had an occasion to do this just the other day, in fact.

All you do is highlight the rows of data that should be grouped together. Then, choose:
Data-->Group and Outline-->Group

Repeat as necessary for the different data groups you want to create.

Why would you want to do this? I'm not sure. But, I did it to separate the top 90% of my purchased items from everything else. I could have assigned them all an ABC Code (A=top 90%, B=90-95%, C=95-100%) and added subtotals, but I didn't really want to do that, and I didn't need the subtotals. This was quick, easy, and very functional in this application.

Pretty Neat Trick!

Rank and Count, now together in one formula!

RANK is a nice function. You can use it to see where an item falls on a list. You can get that same information by sorting the list, but there will be times when another sort takes precedence, and RANK will save the day.

Start typing in the formula in the formula bar
=Rank(
and then hit the fx to bring up the Rank Wizard box
(there's other ways to do this. Pick the one you like best.)

Here's the wizard box.


The number is the value that you want to rank. The Ref is the range of numbers that are being ranked. Be sure to make this absolute using the dollar signs or your range will change when you copy and paste your formula down. The order specifies order. If you leave it blank, it assumes 0 which is descending. If you choose 1, it is ascending. In other words, the largest number in the list will be ranked number 1 if you choose 0 or leave it blank. The largest number in the list will be ranked last if you choose 1.

If the list includes values which are the same, they will have the same rank.

Assuming that a rank is only important in relation to the number of values, I like to add a count.

In this instance, I want to count values in a spreadsheet that I will be adding to over time. So, instead of counting an absolute list such as $B$2:$B$67, I'm counting the whole column. My count formula looks like this:
=COUNT(B:B)

If I wanted to count an absolute list, it would look like this:
=COUNT($B$2:$B$67)

When I put the rank together with the count the formula looks like this:
=RANK(B2,$B$2:$B$68)&" of "& COUNT(B:B)

I used the "&" to concatenate the two functions together with the text string " of ".

The spreadsheet looks like this:


Putting the two formulas together means that I can get rid of columns G and H entirely. I just left them in to show my work.

I don't use Rank very often. I do use COUNT pretty often. I rarely use the two together. But, when I've needed them, it sure has been nice...

When Grouping by Dates in Pivot Tables won't work

I started telling you how much I loved Pivot tables, and then abandoned my blog. I apologize.

After I discovered that you can group by dates in Pivot tables, I also realized that sometimes it didn't work. It took me awhile, but I finally figured it out. Basically, if your data set includes blanks, the Pivot table is unable to Group By Dates. Why? Because Blank is not a valid date. I'm hoping Microsoft fixes this in the next iteration. They may already have, but I don't have that version of Excel, yet.

Let me walk you through why you would want to include blank rows in your data set, how to get rid of the blanks in your pivot table and how to group by dates in your pivot table without using the group by function.

There are times when I create a Pivot Table when I know that my source data will not always be $A$1:$C$67


Knowing that I will continue to add to my source data, I choose A:C as my Pivot table. This would happen if you, as a flea market owner, intend to continue to add rows to your original source data as you continue to sell items. You could use the pivot table wizard to redefine your data set every day, or you could expand your data set and just refresh your pivot table as needed.


Now, you have a row for (blank) in your pivot table. Bleh.


And, you don't really want that because it serves no purpose in this application, so you get rid of that blank line by clicking the pull down on the item column, scrolling through until you find blank, and then un-checking it.


But, now your nicely summarized months are gone, replaced by specific dates because a blank is not a valid date format. What was once nice to look it:


Now makes you cringe and frown.


When you try to resummarize it by month following instructions provided in my post Pivot tables and why I love them, instead of getting the nice box where you can choose Months, years, hours, quarters, etc, you just get one group called Group 1.

But, you've reached the conclusion that you will continue to add to your original data set and it's not feasible to redefine the data set every time you want to see fresh data.

Here's what you do!
Go back to your original data set and add three calculated fields. One each for Month, Year, and Quarter


The calculation for month is above. The calculation for year is pretty straightforward:
=YEAR(C2)

The calculation for Quarter is more involved:
=IF(OR(D2=1,D2=2,D2=3),1,IF(OR(D2=4,D2=5,D2=6),2,IF(OR(D2=7,D2=8,D2=9),3,4)))

In English, if the month is 1, 2 or 3, the quarter is 1. If the month is 3, 4 or 5, the quarter is 2. If the month is 7, 8 or 9, the quarter is 3. Otherwise, the quarter is 4.

I like that calculation. They're may be a different way to get at quarters, but this works. You could also change the 1 to "Q1", etc, if you want to get fancy.

Now, go back to your pivot table, right click and choose "Pivot Table Wizard."


It will start you at Step 3 of 3, and you are redefining your data set here and you need to back to step 3. So, choose Back. You can either drag your cursor from A to F, or just type it in manually:
Data!$A:$F

Now, choose next and then finish. Now, in your field list, you have the newly created calculated columns:


Drag and drop years, months, and quarter to summarize the data the way that you like. Change up as needed.


Do those odd column totals bug you? Me, too.

Hover your mouse over those year total columns until you see a black arrow, then right click. This will highlight all year total columns. Now, right click and choose hide.


If you want to get it back, right click on the word, "YEAR" and choose field settings.


Under subtotals, click automatic. You can also choose sum (same at automatic), count, average, etc. Or, a combination of any or all of those in the option box. Use this wisely, people. You don't want your pivot table to get too complex or you'll lose your users which may just be yourself and it's no good to lose yourself over a pivot table.

So, now it's looking pretty OK.


But, you probably want to format it. So, hover your mouse on one of your category totals. Once you get that hover arrow, left click to highlight all category totals as shown in the image above. Now, right click and format as you see fit.

That was a little more than When Grouping By Dates in Pivot Tables won't work. But, what can I tell you. I get wordy...