Create Charts Practice

xlsx

School

University of Wisconsin, Madison *

*We aren’t endorsed by this school

Course

765

Subject

Statistics

Date

Feb 20, 2024

Type

xlsx

Pages

Uploaded by CorporalJellyfish89

Create Charts Create Charts A good chart quickly conveys the important information in your data. Excel makes it easy to create charts by choosing a chart type from the Insert ribbon. Insert a Chart To quickly create a chart, select the data and labels you want to display, then choose an appropriate chart type from the Insert ribbon. You will often get a good chart this way, but the resulting chart can be modified if needed. Exercise 1 Select the monthly revenue data to the right, in Columns K and L, including the labels, then from the Insert menu , choose the chart called "Line with Markers." Put the chart in the shaded range next to the data. (see finished example to the right) Create a Chart with Multiple Series You can also plot multiple series on the same chart. The data to the right, for Exercise 2, are monthly sales by region, for two regions. We want to create a line chart that contains both series. Again, you would select the data and labels, then choose a chart type from the Insert ribbon. Because there is more than one series, a legend will be included on the chart. Also, the title does not automatically come from a selected label. Instead the placeholder, "Chart Title" is given, which you can rename by typing inside the text box, or by selecting the text box and typing in the formula bar. Exercise 2 Select the monthly sales by region data and labels in Columns K-M. Then from the Insert menu , choose the chart called "Line with Markers." Change the title from "Chart Title" to "Monthly Sales by Region." Put the chart in the shaded range next to the data. (see finished example to the right) Series in Rows Usually, a data series is a column of data, with a label at the top, but sometimes a series or multiple series will be rows. Excel will usually plot these correctly, if you follow the same steps as before. Exercise 3 Select the Ex 3 Data, i.e., for monthly sales by region, including labels, in Columns K-W. Then from the Insert menu , choose the chart called "Line with Markers." Change the title from "Chart Title" to "Monthly Sales by Region." Put the chart in the shaded range below the data. (see finished example to the right) Switch Rows and Columns Suppose you want a chart with the series in rows, but Excel gives you one with the series in Columns, or the other way around? You can easily make the desired change. Simply select the chart and click the Switch Row/Column button on the Chart Design ribbon. The data set below and to the right, for Exercise 4, contains quarterly subscriber numbers for five online streaming services. You can select the entire data range and insert a line chart. But what are the series? By default, Excel creates a line for each quarter. If you make the chart, then click on one line, Excel will highlight which data are plotted to make the line. Then it's

line, Excel will highlight which data are plotted to make the line. Then it's clear that Excel treats the columns as the series of interest. What we want instead is to treat the rows as series, so the lines for each streaming service show subscriber levels across time, i.e., quarters on the horizontal axis. Exercise 4 Select the Ex 4 Data, including labels, in Columns K-O. Then from the Insert menu , choose the chart called "Line with Markers." With the chart selected, click Switch Row/Column on the Chart Design ribbon. Change the title from "Chart Title" to "2022 Subscribers by Quarter." Put the chart in the shaded range below the data. (see finished example to the right) Recommended Charts So far, we've used line charts to look at data over time. Of course, there are other chart types for other purposes. You can utilize Excel's Recommended Charts feature to explore options when you are deciding how to display your data. To do so, you select the data as we have been, then click Recommended Charts on the Insert ribbon, and Excel will show you options that seem appropriate for the data. Often, these recommendations are on track, but just as we've seen with series in rows vs. columns, Excel's recommendation is not always correct. So, always keep your goals and the information in your data in mind when you create charts. Choose chart types that accurately reflect the information in the data, and that make key insights salient to the users of your charts. Below, the data for Exercise 5 are sales for one year at one location of Mike's Bakery, stratified by bakery category. When you want to show strata or categories of some measure, a column or bar chart is often a good choice. Excel's first recommendation for the bakery data is its Clustered Column chart, an appropriate chart type in this case. Exercise 5 Use the Recommended Charts feature to create a chart of the bakery sales data by category. Choose the Clustered Column chart that Excel recommends as a first choice (it shows other options too). Change the chart title to " Bakery Sales " and Put the chart in the shaded range below the data. (see finished example to the right) Scatter Charts A scatter chart shows the relationship (if any) between two numeric variables. For example, you could use a scatter chart to see the relationship between the number of hours you study for each of your exams and your exam scores. Each pair of numbers, hours and score, determines a discrete point on the chart. To create the scatter chart, you select the data range and insert a scatter chart of the first subtype. You can then change the title and add horizontal and vertical axis titles. By default, when you select two columns for a scatter chart, the data in the left column, is the X-axis series, and the other is the Y-axis series. Exercise 6 The data below and to the right show the hours studied for one exam and the exam score achieved by multiple students. Create a scatter plot to show the relationship between hours studied and exam scores. Change the chart title to "Exam Score by Hours Studied" . Add Axis Titles Windows: With the chart selected, click the + that appears at the upper right corner of the chart, then check the box to add axis titles. Mac or Win: On the left side of the Chart Design tab, use the Add Chart

Mac or Win: On the left side of the Chart Design tab, use the Add Chart Element menu (shown to the right) to add axis titles. Label the axes, "Hours" (X-axis) and "Score" (Y-axis). Put the chart in the shaded range next to the data. (see finished example to the right) Save this file and submit it via Canvas.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Ex 1 Data: Ex 1 Chart: Month Revenue Jan $1,002,344 Feb $1,102,578 Mar $1,113,604 Apr $1,336,325 May $1,603,590 Jun $1,683,770 July $1,767,958 Aug $2,298,345 Sept $2,528,180 Oct $3,387,761 Nov $4,742,866 Dec $6,008,736 Ex 2 Data: Ex 2 Chart: Month Region1 Sales Region2 Sales Jan $21,567 $21,130 Feb $13,357 $18,856 Mar $15,610 $13,742 Apr $23,627 $29,844 May $16,745 $14,987 Jun $16,880 $20,257 July $14,962 $15,965 Aug $13,965 $12,617 Sept $21,190 $26,102 Oct $22,974 $26,740 Nov $18,105 $17,285 Dec $20,690 $27,510 Ex 3 Data: Month Jan Feb Mar Apr May Jun Jan Feb Mar $0 $1,000,000 $2,000,000 $3,000,000 $4,000,000 $5,000,000 $6,000,000 $7,000,000 Jan Feb Mar A $0 $5,000 $10,000 $15,000 $20,000 $25,000 $30,000 $35,000 Mon Re

Region1 Sales $64,701 $40,071 $46,830 $70,881 $50,235 $50,640 Region2 Sales $63,390 $56,568 $41,226 $49,532 $44,961 $40,771 Ex 3 Chart: Ex 4 Data (2022 Subscribers in Millions): 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Netflix 222.0 220.6 223.0 231 Hulu 45.3 45.6 46.2 47.2 HBO Max 76.8 92.1 94.9 96.1 Disney 129.8 137.7 152.1 164.2 Youtube Premium 50 58 69 80 Ex 4 Chart: Jan Feb Mar A $0 $10,000 $20,000 $30,000 $40,000 $50,000 $60,000 $70,000 $80,000 $90,000 Mon Re 1st Qtr - 50.0 100.0 150.0 200.0 250.0 2022 Netflix Disney

Ex 5 Data: Ex 5 Chart: Sales Brioche $153,669 Croissant $443,887 Danish $561,886 Roll $230,794 Chart Elements Menu Ex 6 Data: Ex 6 Chart: Hours Studied Exam Score 7 79 12 93 2 28 3 26 10 92 9 95 8 83 8 88 4 58 7 72 7 88 8 86 5 73 5 65 2 36 11 98 Disney Brioche $0 $100,000 $200,000 $300,000 $400,000 $500,000 $600,000 0 2 0 20 40 60 80 100 120 Exam Score

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

3 40 4 38 1 34 4 38 5 45 9 100 12 96 6 55 6 72 0 20 11 92

Place Ex 1 chart here Place Ex 2 chart here July Aug Sept Oct Nov Dec Apr May Jun July Aug Sept Oct Nov Dec Revenue Apr May Jun July Aug Sept Oct Nov Dec nthly Sales by Region egion1 Sales Region2 Sales

$44,886 $41,895 $33,570 $68,922 $54,315 $85,679 $37,895 $37,851 $28,306 $30,220 $21,855 $38,550 Place Ex 3 chart here Place Ex 4 chart here Apr May Jun July Aug Sept Oct Nov Dec nthly Sales by Region egion1 Sales Region2 Sales 2nd Qtr 3rd Qtr 4th Qtr Subscribers by Quarter Hulu HBO Max Youtube Premium

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Place Ex 5 chart here Place Ex 6 chart here Youtube Premium Croissant Danish Roll Bakery Sales 4 6 8 10 12 14 m Score by Hours Studied Hours

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Create Charts Practice

Related Documents