3 fun Microsoft Excel tips for summer vacation

TORONTO – This summer we’ve published a number of fun Microsoft Excel tips and tricks from Alan Salmon of K2E Canada. This is the last in the series, three fun and unrelated Excel tips.

1.  Changing the colour of the gridlines

Gridlines help you track information and quickly locate cells. The default colour for these lines is black but it is easy to change them to some other colour. Display the Excel Options dialog box. (In Excel 2007, click the Office button and then click Excel Options. In Excel 2010 and Excel 2013, display the File tab of the ribbon and then click Options.)

1. At the left of the dialog box click Advanced.
2. Scroll down the options until you see the Show Gridlines check box; select it. (It is in the Display Options for this Worksheet section.)
3. Click the Gridline Color control and choose a colour you want to use for the gridlines. Be sure the Show Gridlines check box is selected.
4. Click on OK.

You can specify different gridline colors for each worksheet in a workbook.

2.  Making sure a column or row is really empty

It is important to check if a row or column has data before you delete it. Here is a quick way to check for a completely blank row or column:

1. Click on the first cell of the column (A1, H1, etc.).
2. Hold down the Ctrl key as you press the down arrow or the right arrow.

Performing these simple steps causes Excel to move to the next cell containing data. If there is no data, Excel selects the last cell in the column (at row 1,048,576) or the last cell in the row (at column XFD). You then know that the row or column is empty and you can safely delete it.

3.  Calculating the day of the year

Here is how to calculate the day of the year. Let's assume you have a date in cell A2. In C2 you want to calculate what the day of the year is. Use the following formula to do the calculation:

=A2-DATE(YEAR(A2),1,1)+1

Dates and times are stored as numbers in Excel and count the number of days since January 1, 1900. December 3, 2017 is the same as 43072. The DATE function accepts three arguments: year, month and day.

DATE(YEAR(2017),12,31) or 31-Dec-2017 is the same as 43100, subtracting these numbers (43100 - 43072 = 28). Of course, once you enter the formulas, you need to format the cells as regular numbers. (Excel will, by default, try to format the cells as dates.)

With the two cells selected, follow these steps:

1. Select the Cells option from the Format menu. Excel displays the Format Cells dialog box.
2. Make sure the Number tab is selected.
3. In the Category list, choose Number.
4. Make sure the Decimal Places option is set to 0.
5. Click on OK. 

Alan Salmon is recognized as Canada’s leading analyst in accounting technology. As the founder of K2E Canada Inc., Alan has over 34 years of business, management systems, education and journalism experience.