We have already learned about aggregation using the SUMIF function in Excel. Here, I'll explain about summarizing data in Excel, including the use of COUNT, COUNTIF, AVERAGEIF, and handling blank cells. We'll calculate the total number of data N, the total number of invoices per branch, and the average amount per branch using functions.

Imagine a situation like this. You are working for a company and today your boss asks you, How many invoices did all the branches have in total? Your first task is to answer this question.

Since one invoice uses one row, if the data is small, it is quicker to look at the last row. Do you remember the shortcut key to get to the last row of the table? Another way is to use the COUNT function.

The COUNT function returns the number of cells in the range that contain numbers. Enter the COUNT function in a cell and choose the range of Amounts as the argument. Then the return value of 200 will be displayed. This is the number of data, or N in statistics. In this case, N=200. Watch the video below, and practice the COUNT function. You would be able to reply to your boss, "There are 200 invoices in total.”

Say your boss asked you which branch got the highest number of invoices. Task 2 is to get the total number of invoices per branch. Let's make a table of the frequencies of the branch codes using the COUNTIF function.
=COUNTIF(search range, search condition)
counts the number of times the search condition exists in the specified search range.
When the search condition is text, don’t forget to enclose the text in "quotation marks". Watch the video below and practice.

You are further asked by your boss, Which branch had the highest average amount of invoices? This is Task 3: get the average amount per branch. Watch the video below to continue the practice. First, let's calculate the average amount by using the formula of dividing the total amount by the frequency of the branch.

Instead of using such a division, you can use the AVERAGEIF function.
=AVERAGEIF(range to look at for a condition, condition, range to average)

Although the calculation results look the same between the one using Formulas and the other using Functions, it is recommended to use functions. The reason is that the treatment of blank cells is different.

To demonstrate the treatment of blank cells, one of the Fukuoka amount data is deleted in the video. As a result, the average value by the formula became smaller than the average value by AVERAGEIF. Why?

Well, I'm going to explain in a moment, but before that, don't forget to Undo the erase and to restore the data that you just deleted for the practice!

Treating Blank Cells. Basically, Excel treats blank cells as 0, so the formulas have a smaller average value. But some functions recognize blank cells as missing values and won’t include them in the calculation. This is the case here. See the "Remarks" in the AVERAGEIF function. It says "If a cell in average_range is an empty cell, AVERAGEIF ignores it." However, just because it is a function does not mean that all blank cells are uniformly ignored. The same "Remarks" states "If a cell in criteria is empty, AVERAGEIF treats it as a 0 value."

In this way, a function is conveniently designed to determine whether blank cells should be regarded as 0 or ignored as a missing values, as appropriate for the function, so if there is a function, use it.

Up to the section above, we used functions to calculate statistics in the Excel cell range. In this section, you will use table. If you scroll down the table without using “Freeze Panes” and you still see the title row of the columns, it is a table. In contrast to table, the regular cells that you have been using are called range in Excel.

The great thing about table is that Excel recognizes them. In this hands-on practice, you will see how the table recognizes new columns or can total values. Let's watch the video below and practice. In the student ID file, please paste the raw data part in the "Input Data" worksheet into the new worksheet "Table".

There are mainly three ways to create a table. (1) "Format as Table" from the "Home" ribbon, (2) "Table" from the "Insert" ribbon, and (3) shortcut keys Ctrl+T (Windows); command+T (Mac). On the "Table" sheet, click anywhere in the copied cell and do one of the operations above. Chika-chan in the video uses shortcut keys and presses OK on the range.

Please find CopyFromHere03.xlsx. Paste the seven columns "Card", "Model", “Engine”, “Body”, “Exterior”, “Suspension”, and “Security” next to the "Table" in your student ID file. Excel instantly recognizes it as a continuation of the table and makes it part of the same table.

If you click on a cell inside the table, Table Design will appear on the ribbon, and clicking outside the table design makes it disappear. Chika-chan in the video clicked on Table Design and selected a design without stripes. You can choose your own design. Check the Total Row checkbox in Table Design to add a total row to the bottom. When you click on a cell in the total row, you can choose a summary statistic from the drop-down list. It's that easy!

If you want to get out of table and back into a regular cell range, choose Convert to Range in Table Design. Note that when you convert to a range, you lose the drop-down list of the total row. There is also no Table Design on the ribbon. You can always convert it back to a table again. Be sure to leave the table converted to a range before moving on to the next practice.

The next step is to convert a range of cells to a pivot table.
(1) Select the cells in the cell range that you want to turn into a pivot table.
(2) Select Pivot Table from "Insert" on the Ribbon, choose the location where you want to create the pivot table, and click "OK. Name the new worksheet.
(3) In "Pivot Table Fields," check the checkboxes for the variables that you want to be columns.
(4) Click each variable in the "Value" ▼ in the lower right corner, and from the "Value Field Settings," select the aggregate value you want to display (e.g., "Average") and display it in the cell of the pivot table.
(5) Drag the column you want to make into a row from the column name and put it into "Rows.
(6) After adjusting the decimal points, etc., the pivot table is complete.

Visualization is the process of representing data in graphs and other graphical formats to visually grasp patterns. In this section, we will visualize the distribution of values of a variable by using a histogram, which is used to see the distribution of quantitative data, and a pie chart for qualitative data. We will explain the functions of these graphs in Excel by drawing a basic graph for the histogram and a pivot graph for the pie chart.

We will now create a histogram, which is a basic graph to visualize the distribution of values of a quantitative variable. As an example of quantitative data, let's take age.

Now follow the video below to do the practice. Open your own student ID file. In the video, to make it easier to work with, the hight of the formula bar is adjusted with the white up/down arrow cursors and the zoom slider is used to enlarge the display. Select the Age cell range with the shortcut keys and click on the Ribbon Insert > Charts > Insert Statistic Chart > Histogram.

Since we want the histogram to be in a new worksheet, let's move it by clicking inside the histogram to bring up "Chart Design" on the Ribbon, and then choose "Move Chart" > "New Worksheet" > Name Worksheet "Histogram". In the video, the worksheet is positioned to the leftmost side by dragging the tab, but this is optional.

To divide the horizontal axis into the 10s, 20s, ... and 60s & above, bring up the "Format Axis" window. You can open the axis Format pane by clicking inside the chart > + sign > "Axis" > "More Axis Options", by right-clicking on the horizontal axis of the chart, or by using the shortcut keys Ctrl + 1 (Win); ⌘command + 1 (Mac) to open the Format pane and choose from Options. In Mac, try also Chart Layouts > Add Chart Element > Axis > More Axis Options as shown in the image below.

In Format Axis > Axis Options > Bin width, select the width of the histogram. In this case, set it to 10. Under "Underflow bin," set the input value and below as the leftmost bin in the histogram. In this case, set it to 19 because the leftmost bin should be the teens. "Overflow bin" sets the right-most bin for all the values greater than the input value. In this case, we want to group the 60+ age group together, so we set the value to 59, so that values greater than 59 (i.e., 60 and over) will be in the right-most bin.

Since this variable is for the age of card use, the left-hand side of the distribution is terminated at age 18, but even taking this into account, most of the card users are in their 20s, resulting in a distribution skewed to the left. This shape is called positive skewness. Conversely, a distribution skewed to the right has a negative skewness. In the case of a symmetrical distribution, the skewness is 0.

Click on the title and title it "Histogram of Age".

In the video, I changed the color and style as a demonstration. You can also style it any way you like.

Also check the data label options from the + sign. In the style Chika-chan chose, it is easier to read the title in white, so in the video, the text color is changed to white and bold.

Next, we will show how to create and edit a pie chart used to visualize the distribution of values (ratio) of a qualitative variable using Excel's pivot graph.

Let's create a pie chart to visualize the distribution of values of qualitative data from car type A to car type J in a practice. Even if we give a numerical value of 1 for car model A and 2 for car model B, it does not mean that car model B=2 is twice as large as car model A=1. These are qualitative data and they are not numbers for the four arithmetic operations, only numbers that refer to categories (car models). The task here is to figure out from the "Model" column of the table sheet which car type is being rented and by what percentage. To summarize and create a chart, we use a pivot table and a pivot chart. In this case, a frequency distribution table is compiled from the raw data in a pivot table, which is then turned into a pivot chart. Pivot charts can be edited in much the same way as basic charts, but there are three main differences.

The first difference between PivotChart and basic Chart is that a PivotChart and a PivotTable are automatically linked, so changing data in one automatically reflects it in the other. Second, a PivotTable is recognized as a whole table and corresponds to a PivotChart, so it is not possible to make only a part of the PivotTable into a PivotChart. Third, PivotChart displays field buttons that allow you to interactively manipulate the chart.

Now, let's create a pie chart with PivotChart while watching the video below. First, select the Model data, create a pivot table in a new worksheet like you learned in the third session, and click (especially for Mac) insert PivotChart. Editing operations for PivotChart is much the same way as basic Chart, but it shows you that you can choose from Quick Layout to insert values, and how to specify value labels from the Formatting panel to make it your own. In this case, we will specify the value labels to be displayed with the value and % on a new line from the formatting panel.

In the video, Chika-chan checked the contents of the field buttons and turned them off. To turn off a specific field button, choose it from under Field Buttons on the PivotChart Analyze ribbon. To turn off all field buttons at once, click on the Field Buttons icon. Name the sheet "Pie Chart". Supplement the text boxes as needed. For example, let the total number of data be indicated by N=. In this case, N=200.

The figure above is a chart often used by universities in the United States that shows which chart to use for what. In the future, charts will be created as instructed by AI like copilot. What will become necessary in the future will be to judge what kind of charts are appropriate in what case. As shown in the four directions in this chart, there are four major types of charts that can be used to show Comparison, Distribution, Composition, and Relationship. So far, I have introduced Histogram for distribution and Pie Chart for Composition to show how to use the basic Charts and PivotChart. Below, Nakamura-sensei will explain how to visualize Comparisons Over Time by Combo Chart of Column Bar and Line Charts Sparkline, and Trendline.

Hello! I am Chikafumi NAKAMURA, the Faculty of Policy Studies, Chuo University. Let's get started. The video below shows how to visualize Comparisons Over Time by a Combo chart of stacked column bar chart and line chart.

While the stacked column chart allowed us to grasp the overall trend, Sparklines are useful when we want to exploratively visualize the transition of individual item.

Now let's create a trend line that can be used for forecast if the trend continues as it is now, other conditions being equal.

The followings are other charts for demonstration. The video below shows Chika-chan creating Filled Map from the data of the number of Covid-19 infections per million population by prefecture as of April 25, 2021. The data source is this site.

Below is how to make interactive Slicers and Conditional Formatting. Slicer is a kind of interactive filter only available in Table. For more information about Conditional Formatting: Windows users can refer here. Mac users can refer here.

Finally, a demonstration of Macro/VBA to create a bar chart race. In the bar chart race, we will use a feature called Macro in Excel. Macros use a programming language called VBA (Visual Basic for Applications).