How to Become an Excel Wizard

by Magnus Geijer

At almost every job site, there is that one go-to person for all Excel questions. This is a person who can turn your mess of seemingly random numbers into a neatly-organized, smartly-colorized, perfectly sensible display of data, with only a few keystrokes. This article may not turn you into that person overnight, but it will give you a helping hand with some of the easiest hints and tips for everyday Excel use.

Printing wide content to one page

Excel documents have a tendency to get wide. Sometimes they become wider than what Excel will default to printing to one page. Many a hair has been torn out by users trying to shrink the print size to the correct percentage under Print Properties, or attempting to use the "Fit to Page" option under the same menu. The solution here is simple. Select "View," then "Page Break Preview." This view will give you a layout of the pages as they would print at the current setup. If your document is larger than what Excel seems to want to fit on one single sheet of paper, there will be one or more dotted blue lines. Click, hold and drag the dotted blue lines to include as much as necessary. When you're done, select "View" and "Normal" to go back to your regular document editing view. If you save the document at this point it will print the same way every time you open the document, until you change the print margins again.

Conditional formatting

The ability to colorize things in Excel is a definite boon. For one thing, a completely black-and-white document can be dreadfully dull, but colors can also be used to highlight important or abnormal information. The most basic way to change colors is to select the fields of interest and change the color of text and background, but for files that are reused, this can take a lot of time. What you want is a document that knows when to highlight things on its own, and as it turns out, Excel does this very nicely. Say that you have a file where you want to highlight any number that is higher than 1,000 in red. Select the fields where you want this conditional formatting to apply, and select "Format" and then "Conditional Formatting." In the first box, select "Cell value is." In the second box, select "greater than." In the third box, type in "1000." As Excel's default conditional formatting is a red background with black text, all you have to do now is to select "OK." Any number that exceeds 1,000 entered into the fields you selected will now be presented on a red background. Unlike a formula, the conditional formatting remains in place until you either remove the formatted cells, or remove the conditional formatting, but not if you just delete the content of the cells.

Linking files

One common annoyance with Excel reports is that sometimes it feels like you have to copy and paste a million different things into your file to give it the data needed to produce the final spreadsheet. It is not unusual to receive the data in one or several different files via email, for example. As it turns out, you don't have to copy and paste the data every time, as long as the files that deliver the data to you have the same layout in each revision. Say that you receive one file from Jane and one from John. Jane's file contains the numbers for the San Francisco office, and John's file contains the numbers from the Sydney office. What you want is a file that displays the total from Jane's file and the total from John's file. Save both files to a convenient folder, and open them from there. Now click in the cell in your file where you want the total from Jane's file and type "=". Then click the cell in Jane's file that holds the information that you wish to display. Do the same with John's file, and you're set. When you receive updated data from Jane and John, all you have to do is replace the old files with the new ones, and your document will pull the updated information.

$

Shortly after you learn the beauty of auto-filling a formula, you discover the frustration of formulae that auto-fill more than you want. Say that you have a column full of item prices in column A. In cell B1 you have the tax rate. In C1 you have a formula that multiplies A1 with B1, =SUM(A1*B1). Selecting C1 and dragging down will auto-fill the formula to apply to all the values in the A column, giving you the price including tax for all items in column A, right? Wrong. Auto-filling the formula will multiply A2 with B2, A3 with B3, etc. What you want to do is make Excel increase the A number, but keep B1 as a constant, as in A1 multiplied with B1, A2 with B1, A3 with B1, etc. Luckily, all you have to do is change the formula to =SUM(A1*B$1), and your formula will auto-fill as you want it to.

Obviously, these are just a few minor items in a vast array of functions available in Excel, but hopefully they will be of use to you. Excel can be a powerful tool in many business situations, and the learning curve is not as steep as it may appear. Browsing through the menus can get you started, and don't forget to make use of the help functions, which are surprisingly useful.