Microsoft Excel 77-727 – 2.3 Summarise and Organise Data

1. 2.3.1 Insert Sparklines

Sparklines are a feature which allow you to create a miniature chart inside a worksheet cell. The idea is that it allows you to improve the visibility and readability of your worksheet without having to create full size charts which may take up a lot of space. Spark lines are a visual representation of each value in a horizontal or vertical range of cells. It’s essentially a mini chart art. Let’s have a look at Helpless Airlines passenger numbers for each month in 2017 and from each of their departure airports. So here’s the raw information and let’s see what we can do with spark lines. I’m going to put the spark line into the old volume for each row of numbers.

What I want to see is the trend from January to December over the course of the year. I just widened this column a little to give me more room to display the spark line. Here on the Insert tab, we can see the sparkline group and you can see that there are three types to choose from. We start with a line type. So when I click on this selection, I get the Create Spark Lines dialog. I have to specify the data that I want to use to create my spark line because I want to see the trend from January to December.

For the first airport, I can select that cell range by clicking on cell B three and dragging to M three. The location I want the spark line to be placed in is shown here by default. It’s the cell that I started the process off in and it’s the correct sell. Now, when I press the OK button, I get my spark line showing a nice, even growth path over the period of time. I can now auto fill the rest of the data. Now, you notice that all of my spark lines look the same. That’s because I was lazy and just increased every month by 10% for every airport.

So that’s what you’d expect. But let’s change some of the data just so you can see where there are different trends. We go to Katanya and modify the figures and you can see that a true representation of the data is being created. I could have done this slightly differently. I’m going to delete these sparkline cells and redo the experiment. This time, however, I’m going to select all of the data at once and I’m going to specify the destination as the range of cells I want spark lines to appear in. Three to 15. Now we get to spark lines all at once. This range of cells now becomes a sparkline group. Therefore, a format change to one of those cells automatically applies to the other cells in the group. If I select any one of these cells, you can see that the whole group is highlighted.

When you select any of the spark lines, the spark line two mainly becomes available with various options to show markers first and last points. Change the style of the spark line by selecting from this drop down. Change the theme colors here. And indeed, I can also change each of the individual point colors. Also, I can also change the type of spark line by selecting from one of the other two types column or Win Loss. The column spark line uses the height of each column to the size of each value.

The Win Last chart type indicates whether each value is positive greater than zero or negative less than zero. In this chart, every bar is the same shape and size. However, positive values appear as higher bars and negative values appear as lower bars. You can clearly select the spark lines by using these options. So if I select the first spark line, I can selectively clear it from the group, or I can delete the whole.

2. 2.3.2 and 2.3.3 Outline Data and Insert Subtotals

In the outline group. I’ll use the Helpless Airlines flight data to illustrate how this works. So here we have our basic flight data worksheet. And what I would like to do is to create subtotals which will be calculated whenever the departure airport changes. The fees that I want to calculate the subtotals on are the flight number and the number of passenger fees. First thing I have to do is to sort the information based on the departure airport. This has to be done because I want the subtotals to be calculated whenever the departure airport changes. So I have to have all of the records for each departure airport listed together in the worksheet. So if I click anywhere in this column, I can use the Sort option to sort the data on the departure airport in ascending order. Now that the data is sorted, I can use the outline options in the data group to start creating my outline information. First I have to select all of the data I’m going to summarize.

Now I go to the Data tab and select the subtotal option from the outline group. The subtotal dialogue is now displayed. The at each change box allows me to select the field where I want the subtotals to be calculated, which in this case is the departure airport. Now we specify what function I want to use when the change in departure airport occurs. We’re going to use the sum function to add up the information in the number of passengers sealed. So I select the number of passengers sealed in the Add subtitle to box. That’s my first level of outlining. Let’s see how this looks. So on the left hand side we have these three boxes one, two and three. As I click on these in turn, you can see the way that the data are presented. Clicking on the number one provides me with the highest grouping, which is the grand total and gives me the total number of passengers from all airports. So we have 5645 passengers in total from all of the airports.

Ticking on two gives me the summary total for each of the departure airports. So we have the total passenger from each of the six airports plus the grand total. Selecting three shows me all of the underlying data. But the nice thing here is that I can be selective if I want to view most of the data at level two, but I want to see all of the underlying detail for cork also. Then I can just click on the plus sign beside Cork and that specific level of detail is revealed. Now I can have more than one level of outlining for my data. Suppose I also want to see a summary of the number of flights from each airport as well as the number of passengers subtotals.

Well, I can specify a further level of subtotal by using the same technique. So I go back to the subtotal option and use the departure airport as the changing point once again. This time I specify count as a function, and I select the flight number field to use the count function on. It’s important to make sure that the replace current subtitles checkbox is unticked. Let’s see the result. So, at the highest level, we had 42 flights and 5645 passengers. At the third level, we can see the number of flights and the passenger numbers from each departure airport. So, for example, we had five flights from Cork with 590 in passengers. So outlining, as we’ve seen, is a very powerful means to present very detailed information in a manner that is clear and understandable.

However, sometimes it may be necessary to create the groups in your data manually because there may not be any natural break in the categorization of the data. Of course, there is a natural break in the data that I’m using. But for the purpose of illustrating the techniques you need to know for manual grouping, I’m going to demonstrate how to do this using the same data from Helpless Airlines. Manual grouping is a much more tedious process, but you are required to understand how to group and ungroup data for the Moss exam. So let’s get started. First, I removed the existing outline functions. So I select all of the data once again and go to the subtotal command. Now I can select the Remove All button to remove the outlines I created earlier. I can create manual groups of data by selecting the data I want to group and by clicking on the group command in the data group. Let’s do this for Rsifi first. But to illustrate the technique more clearly, I’ve added an additional flight from Rsfa as grouping only one record doesn’t make a lot of sense. Before I can group the data, I have to manually insert the subtotals by row after each airport.

To do this, I have to insert rows to make space for the departure airport subtotals. I’m going to use the subtotal formula to manually create the subtotal for each airport. I select the first Belfast record and click the insert option on the right mouse button. Now we have a new row beneath the Araceifi records. I put a title in the first cell, Araceifa subtotal, and then the number of passengers climb. I put in the formula. The formula we’re going to use is subtotal. This formula has a selection of functions that can be used and they’re identified by number. I’m going to select number nine, which is the sum function. After the comma, we have to tell the formula what it is that we want to add up. So I click on these two cells and close the brackets. Now I repeat the process for Belfast.

So I select the first catania record and insert a new row. I put in a title for the row of Belfast subtotal. Now I’ll add in the subtotal formula, this time selecting the range of cells that I want the format to use some function on. Now we can repeat this process for all of the departure airports and we end up with the worksheet looking as follows now in the group pull them, we can select the Auto Outline option. And now the data have been outlined at two levels the grand total for each departure airport and the detail beneath each. Clicking on the plus sign beside each airport name reveals the underlying details. If I want to remove the outline then I can go to the Ungroup option and select the Clear Outline option.

The other way to achieve the same result is to manually create the groups. To do so, I can select a group of cells. I select the Belfast one for illustration and then click on the Group option. In the Outline command group you can see that the Belfast records have been grouped and that I can view the information at an aggregated or a detailed level. It’s important to note that if you’re using multiple subtotals then you need to ensure that the replace current subtotals checkbox is turned off. When creating the first or only subtotal for a worksheet, ensure that this checkbox is turned on. When creating subsequent subtotals, make sure that the checkbox is turned off. In the next lecture we’re going to learn how to.

3. 2.3.4 Apply Conditional Formatting

We’ve seen many of the features in Excel which allow you to format your worksheets to enhance how they look or to place an emphasis on certain data elements. Excel also has a feature named conditional formatting, which can be used to highlight areas of your worksheets based on predefined conditions which you specify. If those conditions are met, then the worksheet is automatically formatted to highlight the sales involved. This is a very powerful way to draw attention to specific data elements without the additional effort of manually changing the formatting. Each time you modify data or add new data to a worksheet, you get the conditional formatting options here in the Home tab. This pull down menu has quite a number of options, but you can see that the word rules occurs a lot. That’s because conditional formatting is based on the rules that you define.

For example, you can specify that if a value in a particular cell were over 500, then you want that cell to appear with a red field in order to warn the user that the value specified has been exceeded. So you have to specify the conditions or rules that would cause the formatting to change automatically. So first we look at the Highlight cell rules options. First, this allows you to select preset rules or customize your own rules to determine when specific formatting is to be applied. This provides the means to highlight cell values based on their relation to specific values that you enter. So we have what are called boolean expressions greater than, less than, between, and so on, which we can use to determine the formatting we specify. For example, in the Passenger Numbers worksheet, we can apply condition and formatting to this range of cells the total number in each month to say that if the total is less than 100, we want the cell to be highlighted.

So first I select all of the cells I want the rule to be applied to. Next, I select the Conditional formatting menu and highlight rules, and then I choose the less than option that brings up the less than dialog box. Now I specify the value 10,000, and then I can select the type of formatting I want to apply. I can select from the preset format, or if I select the custom one, the Format Cells dialog box which we’ve seen before is displayed. I can then specify any custom format I want. I’ll just select a preset format and we can see the effect. So the cells that meet the criterion I specified are now formatted differently to all of the others, bringing attention to the fact that passenger numbers less than 10,000 are an issue to watch out for. The other operators work in exactly the same way. Try them out and see for yourself. Next, we have the top bottom rules, which provide a series of preset rules based on the top or bottom ranking cells in a range. So using the same group of cells.

I can specify that I want to see all cells which are in the top 10% in red with a pink background and the bottom 10% in dark green with a light green background. Or I could decide that I want to highlight cells which are above the average for this group as a whole. Next, we have the option to use data bars which allow you to embed a bar chart into a selected range of cells based on the value in each cell. This type of formatting offers a quick visual representation of each cell value in relation to the others. You can see the effect as I hover over the options.

The chart slide is shown within the cell itself. Visually, this can be far more effective than just seeing a group of raw numbers. When I widen the column, the effects becomes even more pronounced. The color scales option allows you to show the relative value of each cell to each other cell using color gradients. This example shows a red yellow green option with the lowest value highlighted with dark red, dark green for the highest value and yellow for the middle values. Icon sets can be used to highlight values using icons. In this example, a five bar strength meter similar to the wireless network strength indicator of mobile computers and devices, indicates the relative value of each cell to all other cells in the range. The cells with the lowest values have no bars in filled, whilst the highest values have all bars filled. In all of these examples, you see the same conditional format applied to an entire cell range.

If you change the value in any of these cells, the conditional format is automatically reevaluated and the highlighting nature of range. Finally, a cell may have both a manual format and a conditional format. If the cell contains a value that does not meet any of the specified conditions, the cell uses the manual.

4. Exercises Domain 2.3

Workbook and in the correct worksheet we select B four to G ten. Now we can apply conditional formatting. So here I say highlight between and now we put in the two values that I want to control the conditional formatting. So it’s 9000 17,000. I go on to format this in yellow. So I picked the pull down to go to custom format and select yellow fill. And you can see the cells are highlighted in yellow. We can also put in data bars. So I go to conditional formatting, haven’t selected the range of cells and go to data bars and then select the one that I want.

So I want the red data bar, solid fill. I should also use text and conditional formatting. So from a four to a ten condition of formatting and I specify if the text contains the word advert, I want it in green so I can change the formatting, select the color I want, press OK. And you can see two of the cells are highlighted in green because you have the word advert now and clear all of the rules. So conditional formatting and clear the rules, not manage rules. We got to clear the rules so we can get rid of this and select and said clear rules. Now let’s select b four to G ten.

And I want to apply a yellow field where the cells are greater, the data is greater than 400. So this is the first condition I’m putting on this range of cells. You can apply multiple conditions to a range of cells. So here’s the first one. If it’s greater than 40,000, I want a yellow fill. So I go to format and pull down menu, custom format, yellow fill. But now I want to add another condition to that same range. So I select the range again and if the value is less than 10,000, then I want a blue fill so I can have multiple conditions on the same range of cells. Now we’re going to open up the flight workbook, flights detail workbook. And what I want to do now is do outlining. So I’ll open up the file. It’s got a lot of data in this file, but I want to subtitle each change of the parachute airport. So I select I click anywhere within the table and now we go to data and subtotal and we get this dialog box. I want to change on the partner airport. I want to sum the information, but there are other functions. But in this case I want to sum the number of passengers. So I press OK.

I want the page break between the groups. In other words, every change of departure airport. And you can see the answers being shown here. Now, on the left hand side you can see the outlining. If I click on one, that’s the highest level. You can see the total number of passengers is shown. And now I go to the next level which is each of the airports and then I can select one of them, like Cork and show all of the detail by clicking on the plus sign. Now we can remove all the outlining by clicking again on subtotal. I’m saying remove all and it’s all gone again.

I can manually insert subtleties as well so I can manually insert a row. And now I can put in a formula. We’re going to see the formula now in the EAZ intel but this is just a heading band subtotal, but this is equal to subtotal. And subtotal allows you to use a number of functions so we can see the functions. I want number nine, which is the sum function. Now, I haven’t put it in, so I need to go back and put in number nine which tells the system which function I want. So number nine is the sum comma and they’re the range of sales that I want to sum up. So I select the ones for Belfast, normal password for Belfast, close the bracket, press Enter and I get the answer. That’s where it’s manually put in a subtotal.