How can we create a headline title that is in the Center Across Selection mode?
Merge Cells vs Center across selection
In Excel, we have two solutions. We can select sales and use the Megre&Center command from the Home tab (Fig. 1)
Fig. 1 Megre&Center command
Excel will merge cells into one cell and align it in the center (Fig. 2).
Fig. 2 One, centered cell
This solution, however, has got a small drawback as it created one cell instead of many cells. When we try to select only one column and go a bit too high, we end up in selecting every column (Fig. 3)
Fig. 3 All columns are selected
But, when we go down again, we will select only one column again, so it’s not a big problem. Sometimes, we want to move our data a bit to the right or left. When we try to copy our range, we can’t select only cell B1, which is a bit problematic. After selecting the whole range, we want to move it to the right. However, there’s still a problem. If we want to move the range only a bit to the left or right, we cannot do it (Fig. 4).
Fig. 4 Merged cells cannot be moved only a bit
We have to go far enough to the right to make Excel copy the merged cell (Fig. 5).
Fig. 5 Merged cells moved
I want to show you one more solution. Let’s deselect the Megre&Center command. Now that we have our range selected, we can press the Ctrl + 1 shortcut to go to the Format Cells window. In the window, we have to go to the Alignment tab, then select theCenter Across Selection command in the Horizontal area, and press OK (Fig. 6).
Fig. 6 Format Cells window
We can see that our selected cells have merged. But this time, when we try to select one whole column, we can do it (Fig. 7).
Fig. 7 Whole column selected
You have to remember that now the value is located in cell A1. There’s nothing in B1, C1 and D1 cells. The value was just centered across selection. Let’s now change the coloring of the headline title and try to move it a bit to the right (Fig. 8).
Fig. 8 Moving the range
As we can see, it’s possible (Fig. 9).
Fig. 9 The whole range moved a bit
When we have the cell in the Center Across Selection mode, it’s also important that when we want to add some text to one of the cells, the Center Across Selection option will work only on the cells that are not filled (Fig. 10)
Fig. 10 Center Across Selection working only in cells A1, B1 and C1
How to calculate a sum or an average of top 3 maximal values?
Sum of top 3 values
In Legacy Excel, we can use the LARGE function, in which we need to select the range for our calculation. In the k argument let’s write 1 (Fig. 1).
=LARGE($C$2:$C$33,1)
Fig. 1 LARGE function
After entering the function, we have the largest value (Fig. 2).
Fig. 2 The largest value
Now, we can copy the function and add the second and the third largest value (Fig. 3).
This way we have the sum of 3 largest values (Fig. 4).
Fig. 4 The sum of 3 largest values
This solution, however, is the least dynamic of all solutions we have. We can make this formula shorter by hardcoding our values. We have to write 1, 2 and 3 as an array (Fig. 5).
=LARGE($C$2:$C$33,{1;2;3})
Fig. 5 The first, second and third value written as an array
This way Excel will return three largest values. I’m using Dynamic Excel, so the results are spilled (Fig. 6).
Fig. 6 Spilled results
Now, that our values have been hard-coded, we can sum them by using the SUM function. We can also use this function to sum up averages (Fig. 7).
=SUM(LARGE($C$2:$C$33,{1;2;3}))
Fig. 7 Summing up the largest values
Our results (Fig. 8).
Fig. 8 Results
This solution, however, is hard to modify, so we can make it a bit more dynamic. We can use the ROW function and select the first three rows from the sheet. This way our solution is more dynamic (Fig. 9).
=SUM(LARGE,($C$2:$C$33,ROW(A1:A3)))
Fig. 9 A more dynamic solution
Results (Fig. 10).
Fig. 10 Results
But, in Legacy Excel, we should use the SUMPRODUCT function instead of the SUM function or use the Ctrl + Shift + Enter shortcut to put the formula into the cell (Fig. 11).
=SUMPRODUCT(LARGE,($C$2:$C$33,ROW(A1:A3)))
Fig. 11 SUMPRODUCT function
And we have the result. In the second column, in Dynamic Array Excel, we still need the SUM and LARGE functions, then the array which is the range we will look at. Then, we can use the SEQUENCE function to create a sequence of proper numbers, let’s take 3 (Fig. 12).
=SUM(LARGE($C$2:$C$33,SEQUENCE(3)))
Fig. 12 Adding the SEQUENCE function
We have proper results (Fig. 13).
Fig. 13 Results
This solution is much more dynamic. Let’s change the number into 5 (Fig. 14).
=SUM(LARGE($C$2:$C$33,SEQUENCE(5)))
Fig. 14 3 changed into 5
Here is the result (Fig. 15).
Fig. 15 Results
In Legacy Excel, we can also change one number. Let’s change the number of rows in the range from 3 to 5 (Fig. 16).
=SUMPRODUCT(LARGE($C$2:$C$33,ROW(A1:A5)))
Fig. 16 A3 into A5
And we have the same result (Fig. 17).
Fig. 17 Result
We have to remember which version of Excel we have and which solution we can use.
Sometimes, you need to create a running total in pivot tables. How to do it properly?
Running Total in Pivot Table
Let’s start with creating a pivot table. Select one cell from our data and go to the Insert tab and select the Pivot Table command. A Pivot table from table or range window will appear, where we have our range. We need to select the Existing worksheet radio button and write the location, which, in our case, will be cell F1. Let’s click the OK button (Fig. 1).
Fig. 1 Creating a pivot table
And there we have it. In our pivot table we need to drag the Date to the Rows labels.
Fig. 2 Dragging the date
Now, depending on the Excel version, we have an option of Group by dates. I’m going to group our date by months and years (Fig. 3).
Fig. 3 Grouping
We have our table. Now, I want to add Income so let’s press the Income checkbox (Fig. 4).
Fig. 4 Income checkbox
Now, I want to have the income and the running total for the income, so let’s drag the Income two times to the Values area. We can see that we have Sum of Income and Sum of Income 2, where we actually want to have our running total (Fig. 5).
Fig. 5 Income and Income 2
Let’s right click any value from Income 2, then select the Show Values as and then the Running Total In option (Fig. 6).
Fig. 6 Selecting the Running Total option
Now, we have to decide whether we want to base our running total on Date or Years Field. Let’s take Date (Fig. 7).
Fig. 7 Date Field
Now, we can see that in the Income 2 column we have bigger numbers. Let’s format them by pressing any cell in the column and choosing the Number Format option (Fig. 8).
Fig. 8 Number Format option
In the Format Cell window, let’s choose the Currency category, in Decimal places let’s write 0 and press the OK button. Let’s do the same in the Sum of Income column (Fig. 9).
Fig. 9 Format cell window
Now, we can see that we are working with money. In each month we have bigger and bigger numbers which means that we have the running total in this column. We can change the name of Sum of Income 2 into Running Total. Since we are using the Date Field as our reference point, we have the running total in 2020. In 2021 Excel counts from the start, which means that we have the running total only for 2021. The same is with 2022. If we change the Date field into the Years field in the Show Values as (Running Total) window, we will have the same value in the first year and in the Sum of Income column (Fig. 10).
Fig. 10 Years
However, in the next year, we have the the sum from January 2021 and January 2020. In February, we have the sum from February 2021 and 2020. In 2022, we have sums from three Februaries (Fig. 11).
Fig. 11 Sums from three Ferbuaries
Let’s drag Manager into the columns header (Fig. 12).
Fig. 12 Dragging Manager into headers
By doing so, we can show value from the Manager’s perspective, which means a horizontal view.
Fig. 13 Horizontal view
However, this type of data isn’t a proper one to show it this way, so let’s get back by pressing Ctrl + Z two times and see that we can also create a percent running total by going to the % Running Total In option and choose the Date Field (Fig. 14).
Fig. 14 Percentage Running Total
Now, we can see the values as percentage (Fig. 15).
The most versatile solution I know is using the SUM function and a dynamic range which in our case is $B$2:B2. However, the most important fact is that we have to lock (F4 key) the first part of this range (Fig. 1).
=SUM($B$2:B2)
Fig. 1 SUM function
When we copy our formula down, we can see that the first part of the range will always refer to cell B2, but the second one will go down as we drag the formula. This way, we have a range that expands as we go down. It’s the most versatile solution (Fig. 2).
=SUM($B$2:B4)
Fig. 2 An expanding range
This solution has problems with Excel tables. When we sum from F2 to F2 cells and press F4 key to lock it, everything looks fine. However, when we add new data to our table, the last cell will expand because it refers to the whole column July -> August (Fig. 3).
Fig. 3 Adding a new value
In this case, we have to modify our range. Instead of cell F2 we can use the table nomenclature. When we click cell F2, Excel will refer to this table row, where @ means this table row, and Income means the column we are referring to (Fig. 4).
=SUM($F$2:[@Income])
Fig. 4 Range modification
We have to overwrite all cells (Fig. 5).
Fig. 5 Overwriting cells
This way we got proper results. We can check if it works properly by adding some new rows. After we added two rows, the results are still correct (Fig. 6).
Fig. 6 Correct formula operation
The only drawback of this formula is that we won’t see the whole range because Excel won’t select the whole range. Theoretically, the last cell should include the whole column but Excel selects only the first and the last row (Fig. 7).
=SUM($F$2:[@Income])
Fig. 7 The formula in the last cell
We won’t see it, but Excel will. We have to remember that Excel will create a proper reference and we can create a proper running total, even in Excel tables.
If you want to calculate a median value with a condition, you cannot use a simple MEDIAN function because it needs only numbers. We have to use another function that will reduce the number of wages we look at.
Median with condition
Typically, when we want to add a condition to another function we can use the IF function and make a logical test. In our case, the logical test will be simple. We just have to check each cell in the Department column whether it’s equal to the name of the Department we look at now. Since we are comparing a single cell to a whole range, Excel will check every single cell in a given range (Fig. 1).
Fig. 1 Comparing a single cell to a range of cells
If a cell in the range is equal to the target cell, we want the function to return proper wages, i.e. the wages from the same row. If the logical test result is FALSE, we want to have something that the MEDIAN function will ignore which, in most cases, is an empty text string. However, I want to show you that the IF function really returns something so let’s write “no” (Fig. 2).
=IF($B$2:$B$13=E2,$C$2:$C$13,“no”)
Fig. 2 IF function
We can see that Dynamic Array Excel spilled the results and we have wages only for the departments we chose. For other departments we have ‘no’. Now, we can put this function into the MEDIAN function (Fig. 3).
=MEDIAN(IF($B$2:$B$13=E2,$C$2:$C$13,“no”))
Fig. 3 MEDIAN function with IF function
Now, we’ve calculated the median value with a condition for each department. We can see that for HR it’s $4,000, for IT it’s $8,100 and for Marketing it’s $5,900. Those are the middle values for each department (Fig. 4).
