202X2 Charts

 |  No Comments
202X2 Charts

See this post to learn about using charts

We can use tabular data (ie the information in a spreadsheet) to produce highly visual charts to assimilate and explain our data

100% stacked bar chart showing sales per monthStacked bar chart showing class size

Area chart showing monthly revenue and expensesLine chart showing phone, tablet and laptop sales

ACTIVITY

EX 1*EX 2EX 3*EX 4*EX 5*EX 6*EX 7EX 8DOWNLOAD
  1. make a new spreadsheet: Survey Results
  2. below is the data from your survey
  3. select the cells that you want to include in your chart (eg A1 to E8)
    Click Insert >> Chart
  4. choose a chart style
Rating Survey results
Very dissatisfied 12
Dissatisfied 23
Neutral 53
Satisfied 118
Very satisfied 94
  1. make a new spreadsheet: Web traffic
  2. below is your data
  3. select the cells that you want to include in your chart (eg A1 to E8)
    Click Insert >> Chart
  4. choose a chart style
Source Percentage
Organic search 29%
Direct 23%
Referral 20%
Paid search 17%
Social 11%
  1. make a new spreadsheet: Annual Home Sales
  2. below is the data for the sales
  3. select the cells that you want to include in your chart (eg A1 to D6)
    Click Insert >> Chart
  4. choose a chart style
Year New Builds Existing
2014 213,933 123,345
2015 196,334 145,899
2016 218,986 189,000
2017 355,698 200,433
2018 415,320 340,210
  1. make a new spreadsheet: Monthly device sales
  2. below is the data for your sales
  3. select the cells that you want to include in your chart (eg A1 to E8)
    Click Insert >> Chart
  4. choose a chart style
Month Phones Tablets Laptops
Jan 76 10 30
Feb 69 18 25
March 45 35 35
April 74 60 50
May 65 55 40
June 80 67 42
  1. make a new spreadsheet: Grades
  2. select the cells that you want to include in your chart (eg A1 to E8)
  3. Insert your chart
  4. choose a chart style
Marks Juniors Seniors
A 14 28
B 26 34
C 35 30
D 30 5
F 20 15
  1. make a new spreadsheet: Trading
  2. select the cells that you want to include in your chart (eg A1 to E8)
  3. Insert your chart
  4. choose a chart style
Month Revenue Expenses
January £20,000 £14,000
February £35,000 £18,000
March £32,000 £15,500
  1. make a new spreadsheet: Sales per month
  2. select the cells that you want to include in your chart (eg A1 to E8)
  3. Insert your chart
  4. choose a chart style
Month North region South region
Jan 44% 56%
Feb 42% 58%
Mar 47% 53%
Apr 54% 46%
May 52% 48%
June 57% 43%
July 60% 40%
  1. make a new spreadsheet: Class size
  2. select the cells that you want to include in your chart (eg A1 to E8)
  3. Insert your chart
  4. choose a chart style
School/University First-year undergraduate Second-year undergraduate Third-year undergraduate Fourth-year undergraduate
University A 5,425 6,251 3,650 4575
University B 3,550 4,580 4,400 4100
University C 4,800 5,250 3,850 3600

If this opens in Google Sheets, download as Excel

Charts Practise D

Advanced

INFOEX AEX BEX CEX DEX E

Having obtained the data above, we can now do some analysis on that data

NOTE:

  • make sure you use the correct format for the numbers (eg %, £ etc)
  • the table is visually appealing

Editing charts

  1. open: Annual Home Sales
  2. add a column: Average
    1. insert a formula to calculate the average number of new builds  2014-18
    2. this needs to be repeated on every row (same answer each time)
  3. create a new chart that shows all the data
  4. place it next to your previous chart
  5. choose a chart style
  1. open: Monthly device sales
  2. add a column: Total Sales
    1. insert a formula to calculate the total sales each month
  3. add a row at the bottom: Average
    1. insert a formula to calculate the average number of sales for each device
  4. create a new chart that shows all the data
  5. place it next to your previous chart
  6. choose a chart style
  1. open: Survey Results
  2. make a copy of Survey Results
  3. call it Survey Analysis
  4. show the total of all the responses
  5. add a column: %
    1. insert a formula to calculate the percentage of each response
  6. create a new chart that shows all the data
  7. place it next to your previous chart
  8. choose a chart style
  9. create the chart title and axis labels
  10. create a legend
  1. open: Grades
  2. make a copy of Grades
  3. call it Grades Analysis
  4. show the total of all the grades
  5. add a column: % for juniors and seniors
    1. insert a formula to calculate the percentage of each grade
  6. create a new chart that shows all the data
  7. place it next to your previous chart
  8. choose a chart style
  9. create the chart title and axis labels
  10. create a legend
  1. open: Trading
  2. download the spreadsheet as an Excel document
  3. show the total of Revenue and Expenses
  4. show the Profit for each month (Revenue – Expenses)
  5. show the total profit
  6. create a new chart that shows all the data
  7. place it next to your previous chart
  8. choose a chart style
  9. create the chart title and axis labels
  10. create a legend