12 Tips and Tricks for Microsoft Excel

Excel isn't the sexiest application in the world--it has an unfortunate association with the type of Milton-esque office drones we all wish we weren't. All the same, it's a program that most people will end up having to use at some point in their life, and it's one with a lot of arcane secrets. Read on for 10 quick Microsoft Excel tips and tricks that will get you accounting like a pro in no time flat.

Time to Pivot

Pivot Tables are one of Excel’s most useful—and misunderstood—features. Here’s a quick lesson: Click within a chunk of data, click Insert, and select “Pivot Table.” Excel should automatically pick the entire range of said data (provided you don’t have any blank columns interrupting your dataset), and convert this into an editable, table-as-you-go kind of setup.  You can use the various “fields” on Excel’s new sidebar to basically create new tables of information (and calculations) on-the-fly.

Let’s Lookup!

Another widely used, but often confusing feature of Excel is Vlookup—the function by which one looks at data A, finds data A and data B in another spreadsheet, and slaps data B somewhere into the original spreadsheet. It’s tricky to explain, but easy to use: You’re basically using the contents of a single cell as an anchor for referencing information from one location to another. Master the command, and you’ll find a new use for it every day!

Conditional Formatting is your Friend

If formulas aren’t your thing, here’s an easy way to duplicate data between two columns. Highlight the columns and select Excel’s Conditional Formatting feature on the Home tab. Then, select the “Highlight Cells Rules” listing and pick whatever option fits your style. Duplicate values, for example, would highlight all repeated instances with a given color.

Insta-Jump to a Cell

Did you ever stop and wonder if you can actually modify the cell listing that appears to the left of the Excel’s formula bar?  You know, the one that automatically changes to tell you exactly row and cell you’re on at any given moment?  Guess what: You can.  Click on it, and then type in a given row name and column number—you’ll jump right to that cell as if you just hopped through a magical portal.  Really, it’s that fun.

Get Set for Macros

Trying to discuss Excel macros in a tiny paragraph is like trying to stuff an elephant into a car.  However, the first step toward being able to use Macros is an easy one: Open up Excel’s options (Jewel button > Excel Options) and select “Customize Ribbon.”  Click on the unchecked “Developer” box that’s on the right-side of the options window, and you’ll gain access to the “hidden” section of Excel that’s a quick shortcut to the almighty macro.

Why Not Numbers?

From time to time, Excel will foolishly store numbers like a “0” as text instead of a numeral, which can be a real pain if you’re trying to do anything with said information. So how do you fix 23,414 rows of the same error? Insert a new column and type a “1” into an empty cell. Select it, copy it to your clipboard, then select the range of numbers you’re looking to fix. Click on the drop-down menu under the Paste icon, click “Paste Special,” and select the options “Values” and “Multiply.”

Why Not Empties?

Dovetailing off the previous tip, Excel also has a nasty habit whereby information you’ve modified such that a cell should be empty… isn’t.  The cell has no values in it per se, but it still doesn’t register as empty for uses of the Count command or things like that. The easy way to fix this is to simply sort your affected columns in A-to-Z order, then manually select the range of “blank” cells starting at the bottom of the listing all the way down to Excel’s final row. Now, hit “Delete.”

Delete Blank Rows

If you have a bunch of data that’s separated out by blank rows of cells for whatever reason, it’s easy to just nuke these out of your dataset forever without having to do any kind of crazy sorting. Select a column, hit F5, click on Special, then select the “Blanks” option. With said blank rows now targeted, click over to Excel’s Home tab, select “Delete,” and choose the option for eliminating said rows.

Show Thy Formulas

This one’s quick, but super-effective: If you have a spreadsheet full of formulas and you want to see exactly how you’ve built all of your constructions, you can do this by hitting CTRL+~, which will instantly transform your spreadsheet from values to the formulas that constructed them.

Double-Click to Freedom!

Double-clicking various parts of the Excel interface can automate a number of functions, including: double-clicking on the Jewel to close Excel, selecting multiple columns and double-clicking on the separators to auto-adjust the widths of all, double-clicking on the tabs of Excel’s ribbon menu to minimize the whole thing, double-clicking the lower-right corner of a cell to Fill Down its contents based on the contents of the column to its left… the list goes on!  When in doubt, double-click.

Adding Multiple Lines of Text

It’s frustrating to try to add multiple lines of text to a given cell. How the hell do you do it? The answer is so easy, it’s almost shocking. When you want to insert a line break in a cell to split your text up in a more readable fashion, just hold down Alt and hit Enter. Technically, you’re also turning on “Wrap Text” for the affected cell as well.

Email… Anything!

Check out the Excel plugin RDBMail if you want to super-charge your ability to quickly email portions of a worksheet to an Outlook recipient. It adds a new tab to your Ribbon that you can use as a single-click utility for emailing your entire worksheet to a person or the pertinent parts that you’ve selected. You can also toggle between sending the worksheet as-is—formulas included—or sending off only the values of what you’ve been working on.
http://www.maximumpc.com