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??

## Thursday, October 30, 2008

## Wednesday, October 29, 2008

### 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.

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.

## Tuesday, October 28, 2008

### 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!

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!

## Monday, October 27, 2008

### 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...

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...

## Saturday, October 25, 2008

### 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...

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...

## Wednesday, October 22, 2008

### Time Calculations

I was working on a timesheet in Excel the other day, and I was surprised to see that 4:00 PM minus 2:00 PM doesn't equal 2 hours. Very surprised indeed...

So, you know what I did? I hit F1. F1 and my husband and my friend Dawn are the three reasons I am the excel guru that I claim to be.

F1 was very helpful, but I took what it told me and expanded my new found knowledge to get it to do what I actually wanted my timesheet application to do. What I wanted my application to do was calculate the elapsed time in hours between a start and end time in one 24 hour period (as in, not to exceed a day).

Here's what I wanted to see:

From To Elapsed Time in Hours

2:00 PM 4:00 PM 2

When I subtracted 2:00 PM from 4:00 PM like this =(B2-A2), here's what I got:

From To Elapsed Time in Hours

2:00 PM 4:00 PM .0833

When displayed as time, .0833 = 2:00 AM

So, I think I get what it's doing, but it's not what I want.

F1 suggested a few functions, including the hour function. So, I changed my formula to this:

=HOUR(B2-A2)

And that converted .0833 to the number 2. Which was precisely what I wanted in this particular instance.

So, I pulled my formula down, and I discovered that when my start time was 2:00 and me end time was 4:45, my result was still the number 2.

I hit F1 again, and finished reading the help article. It was then that I discovered the MINUTE function.

Using the minute function on this data set:

From To

2:00 PM 4:45 PM

=MINUTE(B2-A2)

Yields this result:

45

So, I put the two functions together in this simple formula:

=HOUR(B2-A2)+(MINUTE(B2-A2)/60)

It now shows me the difference in hours plus the difference in minutes divided by 60.

From To Elapsed time in hours

2:00 PM 4:45 PM 2.7500

Easy, peasy puddin' pie.

Just keep in mind, this will not work if the time spans more than 24 hours.

So, you know what I did? I hit F1. F1 and my husband and my friend Dawn are the three reasons I am the excel guru that I claim to be.

F1 was very helpful, but I took what it told me and expanded my new found knowledge to get it to do what I actually wanted my timesheet application to do. What I wanted my application to do was calculate the elapsed time in hours between a start and end time in one 24 hour period (as in, not to exceed a day).

Here's what I wanted to see:

From To Elapsed Time in Hours

2:00 PM 4:00 PM 2

When I subtracted 2:00 PM from 4:00 PM like this =(B2-A2), here's what I got:

From To Elapsed Time in Hours

2:00 PM 4:00 PM .0833

When displayed as time, .0833 = 2:00 AM

So, I think I get what it's doing, but it's not what I want.

F1 suggested a few functions, including the hour function. So, I changed my formula to this:

=HOUR(B2-A2)

And that converted .0833 to the number 2. Which was precisely what I wanted in this particular instance.

So, I pulled my formula down, and I discovered that when my start time was 2:00 and me end time was 4:45, my result was still the number 2.

I hit F1 again, and finished reading the help article. It was then that I discovered the MINUTE function.

Using the minute function on this data set:

From To

2:00 PM 4:45 PM

=MINUTE(B2-A2)

Yields this result:

45

So, I put the two functions together in this simple formula:

=HOUR(B2-A2)+(MINUTE(B2-A2)/60)

It now shows me the difference in hours plus the difference in minutes divided by 60.

From To Elapsed time in hours

2:00 PM 4:45 PM 2.7500

Easy, peasy puddin' pie.

Just keep in mind, this will not work if the time spans more than 24 hours.

Subscribe to:
Posts (Atom)