Opening the vault on Excel, part 2

Excel expert Neale Blackwood began writing his monthly column in INTHEBLACK magazine in 2002, and recently “opened the vault” on those early articles to review the information and add newer and easier solutions or built-in functions.

 

I answered five questions between February and March 2003. Here they are.

1. I have a subtotal list. I want to copy just the subtotals. However, when I paste I get all of the detail as well as the subtotals. Is there a way to just paste the subtotals?

Excel has a subtotal feature that can insert subtotals in a sorted list. This feature is on the right-hand side of the Data ribbon. An example is shown on the right of Figure 1.

Figure 1.

You can hide the detail rows by using the grouping buttons on the left-hand side. Figure 2 shows the detail rows hidden.

Figure 2.

When you copy this range normally and paste it you will paste everything, even the hidden rows. If you want to paste just the subtotals then you need to select something called “Visible cells only” before you do the copy.

The option “Visible cells only” is in the Go To Special dialog. Press F5, then click the Special button. See Figure 3.

Figure 3.

You can also use a shortcut key to access this option. Select the range and hold the Alt key down then press the semi-colon key (Figure 4). You can now copy and paste and only the subtotals will be pasted.

Figure 4.

The problem with this solution is that Excel only pastes the values not the formulas. So, if the original data changes the pasted range will not update.

You can use a different type of paste. If you use Paste Link then it will link to the original cells. The only problem with that solution is if the structure changes the links may not be valid.

To Paste Link you can right click where you want to paste and select the last icon in the Paste Options section (it is the shape of a link). See Figure 5.

Figure 5.

Making the totals bold

You may have noticed in Figures 1 and 2 that the headings were bold for the inserted subtotals, but the values weren’t bold. I have been asked a few times over the years how to make the values bold as well.

The solution also uses the “Visible cell only” option. Use the grouping icons to only display the subtotals. Select the range the press Alt + ;  then press Ctrl + B or click the Bold icon.

2. I want to use the fill handle to drag only weekday names, but I get Saturday and Sunday as well. Is there a way to fill only weekdays?

There are a couple of ways to achieve this. The first is to drag the cell’s fill handle (small cross at the bottom right-hand corner of a cell) with the right mouse button. This is something that you may never have tried before. When you release the mouse button a menu pops up and you can choose Fill Weekdays. See Figure 6.

Figure 6.

The other way to do it is to drag as per normal with the left mouse button and click the little icon that pops up at the bottom right-hand corner of the range after you have finished dragging. 

This has other options and Fill Weekdays is one of those options. See Figure 7.

Figure 7.

3. Is there a formula to round to the nearest five cents?

The answer to this question hasn’t really changed over the years. If cell A1 contains the number to be rounded, then the following formula will round it to the nearest five cents. See Figure 8.

Figure 8.

=ROUND(A1/0.05,0)*0.05

The principle of the formula is for the value to be rounded to be divided by the amount to round to and then rounded to the nearest whole amount (the 0 in the formula forces this result). You then multiply the result by the amount to be rounded to.

Excel now has an MROUND function that is supposed to perform the same type of calculation. I have found that it returns inconsistent results depending on the values being rounded. I find the above formula is more reliable.

4. I use SUMIF to sum a range based on one condition. Is there a way to SUM a range based on two or more conditions?

The answer to this question has changed over the years because of a new function that was added in Excel 2007. The SUMIFS function allows you to use multiple conditions to perform a SUM. An example is shown below in Figure 9.

Figure 9.

My solution in 2003 was the SUMPRODUCT function which happens to be my favourite function. An example is shown in Figure 10.

Figure 10.

In my original answer I also suggested using a PivotTable to create the report rather than using formulas. That suggestion still applies.

The latest versions of Excel have COUNTIFS, AVERAGEIFS, MAXIFS and MINIFS functions.

Note these functions and SUMIFS don’t work if the ranges are in a closed workbook. SUMPRODUCT will work with ranges in closed workbooks.

5. Is there a way to change headings based on a cell that has the current month?

When you’re trying to create headings which are text it can be problematic to include dates with that text. Excel stores dates as numbers. The first date that Excel recognises is 1/1/1900, that is day one. Every day since then has been given a sequential number.

When you try and use a date with a text string you will see the underlying number. To use a date with text you need to use the TEXT function. This converts the date number into a date format. An example is shown below in Figure 11.

Figure 11.

The & symbol combines text. Some people use the CONCATENATE function to do it, I use &.

Within the TEXT function you must enclose the format within quotation marks. The types of formats that you can use are shown in the Custom number format section in the Format Cells dialog. See Figure 12.

Figure 12.

Read next: Opening the Excel Yourself vault


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

Read the September 2019 issue of INTHEBLACK magazine.

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

CONTENTS