More Excel Time-Saving Tips

Excel Time-Saving Tip #1

When auditing or checking a workbook, I have been grateful to be able to select all the formulas in the worksheet at once. This Excel time-saving tip will even select the hidden formulas. You can use the Go To command to get started. Use Ctrl + G as the keyboard shortcut to open the Go To dialog box. See Figure 1.

Excel time-saving tips - Go To dialog box

Figure 1

Next, click the Special button in the bottom left corner of the Go To dialog box. To select all of the formulas, choose formulas and all of the items that you want underneath it and click OK. See Figure 2.

Go to Special dialog box with Formulas selected

Figure 2

Excel Time-Saving Tip #2

Have you ever needed to update a number by 20% or add 7 days to a date? If so, you have probably used a helper column to be able to use a formula to increase the values. My Excel time-saving tip #2 in Excel will show you how to do it without needing an extra column. You can do the operation in the current column.

Start by entering the numbers or dates you want to change. For our example, we will use a set of dates to increase by 10 days. I have needed this exact example when a project I was working on was delayed by 10 days. See Figure 3. (Note: If you want to increase a number by 20%, you would put 1.20 in a separate cell to copy to use for the operation. This process only works on values and not on formulas.)

5 dates in a list in Excel - a screenshot

Figure 3

In any cell, enter the number of days that you want to add to the dates. You can delete this when you are done. I entered a 10 in cell C2. Then, copy the cell with the 10 in it. See Figure 4.

List of dates in Excel with the cell copied

Figure 4

Select the set of dates that you want to change. For our example, we would select A1:A5. Click the Paste Special button (or right-click your selection A1:A5) and choose Add from the Operation section. See Figure 5.

Paste Special dialog box with Add selected

Figure 5

You will get the dates with 10 days added to them. (Note: If your formatting switches back to General, you may need to click on the Number formatting drop-down list and choose the date format you want.) See Figure 6.

list of dates in Excel with 10 days added to them

Figure 6

Excel Time-Saving Tip #3

Whenever I look at a formula, especially one where there are nested formulas inside, my eyes cross. I see so many different cell references and have to find each one with auditing tools or by manually searching to make sense of the formula. My Excel time-saving tip #3 makes it much easier by using named ranges when creating formulas to give more description to the cell reference.

To create a named range, you just select the cell or group of cells that you want to name. Click in the name box and type the name. Then, hit the enter key. The name box is just above the column heading A. See Figure 7. The name cannot have any spaces in it. You can use the underscore character instead of a space. That’s it!

screenshot of the name box above column A in Excel

Figure 7

When you want to use the cell or cells that you named in a formula, type in the name in the formula instead of the cell reference. For example, if you named cell A1 as discount_rate, you could use that in the formula and not $A$1. You would not enter =$A$1*C6, you would type in =discount_rate*C6.

Enjoy trying out the pro time saving tips in Excel!

Angela Nino - Versitas Editor

Leave a Reply

Your email address will not be published. Required fields are marked *