People don’t often talk about Excel as a productivity tool. It helps you organize data and run reports, but helping you get more done isn’t usually listed among its strengths.
That being said, if you know how to use it, Excel can actually be a great way to improve your productivity. We’ll take a look at conditional formatting, the IF function, named ranges, and keyboard shortcuts – all of which will help you get more done in Excel.
When you have a spreadsheet full of data, color-coding it can be helpful. But manually color-coding hundreds of rows is extremely time-consuming. Conditional formatting solves that problem.
You could, for example, tell Excel to highlight every cell with a value over 100 in blue. Or to display negative values in red text. You can apply any format to any group of cells that you can specify with a formula.
To get started, go to Home > Styles > Conditional Formatting and select Conditional Formatting.
From here, there are all sorts of pre-defined rules you can use. To create your own custom rule, click New Rule.
You can choose from preset types of rules, including two-color scale highlighting based on value, or use the final option, use a formula to determine which cells to format.
You’ll also need to click Format… to choose the format of the cells that meet the conditions.
Using these options, you can tell Excel to format any number of cells that meet a specific criterion, saving you the time of color-coding manually.
The IF Function
Excel’s logical functions add a lot of power to your spreadsheet. The IF function lets you specify a condition and the output if that condition is met. You can also specify the output if the condition isn’t met.
Let’s say that you use Excel for time tracking, and that you have a cell that contains your total billable time for the week. You could use this formula to create a message that either encourages or congratulates you based on the hours you’ve logged:
=IF(D4>35, “Keep going!”, “Good job!”)
With this formula, if the number in cell D4 is above 35, the cell containing this formula will read “Keep going!” Once you get above 35, it will show “Good job!”
Instead of simply displaying text, you can also tell Excel to run other calculations or display specific statistics. You can even nest IF functions, like this:
=IF(D4<10, “Great start!”, IF(D4<20, “Solid progress”, IF(D4<30, “Getting close”, IF(D4<40, “Almost there!”, “Good job!”))))
Of course, you can use the IF function for a lot more than displaying clever messages. It’s one of Excel’s most useful functions, and once you learn to use it effectively, it will be a big boost to your productivity.
This is one of Excel’s easiest time-saving features, but it’s severely underutilized. In most functions, you need to specify a cell or a range of cells.
Most of the time, it’s a simple matter of typing in the name of a cell or clicking and dragging to select a range. But if you have a complicated selection or you regularly select the same group of cells, you can save time by using named ranges.
Named ranges let you type in a short line of text to refer to a group cells. For example, instead of typing “A2:B10,” you could type “sales.”
That might not seem like much of a time-saver, but when you have a more complicated selection or refer to a specific set of cells on a regular basis, it can be very convenient.
First, you’ll need to find the Name Box, which is located at the top-left corner of your spreadsheet. It usually contains the name of the cell or cells that you’ve selected.
To name a range, select the cells you want, then click into the Name Box and type the name of the range.
Now you can refer to that group of cells by typing the range name in a formula.
You can also use the Name Manager, accessed in the Formulas tab or with Ctrl+F3, to create, edit, and manage named ranges. It’s also possible to use the Name Manager to name specific formulas, which will save you even more time.
Just click New, and in the dialog box, enter the formula you’d like to refer to in the Refers to box. Then enter a name and save the name. Now you can refer to a complicated set of formulas with a few letters!
You probably already know a few of Excel’s shortcuts; basic ones like Ctrl+C and Ctrl+V are pretty common. But did you know that you can use Excel almost exclusively with your keyboard?
You might know that Ctrl+A selects your entire spreadsheet, but you may not have known that Shift+Space highlights the entire row that your cursor is in, or that Ctrl+Space highlights the entire column.
Ctrl+D fills your cell with data from the cell above, and Ctrl+R fills it from the cell to the left. Activate the formatting dialog with Ctrl+1. Use Ctrl+F3 to open the name manager to edit your named ranges. Move between sheets with Ctrl, PgUp, and PgDn.
There’s a vast array of keyboard shortcuts that will speed up your work in Excel. Hundreds of them, in fact. It can take a while to get to grips with a lot of them, but if you practice and make a habit of looking up new ones on a regular basis, you’ll become a keyboard shortcut master in no time.