I need your help!

I thought that I knew a lot about Excel, but the truth is, I can't think of one more thing to share with you. Although if you master the things covered here, you WILL be the Excel Expert in your office. You will shock and amaze your office mates, in general. But, I want to answer more questions. I can't think of anything to answer, though. Will you please ask me a question, friend?

How you found me

Here are some keywords that brought you here. I wish I knew what you were looking for because I think I could help.

Excel Bring Second Value: I don't know what that could mean, but maybe you could try an If statement? Give me more, and I think I can help.

VLOOOKUP: I must have a typo like you. The problem here, friend, is one too many O's in your function. If you figured that out and still need more information, see here:VLOOKUP

Table Array Vlookup: I hope the VLOOKUP post helped in some way.

VLOOKUP Second Result: I don't know, but might I suggest an IF combined with an ISERROR and VLOOKUP, perhaps? See here.

My favorite: vlookup getting col_index_num from a list: You can totally do this, but I don't know why you would want to:
It looks like this:
=VLOOKUP(A7,Data!$A$2:$F$67,Sheet1!A12,0) When you fill in the formula, it moves down the list, too. That was pretty easy to figure out, though, so I'm sure the super user looking for this solution won't be happy with what I've shown him here.

stop copy and paste data validation: Have you tried to copy and then paste special, paste values?

complicated vlookup returning #value: There could be a lot of things to check here. You might try formatting the lookup values on the original worksheet and the in the table array exactly the same. When I'm really struggling and I know there should be a match, I use Data-->Text to columns to make sure everything is formatted exactly the same. Plus, if you can, highlight your lookup values, do a CTRL-F to find all spaces, and then replace all the spaces with nothing. This only works if your lookup values don't have spaces legitimately.

I really am happy to answer questions. It's quite possible I won't be able to help, but if I can, I will be happy to. Click on the Contact Me box, and I will be happy to help you out.

Pivoting the data, Lesson Two

So, you've got a Pivot Table and you think it's OK. But now, you really want to pivot the data.


Put your cursor on Item right there in the pivot table. Drag it to Columns section of your pivot table, where the months currently are. Drop it. Now, you have this:


It's an interesting, but useless, way to present data. So, keep pivoting. Click on Months and drag it to the row section of your pivot table. Now, you have this:


I don't really like this view much, either, but there may be instances when you have applications when pivoting the data like this would be beneficial.

Next time, we can start looking at formatting the data. It was when I started trying to format the data that I realized that PIVOT TABLES<>Excel.

Pivot Tables and why I Love Them

The thing that is so fantastic about Pivot tables is that you can summarize huge amounts of data to help you and your users better understand what your huge amount of data has to say to you. You can also summarize small amount of data, too. And, then once you've summarized your data in a Pivot Table, you can "pivot" the data to look at in a different way.

I used to hate pivot tables. But that was back when they confused me, and I couldn't understand why they were called "Pivot" tables. Now, I get them. I really, really get them. And, I love them. I hope you will, too.

I've alluded to this before. Pivot tables are pretty easy, but they aren't exactly like Excel. I don't know what Microsoft has to say about this, but I personally consider Pivot tables to be an application within an application. If you try to use the data in a Pivot table just like you would the data in Excel, you'll hate Pivot tables. So, before we even start, I want you to commit to thinking of Pivot tables as its own application. Can you do that? Doing that will be the thing that takes you from User to Super-User! Yay, are all your dreams coming true?

Assuming you can commit, let's pretend that you are a flea market/pet shop owner. You have been keeping a list of the items that you've sold and when you've sold them, but so far, that data isn't really speaking to you. It's not telling you a story.


Click on Data-->PivotTable and PivotChart Report to bring up this wizard:


Because this is a basic lesson, let's go with the defaults, and then click NEXT. Excel is so clever, that it knows where your data is and will highlight it for you, as long as it contiguous. For this example, go with what Excel suggests:


and just click next.



Go with the defaults here and click Finish. You can choose where you want the pivot table to go right now, if you are so inclined. Doesn't really matter, though. You can copy and paste the whole thing if you need to move it somewhere else.

Here's what you will see:


Now just start dragging and dropping.

Oh, gosh. Before you start, let me give you some definitions:
Row Fields: Data that will appear in the Rows.
Column Fields: Data that will appear in the Columns.
Page Fields: I don't use this very often. And, I'm not missing out on much, either.
Data Fields: Here's where things get summarized. So, the data field will always be a number. It can be a sum, average, count, min, max, etc. You can put text fields here and then count them. You can put date fields here and count, min, max or average them. Or you can put a number data here and summarize it however you want.

I normally start with the Row Fields first. In this case, I want to see a summary of the items sold. So, click on ITEMS and drag and drop it to the Row Fields Box. Now Drag over Qty Sold to the data box.
Check it out! You've summarized your data by item.


So, you can see that you sold 97 Llamas, but only 21 Llama harness. There's an opportunity right there, I think.

This view is interesting, for sure. But, it doesn't give you any ideas about when your items were sold. Go back to your field list, drag and drop "Date Sold" to the Row Block. Say, now that's interesting.


But, you'd like to see it with the dates across the top. You can do that. Grab Date Sold, either from the field list or the pivot table, and drag it to the Column Block. Now look!


This is not easy to look at, though, is it? Since Pivot Tables are so great at summarizing, you don't want to do all that paging over. Ick. Pivot tables has a solution. Right click on any date in the column headings. Choose Group and Show Detail and then Group.


You get this dialog box.


To make things easy, choose months. You get this view. NICE.


I hope this has been educational. Tomorrow, I'll show you how to actually Pivot that data, really turning it on its side.

Love Pivot Tables.

Click here for the sample data to play with:
Pivot Data

Advanced Filter

OK, let's say you have a list of items. It's a long list, and you know there are duplicates in the list. You want to condense the list to its unique items. You could use a pivot table or subtotals, but that's really too in-depth for you for this list at this point in time.

Advanced Filter to the rescue.

Quick, highlight your list of items. Or, click on the whole column. Now, click on Data-->Filter-->Advanced Filter.

You will see this dialog box:

Click on "Copy to another location"

Doing that will un-gray the "Copy to" box. Use your zoomy box to choose where your list with unique records only will reside. Now, click the "unique records only" box. Hit OK. You're done. Now, you have a comprehensive list of unique items.

Quick and Easy.

Iserror

If you are obsessed with both formatting and efficiency, you just have to add iserror to your bag of tricks.

The way I use iserror is to keep those error messages off my spreadsheets. That isn't the only thing you can do with iserror, but it's how I use it.

Let's say that you have mastered VLOOKUP. But your two lists don't contain all of the same data. You don't really care about that. If your VLOOKUP doesn't find a value, that's OK. You just don't want to see an #N/A.

Here's what you do. First, build your VLOOKUP. GO ahead. Use the wizard. You know how.
=VLOOKUP(A2,[Phone.xls]Sheet1!$A$2:$B$312,2,0)
Now, add the iserror:
=ISERROR(VLOOKUP(A2,[Phone.xls]Sheet1!$A$2:$B$312,2,0))

This function is just asking a True or False Question. In this case, it is asking is my VLOOKUP returning a value or an error?

If it finds a value, the answer is NO or FALSE. If it finds no value and returns an error, the answer is YES or TRUE. You don't want to see TRUE or FALSE, though. You want to see a value or nothing. So, you need to use an IF.

=IF(ISERROR(VLOOKUP(A2,[Phone.xls]Sheet1!$A$2:$B$312,2,0)),"",VLOOKUP(A2,[Phone.xls]
Sheet1!$A$2:$B$312,2,0))

Now, the formula can be read like this:
If my VLOOKUP returns an error, enter a blank (""), otherwise, return the VLOOKUP result.

Although not slam dunk easy, if you take this in little chunks, it will work out just fine for you. And, it will save you time because you won't have to bother deleting the #N/A. So annoying.

Data Validation (aka, Dropdown Box)

If you want to use Excel as a Data Collection Tool, Data Validation is an absolute must. Data Validation allows the user to pick a value from a drop down box. That's not all it does, though. You can also add a data validation that will only allow values that are within a predefined range. You can also add data validation that checks the text length and makes sure it's within a predefined range.

When you set up data validation so that the user can pick a value from a list, the user will see a drop down box. In all other instances, the user will NOT see a drop down list.

Choose Data-->Validation and you'll see this dialog box:

If you choose whole number, decimal, date, time or text length, you get these options:

Either enter values in the boxes, or choose a cell that contains the value you'll be using to validate your data. The nice thing about choosing a cell is that you can change what is being validated without actually going into this box.

The other two tabs are pretty straightforward. On the input message tab, you have three entry options. The first is a check box that allows you to Show Message when cell is collected. The second is Title, and the last is the message. Here's a neat trick. Let's say you choose Any Value on the settings tab (so, your data validation isn't validating anything. It's just allowing ANY VALUE). Then you check the checkbox on the Input Message Tab. Then you enter a title and message. When your user lands on the cell with the data validation, they get a pop-up message. You get the message without restricting what data goes into the cell. Or, restrict the data, and still get the pop-up message. Either way is fine. But, if you'd been looking for a way, besides comments, to alert your user to something when they land in a cell, use Data Validation for its messaging. It's a good thing.

The last tab is Error Alert. You only use this if you are actually setting up a data validation that restricts data (You don't need it if you choose ANY VALUE on the settings tab). When your user enters invalid data, they get your error message that you created telling them what they did wrong. There are 4 inputs on this tab:
1. A check box allowing you turn off or on the error message.
2. A style: choose between STOP, WARNING, or INFORMATION.
3. Title
4. Error Message.
If you need to inform your user using Excel, be sure to include an error message.

I want to focus on lists. I've never used custom, and all the others are pretty straightforward. So, lists.

On the settings tab of the Data Validation Dialog box, when you choose Lists, you get 4 other inputs.
1. Ignore Blank. Check this and the user can make it through the cell without choosing or entering a value.
2. In-cell drop down. Check this, and your user can choose from a list of values.
3. Source. Here, you have options. You can enter the list right there in the box, separating the values with commas. Or, click on the zoomy box, and then highlight the list you want your user to choose from. If you do this, you are limited to a list of data on the worksheet that contains your data validation. If you want to move your lists of data to another tab, you can do that, too, but it's not quite as straightforward. But, it's easy once you've done it a couple of times.

Go to another tab and do whatever you have to do get your list together (type it, copy and paste, whatever suits you.) Now, highlight that list. Put your cursor in the name box, and type the name of the range. Name it whatever you please. But, wait! Where is the elusive name box? you ask.

There it is. In the top left corner. I circled it and called it NAME BOX. If you don't type over it, it normally contains your current cell address.

Once you have your named range, in the data validation dialog box on the settings tab, type =NAME BOX (except substitute the name that you have chosen. If you need to change the list in the future, choose Insert-->Name-->Define. This will bring up a dialog box where you can change the cells that are included in your named range. Your named range can only be one column of data if you plan on using it for a drop down box for data validation.

Here's what it looks like:

If you or your user tries to enter a value that is not on your list, they will get an error message. They either get the error message that you defined or a generic message.

Drop Down Boxes and Data Validation.

Fun for all.

Subtotal

Using the Excel wizard to subtotal data can come in handy, and the final presentation is pretty nice, too. It has recently fallen out of favor with me personally because I've discovered and fallen in love with pivot tables. I say that in real life, too. I love pivot tables.

Regardless, here's how you do it:
Step 1: Sort the data based on the column that has the data that you want to subtotal. Let's say you have a list of SKUs (Stock Keeping Units), their associated A,B,C code, along with their current value in inventory. You want to subtotal based on the ABC code, so sort by the column that contains that data. Now, highlight your data.
Step 2: Select Data-->Subtotals. You will get this dialogue box:


You can make plenty of changes here. But for this simple subtotal, I like what it's telling you it's going to do. At each change in ABC, it is going to sum up the total in the Value Column. You can see here that if your list isn't sorted, it's going to add a subtotal to the value column every time there's a change to the ABC. (That would be very bad...) Since I have it sorted right, I expect to there to be three subtotals. You can use the following subtotaling functions:
SUM
COUNT
AVERAGE
MAX
MIN
PRODUCT
COUNT NUMS
STDEV
STDEVP
VAR
VARP

Here's what your subtotaled data will look like:

Click on the + or - sign to expand or collapse data. Here's what it looks like all collapsed.

You can also use the 1, 2 or 3 in the upper left corner to show and hide data. Hit 1 to collapse it completely, 2 to show the subtotaled information and 3 to show all the data along with the subtotals.

There is more to subtotals. Lots more. Let's say you have a much more complex set of data. You want to add subtotals to your subtotals. You can do that with the wizard. Highlight your already subtotaled data, and then choose data-->subtotals once again. This time, uncheck the box that says "Replace Current Subtotals." That will add another layer of subtotals. And, the 1,2,3 you see currently in the upper left corner expand to 4 and 5. I don't know how many times you can do this. But, my advice would be limit it to 3 or 4. (Did I say 3 or 4? I meant to say 2.) Otherwise, you've gotten too complex for your subtotals anyway.

You can also do different types of subtotals on one report, though you have to leave the comfort of the wizard for that.

If you click on the subtotal function, you will see that it's a simple formula:
=SUBTOTAL(9,C2:C30)

The thing that makes this one sum up C2:C30 is the 9. If you replaced the 9 with a 1, it would average the data. Replace it with a 4, and it returns the max value. See below for more of what you can do with the subtotal function.
1: Average
2: Count
3: Counta
4: Max
5: Min
9: Sum
(See Excel help for the complete list. Choose topic -> Subtotal

When using the wizard, you can only have it do 1 function per column. You do not have the option of summing the inventory value and counting the SKUs when you use the wizard. What I have done to get around this and still use the wizard is to tell it to sum the Item and the Value. Once it does it's thing and subtotal my data, I highlight the column containing items, and then do a ctrl-F to Find all cells containing:
subtotal(9,

Then, I replace that with
subtotal(3,

There may actually be a better way to do this. If you know of it and feel like sharing, please do. In the meantime, this works!

Happy subtotaling!!!

Quick Answers to Searcher's Questions

Can you use concatenate in a VLOOKUP?
The answer appears to be YES and NO.

=VLOOKUP(CONCATENATE(A6,B6),'Table Array'!$A$2:$D$6,4,0)

Your lookup_value CAN be a formula, just like you see above. It does not appear that your table array can include formulas, though. I suggest that you concatenate your lookup value in the VLOOKUP, and then create a column for the same concatenation on your sheet containing the table array. This is a good question, though, and I can see how my solution might not be satisfactory. I'll keep looking into this one.

How do I make a table array absolute?
Example: table array is NOT absolute
=VLOOKUP(CONCATENATE(A6,B6),'Table Array'!A2:D6,4,0)

Example: table array IS absolute
=VLOOKUP(CONCATENATE(A6,B6),'Table Array'!$A$2:$D$6,4,0)

The only difference is that you add the dollar signs in front of the cell's column and row references. I recommend, when in doubt, always make the table array absolute. I can't think of any reason not to. But, if it isn't absolute, you may not get the results you expect. So, always absolute!

Excel Label Formatting:
Someone searched on this and found me, but did not actually ask a question. I'm going to assume the question refers to mailing labels or something like that. My suggestion is to make your list in excel, and use Mail Merge in Word. I'm not an expert there, but I have used it, and it works very nicely.

Choosing from a Number list
What this searcher is looking for is data validation. Click on the cell where you want to be able to choose from a number list, and then click data-->validation. This brings up a dialogue box. Under validation criteria, choose List. In source box, enter the cells that contain your list of numbers, or type the number in yourself, separated by commas. If you want your list of numbers to be somewhere on the worksheet, just highlight them. If you want them on a different sheet in your workbook, you have to name the range, and then make your list equal to your named range. Since this is a quick answer, I'll say look at Excel Help for named ranges to figure out how to make this happen. Leave me a comment or send me an email if you need an explanation on this.

Change the format of phone numbers in Excel
Do this by bringing up the format cell box. Now, on the number tab, click on custom. Type in the custom format in the Type box. Let's say your format is currently:
###-###-####
This would format 5551112222 as 555-111-2222
Change the type so that it looks like this:
(1)-###-###-####
Now, your phone number looks like this:
(1)-555-111-2222

Another searcher asks about:
Excel Function to Group like items
The quick answer is Pivot Tables. If you delve into this on your own, let me say that 1)They are awesome 2) They always work. 3) They're kinda simple, really 3)They don't really act like Excel and that makes them seem complex.

I plan on covering a whole set of Pivot Table topics as the days go by, so more on this later.

Today, a searcher asked about what seems like conditional formatting. He or she searched on:
If this column date less than show as color excel
They found me on the 7th page of their Google Search. I hope they found what they are loooking for and disovered the thing to do is use Conditional Formatting, Formula is Option.

Concatenate

Concatenate is a big word and a handy function!

It means to link together like a chain. And, that is exactly what you will do with this handy formula.

Click on your formula bar.


Type in the formula. You can combine your own text, like I did with the "/" and you can use values in your spreadsheet, like I did with the A2, B2, and C2.

Neat-o!

A few hints: if you use text and you need spaces, include those spaces between the quotation marks.

If you are building a date using concatenate, although it will look like a date, Excel thinks it's just a text field. Using format cells to change the format to a date still won't make Excel think it's a date. You'll have to change the formula to look like this:
=DATEVALUE(CONCATENATE(A2,"/",B2,"/",C2))
And, then change the format to make it look like a date. Now concatenate is a handy function, but Datevalue is even better. It's a guru function. Keep that one in your back pocket.

VLOOKUP

VLOOOKUP is definitely a guru function. Here it is...

VLOOKUP means vertical lookup. It's a database type function, and allows you to combine data from two different sources. The sources can be different lists on the same worksheet, different lists in the same workbook, or two different files all together. The choice is yours. For the most part, the process is the same.

Here's a scenario. Let's say you have a list that contains your classmates, their seat number, and their eye color. You have another list that contains your classmates' phone numbers. You want to combine the lists, but you've never heard of VLOOKUP. So, you try to sort them and then copy and paste the phone numbers manually. When you try this, you find out that your phone number list is shorter than your classmates information list. You start looking through trying to find the holes, but then your realize your list is 150 lines long. This will never work! VLOOKUP to the rescue.

You will use the VLOOKUP to find matching values between the two spreadsheets by looking VERTICALLY up and down the second sheet. Once it finds a match, you will instruct it to look to the value on the 2nd column over (in this case) and return it to the first spreadsheet. Life has never been easier.

Here's what the formula will loook like:
=VLOOKUP(A2,[Phone.xls]Sheet1!$A$2:$B$312,2,0)
ANATOMY of this formula
A2: Class mate name.
[Phone.xls]Sheet1!: the second spreadsheet that contains the phone numbers
$A$2:$B$312: the "table array." This contains classmate name and their phone number
0: This just means FALSE. Your choices are 1 or 0 or TRUE or FALSE. Choose FALSE to have the VLOOKUP do an exact match. I've never found any value in choosing 1 or TRUE. (CAREFUL! If you leave this blank, it defaults to 1 or TRUE, and you will probably not get the results you expect.)


This is called a formula bar. Click on the FX to bring up a wizard to build your VLOOKUP.


The lookup value will be your classmates' name. Or, if you doing some other type of activity, it is the value that is common between your two lists.

The table array is the 2nd list that has the data that you want to look up. This needs to include the whole list that may contain matching data. So, it goes from the very first name and phone number to the very last name and phone number.

The Col_Index_Num (Column Index Number) references the column that has the value you want it return to the first list. In this case, it's 2. But, it can be any value from 1 to 256 (but, gosh, that would get complicated!)

The last value in the box, for my money, is always 0. I don't know what to do with results that are a close, but not exact, match.

Now, drag that formula down for the whole list. You've saved yourself a lot of work!

That's it. I hope it's easier for you than I made it sound. I recommend practicing. And, if you ever find yourself sorting and manually matching things up, STOP! Use VLOOKUP. IT WORKS. IT WORKS EVERY TIME (although sometimes you have to massage it a bit.)

A few tips and things to remember:
If both lists are in the same workbook, the wizard will not make your table array absolute with the dollar signs. Make sure that you use dollar signs. Otherwise, when you drag down the formula, you will be changing your table array each time you drag it down.

VLOOKUP will always return the first value it find in the second list. If the second list contains two entries for a classmate with two different phone numbers, it will only return the first phone number it finds in the list. If you sorted the list differently, it would return the other number.

If there is no matching value, it will return #N/A.

If you're getting an error and you know there are matches between the two lists, try and change the formatting on both lists so that the lookups are formatted exactly the same.

And, if that still doesn't work, imagine me standing over your shoulder. That's always done the trick for folks asking for Excel advice.

Good Luck!

Conditional Formatting--Formula is

The other choice with conditional formatting is "Formula is." It's a lot more dynamic than the "Cell Value is" option.

With this, you can format one cell based on the value in an entirely different cell. In fact, you can change the format of a whole row of cells based on the value of one value in that row.

Once you have created this conditional formatting, copy and paste as formatting only along the rest of the row. You'll get results like this:

Well, this is great, of course. But, if you had to do this for even 10 rows of data, you would be doing way too much work. Look again at this:
See the $F$2? The dollar signs mean that whenever you copy and paste the conditional formatting, it will always look to F2 to see if it's greater than 52000. If you want it to always look at column F, but you want it to look at the value on each row, type the formula like this:=$F2>52000. (Little tip for you keyboarders like me...don't use your arrow keys. I don't know what Excel is trying to do, but it starts changing things and is not helpful at all! Use your mouse to move around in the formula box.) Now, each cell where you have pasted this format has conditional formatting. It will always look to the value in F, and apply the formatting when it meets the defined condition. See below:


PASTING SPECIAL:
Using paste special is easy once you know how. Here's how.
After copying, right click where you want to paste the data-->Choose Paste Special. It will bring up this menu:

Now, choose formats.

Very handy, eh?

There are no less than two other ways to do this. You can also choose Edit-->Paste Special. Or, even more fun, choose View-->Toolbars-->Customize to get this menu:


Left Click and drag the Paste Formatting icon to your tool bar. While you're at it, pull the Paste Values icon up there, too. You know, scroll down to the bottom and grap the Select Visible Cells and drag that one up, too. It will come in handy later. Now that the two most often used paste special buttons are on your toolbar, use these whenever you want to perform one of these special pasting tasks.

Conditional Formatting--Cell Value Is

Once you get conditional formatting down, people will definitely start thinking of you as the local Excel Guru. It's a powerful, yet simple, concept. The concept is this: if the value in a cell meets a certain condition, it should be formatted differently than cells that don't meet the condition that you have defined. You can do conditional formatting very simply, or you can add layers of complexity to it. Today, I will show you the simplest way to do conditional formatting. Select the data (you can use shift-ctrl-arrow down or over to select) that you would like to apply the conditional formatting to, and then hit Format-->Conditional Formatting

It will bring up this box. Click on each pull down and choose your conditions. For today's subject, leave the first box as Cell Value is. The other option is "Formula is." That option is more complex, but more powerful, and will be covered later.

Now, define your conditions. Say that you have a series of numbers, and you want all numbers that are greater than or equal to $100,000 to look like this: $120,653 so that your attention is drawn to all of those numbers. Choose "greater than or equal to" from the drop down box, and then type 100,000 in the box next to that. Now, click the format button, and define the format that you want to see. Click on the FONT tab, and then choose Bold Italic.

You're done.

If you want to get fancy, click the button that says "Add>>". You can have up to three conditions. Do something like this:



You get a result like this:



And, if you change the values in cells so that they meet different conditions, the formatting will change, too.

Awesome.

I think it's called dynamic zooming...

So, here's the situation. You have too much on your screen to see all of it at once. Or, you've reached middle age and the font is just too small for your aging eyes to read. You can choose View-->Zoom and then choose a magnification level to fit more or less data on your screen. Or, you could click on the Magnification Glass icon with the + or - sign on it if you've put it on your tool bar. Or, you could just change the magnification level with your mouse if you have a roller ball.

Hit the ctrl key and then move your roller ball up to zoom in and move your roller ball down to zoom out.

I like it. I like it alot.

Autofilter

Have you ever spent time sorting items so that you could group like items together? Ever been just not satisfied spending your time that way? Try this instead:

Put your cursor on cell A2, and then choose Data-->Filter-->Autofilter

This will apply put a drop down on all columns that contain data.

Now, click on one of those drop downs:
Now, choose a specific value from the list, or select custom, and then create a custom autofilter.


You can use this custom autofilter to choose a date range (Is greater than or equal to your first date AND less than or equal to your end date). Or, you can use it to limit another column to all items that begin, contain or end with a character of your choosing.

Play with it. I think you'll really like it! And, SOON, I'll show you how to do a quick summary of filtered data.

Happy filtering!!

Printing Tip #1

Tired of struggling with page break preview? Try this instead:

Choose File-->Page Setup

Then, choose Fit to: 1 page(s) wide by BLANK tall. When you leave the # number of pages tall BLANK, Excel figures out the best formatting to print your entire spreadsheet.

Use this tip, and you will never have to choose page break preview again.

Congratulations.

Navigation Keyboard Shortcut #1

To navigate quickly in Excel, hit ctrl and the arrow that points in the direction you would like to go.

This will take you to the last row or column containing continuous data. Hit shift-ctrl and the arrow key of your choice to select data.

Keep hitting ctrl-arrow over and over as needed until you have navigated to the bottom, top, left or right of your spreadsheet. Put the shift (ctrl-shift-arrow) in there to select all the data you want.

Happy Trails! (Get it? Trails, Navigation? Get it??)