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.