MVP #29: Great Excel Tips Most People Already Know

There are some very cool Excel tips that are popular because they are extremely useful.

Today I'll be sharing as many as I can.

1. Insert a Row.
Let's say you've got a list of items in alphabetical order, and you suddenly realized that you've skipped an item in the list. An item that starts with letter I and should be somewhere in the middle of your list.

What would you do?

You could type it out at after the last item and do a sorting.

Or you could simply insert a row before Jug and type the missing item.

To insert a Row, simply right click on the row you want your new roll to appear on top of. In this case I want the row to appear before Jug, so I will right click on the Jug row. To right click on a row, right click on the row number at the extreme left. Then select Insert.

Type in the missing item. And that's it! This tip can ease you Excel life immensely when you start working with separate reports/data from different people that you need to consolidate into one report.

2. Insert a Column
It's like inserting a row, but this time you'll right click on the column alphabet. This very handy when you need to add a new field to a report.

3. Oops. You can't paste what you've copied. Try Paste Special, Values.
This happens a lot with data that was created outside Excel. Exports from your company's CRM, ERP or Transaction logs. When you open them in Excel and try to copy out the parts you need to another Excel sheet, you get a strange error notification about the destination cells not being of the same type with the copied ones. 

Right clicking, selecting Paste Special, and Values usually fix this.

4. You can't insert a Pivot Table. Check for merged cells.
This problem can almost run you mad at first encounter. Merged cells can cause a lot of havoc and I avoid using them in my data reports. I use them for fancy dashboards but not in my core data sheets.

Whenever you get an unintelligent error notification while trying to insert Pivot Table, then check for merged cells.

5. Vlookup is not returning the field (col_index_num) I specified. Check for hidden columns.
I hate it when people send me reports with hidden columns. They cause all sorts of errors. Formulas, especially Vlookup, start acting strange.

Whenever your lookup formulas are not returning the field you indicate, look for hidden columns. They are usually the cause.

6. Vlookup showing #N/A for everything and you are sure something's wrong. Check for the field formats, maybe you've formatted one as Text and the other as Number.
This is the one problem that makes look silly before my clients. After I have done all the amazing data consolidation and cleaning stuffs, the part they thought would take a week to do, which I did in just 10 mins. Then all that's left is a simply Vlookup to fetch the dashboard reports data. Then vlookup is saying a value, they are sure exists, is not existing. And I can see the client's smiley face dim into a frown. Like all the cool stuffs I have been doing were just a waste of his time and he will have to do it manually himself. 

And almost all the time this is caused by having the value formatted as text in one table and as number in the lookup table. Other times, it's because I forgot to lock the table range (with $) in the formula before dragging it across all the rows.

7. Formulas not executing, but showing just the way I typed it. Set cell format to General.
This happens a lot with non Excel generated files. Files that were exported from other programs. Sometimes, the fields are set as Text and when you type in a formula, it just sits there in the cell and does nothing.

Change the cell or the entire field to General.

8. What you copied is not what is showing. Paste Special, Values.
When you copy cells that have formulas in them, it's like tilling a mine field. A disaster is always around the corner (copy). 

9. Sort won't work. Look for merged cells.
Yeah. Merged cells can be very nasty.

10. Can't copy from one Excel file to another. Or it's pasting as picture. Close one of the files and go to the other, do CTRL + O to open the one you closed.
You must have opened the two files from separate instances of the Excel program.

Finally, a bonus. 
Excel is not like other Office programs, doing CTRL + S too often is often a bad thing. You lose the ability to start all-over when something goes wrong (and something usually goes wrong in Excel). I only save after a big milestone. When I will almost cry if Excel crashes and I have to redo what I've done.