Opening the vault on Excel, Part 6

Excel expert Neale Blackwood CPA began writing his monthly Excel Yourself column in INTHEBLACK magazine in 2002. He recently “opened the vault” on those early articles to review the information and add newer and easier solutions or built-in functions. This month: Page Layout, SUM hours and more.

I answered three questions in November and December 2003.

1. Is there a quick way to change the page orientation from portrait to landscape on all the sheets in a workbook?

Yes, but the technique comes with a warning. It is easier these days to change page orientation as the option is on the ribbon.

Right-click one of the sheet tabs and choose Select All Sheets as in Figure 1

Figure 1.

Click the Page Layout ribbon tab and click the Orientation drop-down and choose Landscape – see Figure 2.

Figure 2.

That change was made to all the selected sheets. You now need to ensure you de-select all the sheets. Either click on one of the sheet tabs (not the active sheet) or right-click any sheet tab and choose Ungroup Sheets.

If you want to change the page orientation on only a few sheets, then hold the Ctrl key down while clicking the sheet tab names. Then change the setting and then ungroup the sheets when finished. If you inadvertently click a sheet, you can click it again to unselect it while still holding the Ctrl key down.  

If you want to change numerous adjacent sheets, you can click the first sheet tab and then hold the Shift key down and click the last sheet tab. Then change the setting and then ungroup the sheets when finished. 

Clicking any other sheet apart for the active sheet will ungroup the sheets.

If you want to change more print settings across multiple sheets, you can select (group) all the sheets and then click the small arrow icon on the bottom right corner of the Page Setup section of the ribbon tab – see Figure 3. This will open the Page Setup dialog, which has all the print settings.

Figure 3.

WARNING:

Always ungroup the sheets after you finish what you are doing. When the sheets are grouped whatever you do, for example, changing a cell value, will be done in all the sheets.

This can be a great time saver when all the sheets are identically laid out, but it is can also lead to a corrupted file if the sheets are laid out differently. Unfortunately, Excel doesn’t provide much in the way of a visual warning that you have multiple sheets selected. The two obscure screen changes are

  • the selected sheet tabs will all be highlighted at the bottom of the screen.
  • at the top of the screen, the word Group appears in the file name – see Figure 4.
  • Figure 4.

Grouping

Grouping is probably Excel’s most over-used term. Excel has at least four distinct types of grouping:

  • Sheet tabs – multi-select sheets
  • Rows and columns – used to easily hide and unhide rows and columns 
  • Graphics and images – used to move, format and resize multiple images or charts
  • PivotTables – used to summarise and add subtotals to reports

2. I’ve entered hours in a column. Six hours is entered as 6:00. When I SUM the column the result is incorrect. How do I SUM hours?

The solution to this issue is not intuitive. You need to change a format.

When you SUM hours, Excel assumes that you want the result displayed in hours. In the standard format for hours the maximum number of hours is 24. Therefore, if the hours summed exceeds 24, Excel will only display the remainder hours after each 24-hour period. For example, if the number of hours totalled is 33, only nine hours would be displayed (33-24).

You need to amend the format of the cell that contains the SUM calculation. A custom number format is required. Press Ctrl + 1 to open the Format Cells dialog. In the Number tab click the last option on the left, Custom. As per Figure 5, in the Type box enter the following code

Figure 5.

[h]:mm

Figure 6 has the before and after screen shots of applying the format on cell B9.

Figure 6.

Calculations with hours

The following examples are useful if you need to do numeric calculations with hours. In Excel, hours are represented as a fraction of a day. Noon is 0.5 and 6pm is 0.75. If you try multiplying values by hours, you will find that the result may not be what you expect. For example, if the cell is displaying 48 hours (row 5 in Figure 7) and you multiply it by 10, the result will be 20. That’s because 48 hours represents two days. If you want to multiply hours by a rate, then you also need to also multiply it by 24 to convert the hours correctly. Figure 7 displays examples of performing calculations with hours.

Figure 7.

3. How do I display the last date of the month in a cell?

There are a couple of ways to achieve this. It depends if you are automating a process or if you are just creating dates as a data entry.

To create data-entered month-end dates you can use Excel’s Fill feature. If you are creating the dates horizontally, then enter a month-end date in one cell and then the next month-end date in the next cell to the right. Select both cells and then use the mouse with the Fill Handle (small cross on the bottom right-hand corner of the cell/range) to drag the dates across. Excel will populate the other cells with month-end dates.

If you need to automate the process of entering month-end dates, then you can use a formula that displays the month-end date for any date. If cell A2 contains a date, then the following formula will return the end of month date for the date in cell A2.

=EOMONTH(A2,0)

The function stands for end of month. The ,0 on the end instructs Excel to not increment the month. If you had ,1 on the end it would display the end of the next month.

Note: you may need to change the format of the cell to display the date correctly.

Figure 8 has a number of other useful date calculations and their formulas.

Figure 8.

The companion Excel files (blank and complete) will go into more detail to demonstrate these techniques.

Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services to organisations around Australia. Questions can be sent to a4@iinet.net.au


Like what you're reading? Enter your email to receive the fortnightly INTHEBLACK e-newsletter.

Recommended for You

READ MORE

Opening the vault on Excel, part 3


READ MORE

Opening the vault on Excel, part 4


READ MORE

Opening the Vault on Excel, part 5


December 2019
December 2019

Read the December 2019 issue of INTHEBLACK magazine.

Each month we select the must-reads from the current issue of INTHEBLACK. Read more now.

CONTENTS