10 Excel Tricks For Non Technical Beginners

Spreadsheets are the bane of many office workers’ existences, and the subject of countless Dilbert strips. If they are a part of your professional life, though, you’re in luck. There are many simple, practical Excel tricks you can use to make your life easier, even if you’re not a computer wizard. Here are ten of the best.

10. The IF Formula

True to its name, this formula allows you to set certain conditions. If a particular thing is true, you can mark the row one way. If it’s false, it can be marked another. For example, salespeople who meet a particular numerical quota can be marked “pass” automatically, and the ones who need an Alec Baldwin pep talk are marked “fail” – without you having to type those words a million times.

9. Conditional Formatting

It may appear to be just a simple dropdown in the “Home” tab, but this one of the Excel tricks will turn you into a spreadsheet Picasso. You just select the range of cells you want to change, and you can format them any way you want. Make the text red, or make the background red. Hell, make the background light red and the text dark red! The world is your oyster.

8. Transpose

Sometimes you put data in a column, and then come to realize it would work much better in a row. This function makes that fixable in a split second. Copy the stuff you want to change, click on where you want to move it, and select “Paste Special.” Choose “Transpose” from there, and you’re good to go.

7. Keyboard Shortcuts

There’s no need to do all that copy-pasting, dragging, and dropping. Use some very simple keyboard shortcuts so you can finish your work faster and get back to checking Facebook. Here are some of the most common ones, courtesy of PC World:

Control-Down/Up Arrow = Moves to the top or bottom cell of the current column
Control-Left/Right Arrow = Moves to the cell furthest left or right in the current row
Control-Shift-Down/Up Arrow = Selects all the cells above or below the current cell
Shift-F11 = Creates a new blank worksheet within your workbook
F2 = opens the cell for editing in the formula bar
Control-Home = Navigates to cell A1
Control-End = Navigates to the last cell that contains data
Alt-= = Autosums the cells above the current cell

6. Remove Duplicates

The function of a spreadsheet can change even while you’re in the midst of creating it. So occasionally, you’ll be left with lots of duplicate information. Rather than deleting it cell by cell, there’s a simple way to handle it all in one fell swoop. Just highlight the area you want to de-duplicate, go to “Data,” and select “Remove Duplicates.”

5. Calculations

Rather than pulling out a separate calculator while you’re already working on a spreadsheet, let Excel do the math for you. To add, subtract, multiply, or divide numbers when entering them into a cell, simply use the operations’ respective signs (+, -, *, and /).

4. COUNTIF

It may sound like a character from Young Frankenstein, but the COUNTIF function is actually something that will make your life much easier. Instead of counting how often a certain word or value appears in a spreadsheet, or even a section of a spreadsheet, COUNTIF will do it for you. Just use “=COUNTIF” with the range and criteria in parenthesis next to it, and your counting problems will be solved.

3. Creating Charts

Data visualization is all the rage these days. If you want to stay on the cutting edge, Excel makes it incredibly easy to turn your spreadsheet into a nice-looking, easily customizable chart. Select Insert > Chart > Chart Type, and then use the Chart Tools menu to finalize your look.

2. Combine Cells With “&”

The formula =A2&” “&B2 looks complicated, but it does a very simple thing. It can take information from two separate cells (say, first name and last name) and combine them in a new column. Use it to add full names, addresses, or anything else you can combine.

1. Flash Fill

Of all the Excel tricks out there, the greatest of all, Flash Fill, was created in 2013. Once you turn this function on, Excel uses the equivalent of predicative text to suggest values for subsequent boxes. As simple as it sounds, this one feature will save you a ton of work and likely turn you into an office hero.