讲解 ECON10151 Lecture 3 Creating Charts, Tables and PivotTables辅导 留学生Matlab语言

ECON10151 Lecture 3

Creating Charts, Tables and PivotTables

Oct 2024

Learning Outcomes

•  Data Visualisation: understand the importance of data visualisation and learn how to create various types of charts (e.g., column, line, pie) in Excel, selecting the appropriate type for different data scenarios.

•  Chart Customisation: customise charts by adjusting titles, labels, colours, and styles to enhance clarity and visual appeal.

• Table Creation and Management: be able to create, format, and manage tables in Excel, utilising features such as headers, filtering, and sorting to effectively organisedata.

•  PivotTable Analysis: earn to create and manipulate PivotTables to summarise and analyse large datasets, using Slicers for interactive data filtering.

•  Insights from  Data:  develop the  skills to  interpret data presented in charts, tables, and PivotTables, translating it into meaningful insights for informed decision-making.

1   Charts

1.1   Introduction

Excel offers a variety of chart types, each suited for specific data and analysis needs.  A good chart is one that effectively communicates data in a clear and accurate manner, enhancing the viewer’s understanding of the information presented.  It should have a descriptive title that succinctly explains its purpose, guiding the audience on what to expect.  Clear labelling of both the x-axis and y-axis, including any necessary units of measurement, ensures that the viewer can easily interpret the data.  Choosing the appropriate chart type is crucial, as different types of data are better represented in specific ways. For instance, line charts are ideal for showing trends over time, while bar charts work well for comparing categorical data.

The chart should maintain a consistent scale on both axes to avoid distorting the data, ensuring that the visual representation accurately reflects the underlying values.  A well-designed chart is free of unnecessary clutter; excessive colours, 3D effects, or overly complex elements can distract from the key message.  If there are multiple data series, a legend is essential to differentiate them, and annotations or highlights can help emphasise important trends or outliers.  Including the source of the data adds transparency and credibility. Ultimately, a good chart delivers insights in a visually engaging yet straightforward manner.

Below is a summary of the main types of charts in Excel and their typical uses.

•  Column/Bar Chart

Purpose: To compare values across different categories.

Suitable for:  Discrete categorical data, such as sales figures for different products or population num- bers across countries.

Example: Comparing sales of different product lines within a year.

•  Line Chart

Purpose: To display trends over time or ordered categories.

Suitable for: Time-series data or ordered data, such as monthly sales or stock prices. Example: Tracking the monthly revenue growth over a year.

•  Pie Chart

Purpose: To show proportions or percentages of a whole.

Suitable for: Categorical data representing parts of a whole, like market share or survey responses. Example: Displaying the percentage breakdown of company market share.

•  Scatter Plot

Purpose: To visualise relationships between two numeric variables.

Suitable for: Paired numerical data, such as height vs. weight or marketing spend vs. sales. Example: Examining the correlation between advertising spend and revenue.

• Area Chart

Purpose: o emphasise the magnitude of change over time, often cumulative.

Suitable for: Time-series data where the cumulative value is important, such as sales over time. Example: Displaying total sales growth across different regions.

•  Histogram

Purpose: To show the frequency distribution of numeric data.

Suitable for: Data grouped into ranges, such as test scores or age groups. Example: Displaying the distribution of exam scores in a class.

•  Pivot Chart

Purpose: To summarise and analyse large data sets interactively.

Suitable for:  Dynamic data summaries, such as sales data that can be grouped by region, product, or time.

Example: Visualising total sales by region and product.

1.2   Tasks

*Please download the Excel file named L3 Data from Blackboard, which contains the data for the tasks in this lecture.*

Data Description

The dataset in Worksheet Charts contains economic indicators for the United States, United Kingdom, and China from 2004 to 2023. The key variables in the dataset are:

GDP  (Gross  Domestic  Product) :  The total market value of all final goods and services produced within a country in a given year. GDP is measured in thousands of US dollars and reflects the overall economic perfor- mance of the country.

GDP  per  Capita: This represents the average economic output per person and is calculated by dividing the

GDP by the population of the country.  It is measured in thousands of US dollars and provides insight into the standard of living or economic prosperity of individuals within each country.

The dataset spans a period of 20 years, offering a detailed view of the economic growth and performance of these three major economies over time.  This information will be useful for analysing trends in economic development and comparing the relative economic strength and growth between the countries.

The data will also serve as the basis for various analyses, such as calculating growth rates, performing cross- country comparisons, and visualising trends using Excel charts and tables.

1.  Creating a Plot (Single Series): Include the GDP data for the United States from 2004 to 2023 and label it as ”United States.”

Step 1 Select the Data

- Highlight the years (from cell A2 to A21) and the GDP data (from cell D2 to D20) for the United States.

Tip: If you are selecting data from columns that are not next to each other, first select one column. Then,

hold Control (Mac users: Use Command) and select the values in the other column.

Step 2 Insert the Scatter Plot (X-Y Series)

- Go to the Ribbon menu, and select Insert.

- Under the Charts section, choose X Y Scatter. 

- Select the Scatter with Smooth Lines option to create the chart.

Step 3 Add Series Label

- Go to the Add Chart Element option in the top menu.

- Select Legend → Right to display ”Series 1” on the chart.

- Right-click on ”Series 1” and choose Select Data from the dropdown menu.

- Click on ”Series 1” in the Select Data Source window, Click on ”Edit” button, then set the Series Name

to cell C2, or manually type ”GDP” .  (Mac users: Click on ”Series 1” in the Select Data Source window, then set the Series Name from the right side.)

- Click ”ok” .

Step 4 Add Chart Title and Axis Titles

- Change Chart title to ”United States”

- Vertical Axis: Thousands, $

- Horizontal Axis: Year

2. Adding Another Series: Include the GDP per capita for the United States from 2004 to 2023

- Right-click on the chart and select Select Data.

- In the Select Data Source window, click Add (or +).

- For Series Name, select cell C22 or enter ”USA GDP per Capita.”

- For Series Values:

X values: Select the year data from A22 to A41.

Y values: Select the GDP per capita data range (from cell D22 to D41).

- Click OK.

The line representing GDP per capita for the United States appears flat, making it difficult to observe changes over time. This lack of visibility is due to the significant differences in scale between GDP and GDP per capita.  To enhance the clarity of the graph, it is necessary to introduce a secondary vertical axis to effectively represent the scale of GDP per capita.

3.  Use Two Scale Ratios

- Right-click on the line that represents GDP per Capita and select Format Data Series.

- In the Format Data Series pane, click on the Series Options icon (bar chart icon) and choose Sec- ondary Axis.

2   Tables

2.1   Introduction

A well-constructed table in Excel is an essential tool for organising, analysing, and presenting data efficiently. Excel tables enhance data management by automatically formatting and structuring data in a way that makes it easier to sort, filter, and perform. calculations. A good table starts with clearly defined headers, ensuring that each column has a descriptive title to help users understand the type of data contained within. This makes it easier to interpret and manipulate the data, particularly when working with large datasets.  One of the most valuable aspects of Excel tables is their ability to expand automatically as new data is added, which means any functions or formulas linked to the table will update dynamically.

Excel tables also make filtering and sorting simpler.  The built-in drop-down menus allow users to quickly find specific values, organisedata in ascending or descending order, or apply custom filters based on specific criteria. Another powerful feature is the use of structured references, which replace traditional cell references with column names, making formulas easier to read and less error-prone.  Additionally,  Excel tables offer automatic formatting options,  making it easier to apply alternate row colours,  highlight important data, or emphasise specific trends.

Tables in Excel also support the use of total rows, which allow for automatic summarisation of data using functions such as SUM, AVERAGE, or COUNT, without requiring users to manually write formulas.   Pivot tables, often used in conjunction with regular tables, offer an advanced way to analyse and summarise large datasets by organising and grouping data in various ways.   Ultimately,  Excel tables  provide a structured, flexible, and dynamic platform. for data analysis, enabling users to efficiently manage and interpret their data.

2.2   Tasks

Please click on the worksheet named Table, which contains the same data as the Chart worksheet.

1.  Convert to table:

(a)  Highlight all the data in your worksheet.

(b)  Go to the Insert tab and click on Table.

(c)  In the dialog box, ensure that the option My table has headers is checked.

(d)  Click OK.

(e)  Once the table is created, click on any cell within the table to bring up the Table Design(or Table) tab in the Ribbon menu. You will see the default table name, which is Table 1.

2.  Compare GDP and GDP per capita across countries in 2023:

(a) To compare the data, sort and filter the table by the year 2023.  

(b)  Select the table column headers for GDP and GDP per capita.

3.  Calculate annual growth rates of GDP and GDP per capita:

(a)  Go to the Year header and click the down arrow button, then select ”Clear Filter.”

(b)  In a new column next to your data, create a new header titled ’Growth Rates.’ Excel will automatically incorporate this new variable into the table.

(c)  Starting from cell E3 (Column E, Row 3), enter a formula to calculate the annual growth rate for each country’s GDP. (We cannot calculate the growth rate for the year 2004 due to missing information, so the growth rate is calculated starting from 2005.)

(d)  Use the formula:

Growth Rate = GDP in Year (t-1)/GDP in Year (t) − GDP in Year (t-1) 

(e)  Once you press Enter, Excel will automatically apply the formula to all the cells in the column below, utilising the table’s dynamic capabilities.

(f)  Ensure that the value in the Growth Rates column for the year 2004 is changed to N/A, since the growth rate cannot be calculated for that year.

4. Add a Total Row to calculate the variance of growth rates across countries.:

(a) To add a total row, click anywhere in the table and select Table Design(or Table) in the Ribbon. (b) Tick the box labeled Total Row.

(c) The total row will automatically appear at the bottom of the table.

(d)  In the column showing the growth rates of GDP per capita, change the function in the total row to Variance.

(e)  Compare the variance of GDP per capita growth rates across the three countries.

i. To analyse the United States, select the filter dropdown for GDP per capita from the Indicators column and choose United States. Then, scroll to the bottom of the list and select Variance by clicking the down arrow.

ii.  Repeat the process for the United Kingdom by filtering for United Kingdom and GDP per capita in the Indicators column, and select Variance from the dropdown menu.

iii.  Finally, filter for China and GDP per capita in the Indicators column column and choose Vari- ance from the dropdown list.

3    Pivot Tables

3.1    Basics of PivotTables

Excel provides powerful tools for summarising, analysing, and visualising large datasets,

Pivot Tables: A Pivot Table is a tool in Excel that allows you to summarise, analyse, and explore large data sets. You can quickly reorganise databased on categories, averages, or sums, and filter the information with- out altering the original dataset.

Pivot Charts:A Pivot Chart is an extension of a Pivot Table that visualises the summarised data.  They are dynamically linked to Pivot Tables, so any changes made in the Pivot Table (such as adding filters or changing rows/columns) will reflect in the Pivot Chart.

Slicers:  Slicers are visual filter buttons for Pivot Tables and Pivot Charts that allow you to filter data inter- actively.  You can quickly adjust data displayed in your Pivot Tables and Pivot Charts by selecting options in slicers.

Dashboards:  A Dashboard combines multiple Pivot Tables, Charts, and Slicers into one cohesive display, allowing users to interact with the data and view real-time changes.

3.2   Tasks

Data Description: The dataset contains graduate labor market statistics for England, including employ- ment rates, high-skilled employment, inactivity, and unemployment rates categorised by graduate type from 2007 to 2023.

Table 1: Variables Description

1.  Creating Pivot Tables

(a)  Create Pivot Table 1: Employment Rate by Graduate Type Go to Insert tab, Find PivotTable

• Table/Range: Select all data values in the dataset.

•  Choose where to place the Pivot Table: New worksheet - Click OK

•  PivotTable Fields - Configure the following:

–  Rows: year

–  Columns: graduate type

– Values: employment rate (Value Field Settings: Average)

•  Name this PivotTable ”Pivot1.” This Pivot Table will show the employment rate trends over the years for each graduate type.

•  Name this new worksheet as PivotTables Practice

(b)  Create Pivot Table 2: Employment Rate by Age Group

• Table/Range: Select all data values in the dataset.

•  Choose where to place the Pivot Table: Existing Worksheet: PivotTables!$A$27- Click OK

•  PivotTable Fields - Configure the following:

–  Rows: year

–  Columns: age group

– Values: employment rate (Value Field Settings: Average)

–  Filters: graduate type

•  Name this PivotTable ”Pivot2.”This table allows filtering employment rates by specific age groups.

(c)  Create Pivot Table 3: Employment, Inactivity, and Unemployment Rates by Graduate Type

• Table/Range: Select all data values in the dataset.

•  Choose where to place the Pivot Table: Existing Worksheet: PivotTables!$A$50- Click OK

•  PivotTable Fields - Configure the following:

–  Rows: graduate type

– Values:  employment rate, unemployment rate, and inactivity rate (Value  Field Set- tings: Average)

•  Name this PivotTable ”Pivot3.”This table compares employment, inactivity, and unemployment rates across graduate types over the years.

2.  Designing Interactive Dashboard

(a)  Creating Pivot Charts

i.  Pivot Chart 1: Employment Rate by Graduate Type

•  Convert Pivot Table 1 into a Line Chart to visualise employment rate trends by graduate type. [Select Data - Insert tab - Choose Line Chart]

ii.  Pivot Chart 2: Employment Rate by Age Group

•  Convert Pivot Table 2 into a Clustered Column Chart to compare employment rates across age groups. [Select Data - Insert tab - Choose Column Chart - Select Clustered Column]

iii.  Pivot Chart 3: Employment, Inactivity, and Unemployment Rates by Graduate Type

•  Convert Pivot Table 3 into a Stacked Column Chart to compare these rates across gradu- ate types. [Select Data - Insert tab - Choose Column Chart - Select Stacked Column]

(b)   Adding Slicers

•  Click on any cell in the PivotTable, goto the PivotTable Analyse tab, and click on Insert Slicer.

•  Slicer 1: Add a slicer for year to filter data by specific years. Slicer tab - Report Connections - choose Pivot2 and Pivot3

•  Slicer 2: Add a slicer for graduate type to filter by graduate type. Slicer tab - Report Connections - tick all PivotTables

(c)  (Homework) Designing the Dashboard Layout

•  Create a new worksheet, remove the gridlines, and insert a text box with the title ’Graduate Labour Market’ . Additionally, insert three text boxes, each with titles for the three charts.”

•  Left Side: Place the two slicers (for year and graduate type).

•  Middle Section: Add Pivot Chart 3 to display employment, inactivity, and unemployment rates.

• Top Right Side: Add Pivot Chart 1 for employment rates by graduate type.

•  Bottom Right Side: Add Pivot Chart 2 to display employment rates by age group.

• Adjust the colour of the text, remove the outline from the charts and text boxes. Personalise the dashboard based on individual preferences.

(d)  (Homework) Interact with the Dashboard Test the dashboard by adjusting the slicers.  Select dif- ferent years and graduate types to see how the charts update in realtime.

Please find the example included in the L3 worked example Excel file.