Today, we want to learn how to transpose our data, i.e. how to switch columns with rows.
Switch Rows with Columns — data transposing
We have two solutions to choose, a static one and a dynamic one.
Let’s use the first one. We need to copy our data (1) and then right click on our target cell (2). From the pop-up menu, we select the Transpose option (3) (Fig. 1)
Fig. 1 Transpose option
We can see that our data has been transposed (Fig. 2)
Fig. 2 Transposed data
Now, let’s use the dynamic solution. Here, we just use the TRANSPOSE function and select the array we want to transpose (Fig. 3)
=TRANSPOSE(A1:B6)
Fig. 3 TRANSPOSE function
And we have our data transposed. However, we can see here that Excel didn’t copy the cell formatting. If we want the same formatting, we should copy it from somewhere else (1) using the Format Painter (2) option, and use it in the target place (3) (Fig. 4)
Fig. 4 Copying the cell formatting
When we change something in our original data, the data in the static solution won’t change, but the function will.
In versions older that the Dynamic Array Excel, we should select a proper range before putting the formula in a cell, then press the Ctrl + Shift + Enter combination to enter it as an array formula.
Do you want to round the working time to the nearest 15 minutes? I will show you how to do it.
Round time to nearest 15 minutes MROUND, FLOOR or CEILING functions
We can round our working time to the nearest multiple of 15 minutes. In most cases, when we round in Excel and reach the middle point, we start to round up. In the case of 15 minutes, the middle point is 7.5 minute. Now, let’s start the rounding. It’s a simple task. We can just use the MROUND function, write the number, which in our case will be the time, and then write the multiple as time, i.e. in double quotes. In this case, the first two digits correspond to hours, and the last two ones correspond to minutes. If we need seconds, we just write them further (Fig. 1)
=MROUND(A2,“00:15:00”)
Fig. 1 MROUND function
Now, let’s check our time. In the first three examples, we didn’t reach the middle point, so we go down. However, after we pass the middle point, which is shown as a threshold, we start rounding up. In the last but one example, we have exactly the same value, so we leave it just as it is. However, in the very last example, we didn’t reach the next middle point, so we still round down (Fig. 2)
Fig. 2 Rounding with the MROUND function
Sometimes, we always want to round down. In this case, we can use the FLOOR function. In the first argument, we have to write the time. In the second one, which is called significance, we write the same multiple as in the MROUND function, which is time in double quotes (Fig. 3)
=FLOOR(D2,“00:15”)
Fig. 3 FLOOR function
In this case, we always want to round down. It means that even when we go to halfway, we still go down. What’s more, even if we are very close to the next multiple, like in the last but one example, we still round down. Even, when we pass the multiple, we still round down, but this time to the next multiple, as in the last example (Fig. 4)
Fig. 4 Rounding with the FLOOR function
When we want to round up, we can use the CEILING function. We write the same arguments as in the previous functions. In this case, it’s enough if we pass the previous multiple by only one second, and the rounding will go up. Moreover, when we pass the next multiple by only a tiny bit, let’s say one hundredth of a second, we will go up to the next multiple (Fig. 5)
Fig. 5 Rounding with the CEILING function
Summing up, when we use the CEILING function, we always go up. When we use the FLOOR function, we always go down. And when we use the MROUND function, we go to the nearest multiple. The most important is the middle point. We can, of course, write any time we want in the second argument of those functions.
Do you want to put a Pareto chart in Excel? I will show you how.
How to Create A Pareto Chart
If you want to put a chart on which you have columns that represent income and a line that represents the cumulative percent income, follow me.
From Excel 2016, you can simply go to the Insert tab and choose the Pareto chart from the Histogram command (Fig. 1)
Fig. 1 Inserting a Pareto chart
And you have the Pareto chart ready. However, the charts from Excel 2016 have some drawbacks. The line isn’t an actual series of the chart. It means that we cannot add data labels there. That’s why I don’t prefer using this type of chart. What I prefer is the earlier version that gives me more freedom concerning value changing, although it requires more calculations. We have to calculate the cumulative percent income on our own (Fig. 2)
=SUM(B$2:$B2)/SUM($B$2:$B$9)
Fig. 2 Cumulative percent calculations
Now, we can insert a simple column chart. Since the income is enormously bigger that the percentage, we aren’t able to see the income columns at all, but we want to select them (Fig. 3). How can we do it?
Fig. 3 No % Income columns
We can select our chart and go to the Format tab. On the left we can see all elements of the chart. We are interested in the Series “% Income” option (Fig. 4)
Fig. 4 Series “% Income”
We can see now that the series is selected on the chart. Let’s press Ctrl + 1 and go to Format Data Series, Series Options, Plot Series On, and select the Secondary Axis option. Now we have % income on a different axis (Fig. 5)
Fig. 5 Secondary Axis options
Now, we can change the chart type into a chart that will represent income better. We have to click on the chart element once, then go to the Insert tab and the Line chart with Markers option (Fig. 6)
Fig. 6 Line Chart with Markers
Since we are creating a Pareto chart ourselves, the values aren’t sorted and we have to do it manually. We just select one cell in our data, then go to the Data tab and choose the from Z to A option (Fig. 7)
Fig. 7 Data sorting
Now the data on the chart is nicely sorted and it looks more like a Pareto chart. We still have to add modifications to make it a real Pareto chart. First of all, let’s select a column and press Ctrl + 1. On the right, we have the Gap Width option. Let’s slide it to 0%. Now, let’s go to the secondary axis. It goes up to 120%, however our maximum is 100% (Fig. 8)
Fig. 8 Gap Width modification
We have to select the axis, press Ctrl + 1, go to Axis Options and write 1 in the Maximum bar, which equals 100%. We also change our Major Units to 0.2 which means that there will be less percent numbers showed on the axis (Fig. 9)
Fig. 9 Less percent numbers
We still don’t need the 0 after dot in our percentages, so let’s go to Numbers and change the Format Code from 0.0% to 0% and click Add (Fig. 10)
Fig. 10 Format Code change
Now, let’s go to the Income Format Axis. Press the axis and Ctrl + 1. Let’s change the Major from 100000 to 200000. This way the chart will show less numbers (Fig. 11)
Fig. 11 Less numbers
What I care about the most right now are data labels for the line. Let’s click once on the line, click on the plus sign and we have Data Labels option. Let’s place them above our line (Fig. 12)
Fig. 12 Data Labels
We still need to set a proper title. Let’s just write Pareto. After implementing the most important changes, the Pareto chart looks like that (Fig. 13)
Do you want to know how to round numbers? Follow me.
Round to nearest multiple MROUND, FLOOR or CEILING functions
Let’s assume I get paid for correcting text and I don’t want to work with tiny amounts, like individual cents. What may help me is rounding the number of characters checked by me in a given text. When I round it, my income will be rounded as well. Let’s assume that I want to round to the nearest multiple of 200. In standard Excel rounding, when we go to the middle of the number, which in our case is 100, the rounding starts to go up. It means that when we use, e.g. the MROUND function to round the nearest multiple of 200, in the case of 2349 we go up because we passed the threshold of 2300. However, in the case of 2499, we go down. Why? Because we haven’t reached the middle point. But when we reach the middle point, which in this example is exactly 2500, we go up. In order to go up, we only have to reach the middle point, and when we don’t reach this point we go down. It means that it’s even for me and for my customer (Fig. 1)
Fig. 1 Threshold and rounding
Sometimes, there are situations that I always want to round down or always round up. Let’s assume that rounding down will be a little bit nicer to my customer, and rounding up will be nicer for me. How can we do it? In Excel, when we always want to round down, we can use the FLOOR function (Fig. 2)
=FLOOR(D2,200)
Fig. 2 FLOOR function
We have to remember that we are still using the rounding to the nearest multiple of 200. Our rounding goes down even if we passed the halfway. Even, when we are very near and almost reached the point of the multiple, and even if the difference is a very small fraction of an integer, like in 2599.999, we still go down. And when we reach the nearest multiple, which is 3000 in our case, it becomes our base and we will still go to this base until we reach the next base, which is the next multiple of 200 (Fig. 3)
Fig. 3 Rounding down
When we want to always go up, we can use the CEILING function, where we also give it a number and significance, i.e. a multiple, as it was with the FLOOR function (Fig. 4)
=CEILING(G2,200)
Fig. 4 CEILING function
When we pass our next multiple, we always go up. Even if the number passed the nearest multiple by only a small fraction, like 30000.0001, we still go up (Fig. 5)
Fig. 5 Rounding up
Of course, 200 is only an example of a multiple for those functions. We can write there 500, or use even decimal numbers, like 0.5 or 1.5. However, the example above used simple, hole numbers.
Today, we are going to talk about putting two different series into one chart.
Secondary Axis on Excel Chart Temperature and rainfall
If you have two series that differ from each other, e.g. they have different units or one of them is much bigger than the other, then you should use the Secondary Axis on you chart. How can you do it? From Excel 2013 the task is simple as Microsoft inserted the Combo chart. Our example has simple data, so we just have to click on one cell, then go to the Insert tab, where we can find a Combo chart with the Secondary Axis (Fig. 1)
Fig. 1 Combo chart with secondary axis
I can see that not everything is as I wanted, that’s why I’m going to make some changes. I just select the chart and go to the Chart Design tab, where I can find the Change Chart Type option. In the window that appeared, we change the type of each series. The rainfall is on the secondary axis, which is good, however, I prefer the rainfall to be presented as a column chart, and I will put the temperature into a line chart with markers (Fig. 2)
Fig. 2 Column chart and line chart with markers
After pressing OK, we can see a finished chart with two values (Fig. 3)
Fig. 3 A finished chart with two values
But, how can we do it in Excel from before 2013? Let’s insert a simple column chart by going to the Insert tab, then choosing the proper column chart (Fig. 4)
Fig. 4 Column chart
As our values differ much in size, where the rainfall is significantly bigger than the temperature, I would like to have the rainfall series on a secondary axis. I have to select the hole series by clicking once on the series element, then press Ctrl + 1, find the series option, go to Plot Series On, and choose Secondary Axis (Fig. 5)
Fig. 5 Series options
And, just like that we have a different axis for rainfall, and a different axis for temperature. There is still one thing we need to change, which is the type of temperature series, because now one series is behind the other and we don’t know how high some columns are. Let’s click one time on the series and go to the Insert tab, where we can choose a new chart type. Let’s choose the Line with Markers option (Fig. 6)
Fig. 6 Line chart with markers
Just like that, I have a chart with a secondary axis and different chart types. Let’s change the name of the chart into Temperature and rain. The chart is finished (Fig. 7)
Today, we are going to talk about bank rounding, or half-way-even rounding.
Bankers Rounding (Half-Way-Even) to Dollars, Pennies and Hundreds
In a classic Excel rounding, if we have 5 as the most significant number for us, we always go up. It means that errors go up. To make the distribution of our roundings a bit more even, we sometimes need to go down. That’s why we have the bank rounding which goes around the nearest, even number. Let’s take the number of 7.50. Our whole number, which is 7, is an odd number. By rounding up, we go to the nearest, even number, which is 8. However, if our whole number is even, like in 8.50, we go down to 8. The distribution of the rounding values is more even, which means that an error is less significant. It’s important especially in banking, that’s why we call it bank rounding (Fig. 1)
Fig. 1 Rounding to the nearest, even number
How can we use it in Excel, when we know that the standard ROUND function will always go up if the most significant number is 5. The answer is that we have to create a different formula. The formula that works for me, where I haven’t seen any errors is a bit complicated, however, when we start working with this, it will be quite easy to understand. First, we have to check whether our numbers are half way or not. We can see that two numbers are half way. In the remaining ones, the situation is clear, i.e. we know that we want to go up or down (Fig. 2)
Fig. 2 Halfway numbers
If we want to check whether to go up or down, we can use the MOD function, where we should write 1 to check what the divider is, i.e. we want to check what is the rest after dividing it by 1 (Fig. 3)
=MOD(A2,1)
Fig. 3 MOD function
We can see the results (Fig. 4)
Fig. 4 MOD function results
If the number is halfway, we need to round it to the nearest whole number. It means that we have to check whether the result of the MOD function is equal to 0.5. If it is, we should use the MROUND function that returns the desired number rounded to the desired multiply of even numbers from our example. It means that we want to round 207.50 to the nearest multiply of even numbers, which is 2. In other cases, we just use the ROUND function to whole numbers. In our case, the number of digits is 0 (Fig. 5)
=IF(MOD(A2,1)=0.5,MROUND(A2,2),ROUND(A2,0))
Fig. 5 Proper formula
Looking at the results, we can see that when we have an odd, whole number and we are halfway, we go up to the nearest even number. If we have an even number and we are halfway, we go down to the nearest, even number. However, if we pass the halfway threshold, we start to go up (Fig. 6)
Fig. 6 Even and odd numbers in data
Now, let’s go to the rounding to 100. We copy our previous formula, which aimed at rounding to whole numbers. Now, we want to round to 100, which is 100 times more than in the previous example. It means that we have to multiply all numbers in our formula by 100 (Fig. 7)
=IF(MOD(E2,100)=50,MROUND(E2,200),ROUND(E2,-2))
Fig. 7 Multiplication by 100
And just like that we have proper results. We went halfway in two cases. Since we’re rounding to 100, we went up in the number 2350. In the second halfway number, which is 2450, we already had an even number, so we went down to the nearest even number (Fig. 8)
Fig. 8 Rounding to 100
If I change numbers in the last example and pass the halfway threshold, we can see that the rounding goes up (Fig. 9)
Fig. 9 Rounding goes up
If we want to use the bank rounding to decimal places, e.g. for pennies, it will be a bit more problematic. Here, we have to remember that we are checking the second digit to know whether it’s an even value. If the digit is odd, we round up, and if the number is even, we go down to the nearest even number. We have to remember that if we pass the halfway threshold, we start to go up (Fig. 10)
Fig. 10 Rounding with decimal places
If we want to work with this banker’s rounding rule with decimal places, we cannot use the MOD function because, when we work with e.g. pennies, the MOD function results will have some inaccuracies in digits that are far away, like on the fifteenth place after the dot, or so. In such a situation we won’t be able to check whether it is halfway, because the far places show that it’s not (Fig. 11)
Fig. 11 Inaccuracies in digits
When we work with whole numbers, we can use the MOD function because they are precise. A number in halfway will be returned by the MOD function as still a halfway number. In some other situations there can be some inaccuracies, but they don’t concern us, because they will work correctly. Here, we care only about halfway numbers, which stay halfway in the MOD function (Fig. 12)
Fig. 12 The MOD function with whole numbers
In the case of pennies, we have to use a different method. This method will show the number of digits we want to have. Let’s assume that we want to have five digits after dot. In such a case, we have to change our number to text by using the TEXT function, where we write the format number as 0.00000 (Fig. 13)
=TEXT(H2,“0.00000”)
Fig. 13 Changing numbers into text
Just like that we have numbers as text, but with five-digit precision. Now, let’s look for halfway numbers. We can see that halfway is 500 as the last three digits (Fig. 14)
Fig. 14 Numbers with five-digit precision
Now, we have to take out those three digits. Since they are located on the right side, we can use the RIGHT function, where we write 3 (Fig. 15)
=RIGHT(TEXT(H2,“0.00000”),3)
Fig. 15 The RIGHT function taking out the last five digits
And just like that we have only three, last digits. When we are in halfway, we will have exactly 500. Here, we also have to remember about different roundings. If the number before 500 is odd, we go up, but then it’s even, we go down (Fig. 16)
Fig. 16 Three, last digits
We have to check whether the number is equal to 500 using the IF function. We have remember that we are working with text, so we need to write 500 in double quote. Then, we can simply use the MROUND function, just like in previous examples, but this time our multiply isn’t 2, but 0.02. If we aren’t halfway, we can simply round our values using the ROUND function (Fig. 17)
We can see that the rounding went up when the significant number was odd, and the rounding went down when the significant number was even. When we passed the halfway threshold, we started rounding up. We have a formula that works with decimal places and the banker’s rounding, however, we have to remember about our assumptions. We set the precision to five digits, but only the last three of them are important for us. Based on the last three digits, the rounding uses the MROUND or the ROUND function (Fig. 18)
This task is quite easy as we have proper functions, however we have to understand how the rounding works in Excel.
The most important thing is the most significant digit. When we round to whole numbers, e.g. to a dollar, the most significant number is the first digit after dot. If this number is 4 or less, the rounding goes down. However, if the number is 5 or more, the rounding goes up. If we remember this, we can start rounding in Excel (Fig. 1)
Fig. 1 The digit after dot
Now, we can use the ROUND function, give it a number and write the number of digits we want to round up. Since we want to round to whole numbers, we have to write 0 (Fig. 2)
=ROUND(A2,0)
Fig. 2 ROUND function
And just like that we have rounded to whole dollars. We can see that in the case of 49 cents, we are going down, and in the case of 50 cents, we are going up. And from 5 to the end of the list, we are only going up, as we have passed the halfway threshold (Fig. 3)
Fig. 3 Halfway threshold
If we want to round to pennies, the situation is almost the same. However, in the case of rounding to whole dollars, the most significant number was the first one after dot, and in this case, the most significant digit is the third one. If the digit is 5 or greater, it will go up by a penny. Let’s use the ROUND function again, give it a number, but now we have to write 2 in the place of the number of digits. As we know, a penny is one hundredth of a dollar, which means that it’s two digits after dot (Fig. 4)
=ROUND(D2,2)
Fig. 4 Rounding to pennies
And just like that, we have rounded numbers to pennies. We can see that when we passed the halfway threshold, we started rounding up (Fig. 5)
Fig. 5 Results
In Excel, we can round even to bigger multiplications of ten, e.g. one hundred. In this case, the second number is the most significant one. We have to remember that when rounding to one hundred or any bigger, whole numbers, we have to write the number of digits as negative. Since we are rounding to one hundred, we have to write ‑2, as there are two 0s, or two digits to the left from 1. When we go to the right from the dot, as it was in previous examples, we have to write a positive number, and when we go to the left, we write a negative number (Fig. 6)
=ROUND(G2,-2)
Fig. 6 Rounding to 100
As we can see, when we went halfway, i.e. 50, we rounded up (Fig. 7)
In the previous post, I was talking about a dominant, which is the most frequent number. Today, we are going to find the most frequent text.
Most frequent text
This task is a bit harder, because the MODE function that we used previously needs numbers. It means that we have to convert text into numbers. To do so, we can use the MATCH function. It means that we will be looking for our whole text in our whole text. We want the exact match (Fig. 1)
Fig. MATCH function to look for the exact match
While using the MATCH function, Excel starts calculations from the top. It means that each puppy in the range is referring to the first one on the list, which is on the first position is presented as 1 (Fig. 2)
Fig. 2 Puppy gets number 1
Looking at the second example, the first kitten is in position number 2, so each kitten will refer to the same number (Fig. 3)
Fig. 3 Kitten gets number 2
Now, that we have our text connected to numbers, we can add the MODE.MULT function to return the most frequent numbers, i.e. positions of text (Fig. 4)
=MODE.MULT(MATCH(A2:A9,A2:A9,0))
Fig. 4 MODE.MULT function to return the most frequent numbers
And we have the results (Fig. 5)
Fig. 5 Results
Since we have the positions, we can add the INDEX function to return text connected to those positions (Fig. 6)
=INDEX(A2:A9,MODE.MULT(MATCH(A2:A9,A2:A9,0)))
Fig. 6 INDEX function to return text
Just like that we have our solutions in the Dynamic Array Excel (Fig. 7)
Fig. 7 Solutions in Dynami Array
In the classic Legacy Excel, we should select more cells and use the Ctrl + Shift + Enter key combination to put our results in all cells (Fig. 8)
Fig. 8 Key combination
However, we are in DA Excel, so we have a simpler solution. At the end, we want to check what happens when the text appears only once in a range. We can see that the MODE.MULT function returns the #N/A error (Fig. 9)
Fig. 9 #N/A error
When text appears the same number of times, in our case it’s cat and dog, the MODE.MULT function will return those results (Fig. 10)
Today, we want to find the most frequent value/number.
Most frequent number
From statistical point of view, we want to find a dominant. This task is quite easy in Excel, however there are some nuances. It’s easy thanks to the MODE function. We can use it to find a single dominant. In this function, we just select the range, and Excel will quickly calculate it for us (Fig. 1)
=MODE(A2:A9)
Fig. 1 MODE function
We have to remember that while using the MODE or MODE.SNGL function, Excel will return only one number. It means that we cannot be sure that only this number is the most frequent one. There is a way out, as we also have the MODE.MULT function (Fig. 2)
=MODE.MULT(A2:A9)
Fig. 2 MODE.MULT function
After using this function, we can see that in our example there are two most frequent numbers: 5 and ‑1. The MODE function showed only the number that appeared at the beginning of the range. When I change the order of the two first numbers in the range, we can see that now the MODE.SNGL function returns ‑1. The MODE.MULT function will still return two number, however in different order (Fig. 3).
Fig. 3 Different order of numbers
When we use the MODE.SNGL function in a range where there isn’t any most frequent numbers and each number occurs only once, the function will return the #N/A error. In our range of Wages, each wage appears only once (Fig. 4)
Today, we are going to learn how to insert scroll bars in Excel and where we can use them.
How to Insert Scroll Bar
In our first example, we have a PMT function. The scroll bars help us change our value for the PMT function more rapidly (Fig. 1)
Fig. 1 PMT function
Let’s insert a scroll bar from the film Excel tips 43, when I was changing the time with a scroll bar. Let’s start with the Developer tab. It’s turned off by default, so we have turn it on. Just right click on the ribbon and choose the Customize the Ribbon option (Fig. 2)
Fig. 2 Customize the Ribbon option
In the window that appeared we have to find the Developer tab, select the proper checkbox and press OK (Fig.3)
Fig. 3 Developer checkbox
Now, we can go to the Insert option and select the Scroll Bars in the Form Controls area. We aren’t interested in ActiveX Controls because they are connected with VBA, and we need something simple. Now, we can draw our scroll bar. When we are holding the Alt key, Excel will fit our scroll bar to cell borders. We don’t always want to do it, but we have such an option. Even after selecting our scroll bar area, when we click the Alt key, Excel will still fit it to cell borders (Fig. 5)
Fig. 5 Cell borders
In our case it isn’t so important. Now, we have our vertical bar (Fig. 6)
Fig. 6 Vertical bar
And horizontal bars (Fig. 7)
Fig. 7 Horizontal bars
Let’s look at properties of the scroll bar. We have to right click to select the scroll bar and right click again to go to the Format Control option (Fig. 8).
Fig. 8 Going to scroll bar properties
In the window that appeared, we have some options, such as Format Control which is now 26, Minimum value which is 0, Maximum value which is 100. We have to remember that scroll bars can have only integer numbers from 0 to 30.000. In our example, I want to work with time and I want the scroll bar to have a 1‑minute precision. It means that I have to set the maximum value to 1440, as this is the number of minutes in one day. The next option, which is Incremental change, is connected to the arrows at the ends of our scroll bars. Then, we have Page change which is connected to clicking on our scroll bar. However, the most important option here is the Cell link bar. We can connect Current value to Cell link. Let’s assume that I want to connect our scroll bar to cell E1. I’m clicking on the cell and pressing OK (Fig. 9)
Fig. 9 Connecting Current value to a cell
Now, the value in cell E1 changed into 26 because that’s the current value in our scroll bar (Fig. 10)
Fig. 10 The same value
When I move the scroll bar, the value in cell E1 is also changing in accordance to the value on the bar. When we click on the arrows on our scroll bar, the time is changing minute by minute downwards or upwards. When we click on the scroll bar, but not on the arrows, we move by 10 minutes upwards or downwards, depending on which part we are clicking. We can move even more rapidly by catching the slider and moving it.
Our value from the scroll bar is an integer. Sometime, though, we need time or percentages. The question is, how can we changed integers into time or percentages? In our example, we have to just divide the value by 1440 and we will get the time in minutes (Fig. 11)
