Ten Excel Tricks to Learn in Ten Minutes

Like a lot of office workers, I never took a formal class in Excel. I picked up some of my skills from watching coworkers, and the rest came from Google. Although I can do fancy things with pivot tables, IF statements, and VLOOKUPs, there are a lot of really basic tips and shortcuts I didn’t learn about until embarrassingly late in my career.

Below are a few of my “tricks” I learned too late. For extra credit, check out my article, Ctrl F That, for my favorite keyboard shortcuts.

Note: The specific instructions below are for Excel 2010, but most of them will work in Google Sheets and any version of Excel.

1. Use an Apostrophe to Format as Text
If you need to add something in text format, put an apostrophe before it. This is especially useful if you have a number with leading zeroes (like department 003071). If you were to type the numbers in, Excel would change it to a number format and cut off the zeroes on the left (3071). If you type an apostrophe first, like ‘003071, Excel will leave the zeroes in, but not show the apostrophe.

apostrophe.png

2. Concatenation
Isn’t that a fun word? Here’s another one: ampersand. In Excel, you can use ampersands to combine the text of two cells. So if you have a First Name column and Last Name column, combine them into a Full Name column by typing =[First Name cell] & [Last Name cell]. You have now successfully concatenated using ampersands.

If you want to add a space between the names, add another ampersand and a space in quotation marks to the middle, like this: =[First Name cell] & ” ” & [Last Name cell].

concatenation.png

The opposite of this is the Text to Columns feature, which could take a Full Name column and split it into First Name and Last Name columns. It’s a little too in depth to get into here, but I’d recommend a quick Google if you’re interested.

3. Format as a Table
Okay, it’s honestly embarrassing that I didn’t know this one until recently.

Format as Table

Just highlight your data, click Format as Table on the home page, and select a color scheme. It makes your table easy to read and adds a filter for each header. You can add totals just by right clicking and selecting Table>Totals Row.

It also makes it easy to add formulas—if you were to add a new “Averages” column and put an AVERAGE formula in the Bananas row, it would add averages for every other row as well.

If you want the pretty colors but hate working in tables, right click and select Table>Convert to Range>Yes to convert it back to normal.

4. Paste Special as Values
Sometimes after doing a ton of crazy formulas, you want all of your data to be translated into the output value only, with no formula shenanigans related to it. To do this, copy the cell(s) with the formula, then right click on the desired location and select the clipboard 123.pngicon under Paste Options. This will paste only the output value and get rid of the formula permanently.

WARNING: Only do this if you will never, ever need to use that formula again (perhaps after concatenating first and last names). Once it’s converted to text, you can’t convert it back to a formula.

5. Arrange Workbooks
A lot of times, I’ll  have multiple workbooks open at the same time and I’m the kind of weirdo that needs everything to fit on the screen perfectly. To “split-screen” workbooks within the Excel window, do View>Arrange All. Then select Vertical (or whatever floats your boat) and hit OK.

6. Conditional Formatting
There’s a lot you can do with conditional formatting, but the basic idea is pretty simple: you’re telling Excel to change the color of a cell (the format) based on a condition you set. For instance, you could make a cell turn red if the cell has a number between 67 and 100.

To start, highlight the data you’re looking at and click on Conditional Formatting on the Home tab. For our example, we’ll choose Highlight Cells and Between. Now tell it the range of numbers and the color you want these cells to be. You can select Custom Format from the drop-down menu if you want a more fun color.

Conditional Formatting 2.png

Conditional Formatting is a fun tool to play around with. You can do color gradients, use icons (like red flags or gold stars), or search for text instead of numbers. Below is an example of using the color gradient feature. I made a spreadsheet for average weekly temperatures for my city, with lower numbers in blue and higher numbers in red.

Conditional Formatting
These numbers are completely made up but it looks nice, right?

I would suggest playing around with this to see what you can do. If you mess up, you can always go to Conditional Formatting>Manage Rules or Conditional Formatting>Clear Rules.

7. Use the Average, Count, and Sum Display
Any time you highlight cells with numbers in them, Excel will automatically calculate the average, count, and sum at the bottom right. No formulas needed.

 

average, count, sum.png

If you don’t see these fields in your workbook, right click in the bottom right and select which calculations you want displayed.

8. Freeze Panes
Sometimes you’ll have a very long spreadsheet with important headers at the top. To keep your headers visible as you scroll down, you can actually “freeze” the top row. Go to the View tab and then select Freeze Panes> Freeze Top Row. If you need to undo this, go to Freeze Panes>Unfreeze Panes.

freeze panes.png

You can also freeze both the first column and the top row at the same time (similar to the View>Split feature). To do so, click on the B2 cell and then click Freeze Panes. As you scroll down, the top row will stay the same, and as you scroll right, the leftmost column will stay the same.

9. Hide or Unhide Columns or Rows
Sometimes you’ll have very wide spreadsheets with lots of unnecessary columns in the middle. To hide a column, select the entire column (click on the letter at the top), right click, and select Hide.

Make sure to unhide everything before sharing with others or else your boss may call you into her office to ask why you deleted half the spreadsheet. To unhide your column, Shift + click on the column to either side of it, right click, and select Unhide.

10. Absolute References with $ and F4
Hopefully, you already know that if you type a formula and drag it down to new cells, Excel will automatically change any cell references to match the row/column you drag it to (if not, consider this a bonus tip). For example, if you type =A1 + B1 in column C and then drag it down, Excel will automatically change it to =A2 + B2, then =A3 + B3, and so on.

To keep the reference cell from changing, you could put a dollar sign before the number to keep the row reference the same while dragging down (=A$1 + B$1) or before the letter to keep the column reference the same while dragging left or right (=$A1 + $B1).

To be extra safe, you could put a dollar sign before both the letter and the number to keep it from ever changing, no matter which direction you drag your formula. It would look like =$A$1 +$B$1.

But what if you hate going through your formulas to enter dollar signs? Click on the cell reference in the formula (e.g. A1) and then hit F4. Excel will automatically put both dollar signs in. If you keep pressing F4, it will cycle through the other options listed above.

 

What are your Excel tricks? Leave your answer in the comments.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s