EXCEL GRAPHICS

Please take a look the slides and make an Excel graphics

Excel Graphics

1

1

Excel Graphics
2
As you complete the analytics for a study of some kind, you will likely need to prepare a presentation of your findings – perhaps to middle or senior management. Your presentation time will likely be constrained to about 10 minutes, because of meeting time constraints and considering the other presentations that will also be scheduled at the meeting. It is important to convey your findings quickly and with little or no misunderstanding. Presenting with graphics is usually an excellent way to convey your findings. The presentation graphics should have the following qualities.
The graphic must be simple to understand. Someone in the meeting should be able to understand its message within 4-5 seconds of seeing it. This includes having a simple and short title and simple to read graphs.
Do not use red colors (which signal a problem), green colors (which signal all is OK), or yellow colors (which signal a possible problem) in the graphic unless the purpose of the graphic is to highlight those problems and successes.
Ensure that all fonts are large enough to been seen in the back of the room.

2

Excel Graphics
3
BAR CHART EXAMPLE
Please develop a spreadsheet as you go through the following set of slides.
The owner of a small company of 26 employees wants to know how the employee degrees are distributed. After some analytics of HR data, the following recap table is built. Prepare a corresponding bar chart as a presentation graphic showing how many or each type of degree are held by employees.
15 K L M N O
16 Master’s Bachelor’s High School Grand Total
17 Grand Total 4 19 3 26

3

Excel Graphics
4
Here is an example of a finished product. It is easy to understand quickly, and contains no red, yellow or green in the bars. Fonts are relatively large. This is not the only format; you are free to design the cosmetics of your graph basically as you choose.

4

Excel Graphics
5
The below graph can be considered the completion of the first step of building a good presentation graphic. The next set of slides will show at least one way to generate this graph and then convert this “raw graph” into a presentation-quality graph.

5

Excel Graphics
6
Follow the following steps to get from the table to the graph.
Go to the Excel ribbon and click on Insert/Column, and select the 2D Column format in the top center of the format options.
A popup should appear on the screen. It may be blank, or filled in. Excel may try to guess how you want the graphic to appear. It is usually incorrect.
If the popup is filled in, erase what is in there. Left click the center of the graph, and right click. Select “Select Data.” Then click on “Remove” to clear the popup.

6

Excel Graphics
7
Now input the data for the graph.
Left click the “Add” button. This will eventually populate the y-axis values. The following popup will appear, although it should be blank.
Type in some name for the data that will appear in the graph legend.
Go to “Series values” and highlight the spreadsheet cells which have the number of degrees from slide 3 (columns L through M, row 17).
Click “OK.”

7

Excel Graphics
8
Your graph should look much like this one as the first step is complete.
The data is properly shown. Now to add cosmetics to transform this raw graph into a presentation graphic. First, move the chart into its own worksheet tab: right click into a vacant area, and select “Move Chart.”

8

Excel Graphics
9
Click on the button by “New Sheet,” click “OK,” and the chart will automatically move to a new worksheet. If you want to, you can name the sheet where the dialog box indicates “Chart1” by deleting “Chart1” and putting in a worksheet name convenient for you.
Move over to the new chart worksheet to add cosmetics. These actions can be done in any order you choose.

9

Excel Graphics
10
Move the mouse onto the center of any bar and right click. Click on “Add Data Labels.” The graph should now have data labels on each bar. I have also formatted them in Bold and 12 pitch to make them easier to read from the back of a meeting room (go to any data label, left click, and them format as you would in Word or Excel).

10

Excel Graphics
11
Move the mouse onto the blank area inside the graph, right click, and left click on “Format Plot Area.”

11

Excel Graphics
12
This popup dialog box now appears. For the graph in this presentation, I clicked on “Solid fill” and then went down to “Color” and selected a light gray. You should click on each of these Fill options to see what Excel provides. Take about 3-5 minutes now to do so. You may prefer to choose different options.

12

Excel Graphics
13
My graph now looks like this. Yours should be similar although your cosmetics preference my be different than mine.

13

Excel Graphics
14
I prefer to have most of the numbers and labels in 12 pitch bold Calibri font. Bold, and at least 12 pitch, makes the graph easier to see from the back of the meeting room.
1. Left click on any number on the y-axis, and format at Bold 12 pitch.
2 and 3. Do the same for the x-axis and legend.
4. Do the same for the Title, except that I prefer 16 pitch to make it stand out better.
The graph on the following slide is updated with the bolds.
1
2
3
4

14

Excel Graphics
15
Consider moving the legend to below the x-axis. This will enlarge the graph.
Right click on the text box “Degrees in Company.” A new popup dialog box appears (see next slide).

15

Excel Graphics
16
I prefer the legend below the x-axis. To accomplish this, I click on “Bottom.” You may experiment with various positions for the legend.
The graph after moving the legend to the bottom is on the next slide.

16

Excel Graphics
17
The x-axis has a 1, 2, and 3 as its labels, but it would be better to name the college degrees.
Right click over any number on the x-axis, click on “Select Data,”

17

Excel Graphics
18
The left popup is first shown. Click on “Edit” and the second popup appears

Left popup Second popup
For the second popup, click in the “Add label range” box and then highlight the cells from the table L16 to N16 from the data area of the first worksheet. Click on “OK.”. This will properly label the x-axis.

18

Excel Graphics
19
This is how the new Select Data Source popup appears before clicking on “OK”
This is how the newly updated graph looks like after clicking on OK.”

19

Excel Graphics
20
The y-axis needs a label. I won’t label the x-axis because it is obvious at least to me what that data set represents, and the legend is sufficient if necessary.
Click on any number on the y-axis/Layout/Axis Titles/Primary Vertical Axis Title/ Rotated title. A text box will appear by the y-axis. Click in the box, and format so that the title reads something like “Number of Employees.”

20

Excel Graphics
21
In my opinion, the margins on the graph are too small. I want to enlarge them.
Click anywhere on the graph background, and a small box appears on the edge of the graph next to the y-axis number 10. Put the cursor over that until the cursor changes in format to a double sided arrow. Left click, then move the margin in towards the center of the graph to taste. Do the same for each side as you prefer. You may want to move the title text box down to accommodate the new graph size.
What I consider to be a complete graph, ready for presentation, is on the next slide. Yours may look different, but it must show the same data and provide the same message to those seeing the presentation graphic.
This presentation graphic can be cut and pasted into either PowerPoint or Word.

21

Excel Graphics
22

22

Excel Graphics
23
15 K L M N O
16 Master’s Bachelor’s High School Grand Total
17 Grand Total 4 19 3 26

PIE CHART EXAMPLE
The data in the original table can be portrayed in a bar chart, and also in a pie chart. An example of a completed pie chart is on the next slide.

23

Excel Graphics
24
Here is an example of a finished product. It is easy to understand quickly, and contains no red, yellow or green in the bars. Fonts are relatively large. This is not the only format; you are free to design the cosmetics of your graph basically as you choose.

24

Excel Graphics
25
The below graph can be considered the completion of the first step of building a good presentation graphic. The next set of slides will show at least one way to generate this graph and then convert this “raw graph” into a presentation-quality graph.

25

Excel Graphics
26
Follow the following steps to get from the table to the graph.
Go to the Excel ribbon and click on Insert/Column, and select the Pie format in the top left corner of the format options.
A popup should appear on the screen. It may be blank, or filled in. Excel will try to guess how you want the graphic to appear. It is usually incorrect.
If the popup is filled in, erase what is in there. Left click the center of the graph, and right click. Select “Select Data.” Then click on “Remove” to clear the popup.

26

Excel Graphics
27
Now input the data for the graph. Populate the y-axis first.
Left click the “Add” button. This will eventually populate the y-axis values. The following popup will appear, although it should be blank.
Type in some name for the data that will appear in the graph legend.
Go to “Series values” and highlight the spreadsheet cells which have the number of degrees from slide 23.
Click “OK.”

27

Excel Graphics
28
Your graph should look much like this one as the first step is complete.
The data is properly shown. Now to add cosmetics to transform this raw graph into a presentation graphic. First, move the chart into its own worksheet tab: right click into a vacant area, and select “Move Chart.”

28

Excel Graphics
29
Click on the button by “New Sheet,” click “OK,” and the chart will automatically move to a new worksheet. If you want to, you can name the sheet where the dialog box indicates “Chart1” by deleting “Chart1” and putting in a worksheet name convenient for you.
Move over to the new chart worksheet to add cosmetics. These actions can be done in any order you choose.

29

Excel Graphics
30
Move the mouse onto the central area of any pie slice and right click. Click on “Add Data Labels.” The graph should now have data labels on each pie slice. I have also formatted them in Bold and 12 pitch to make them easier to read from the back of a meeting room (go to any data label, left click, and them format as you would in Word or Excel). Also, I clicked on each one and moved them into the center of their slices (optional). You can move them to any place you wish using this method.

30

Excel Graphics
31
Recall that is can be misleading to show data in red, yellow, or green. Excel chose the colors of the pie slices and they need to be changed. Move the mouse onto the largest pie slice. Left click twice, and right click. Choose “Format Data Point.” Click on “Fill.” I chose “Solid fill,” and the color light purple. The graph now looks like the next slide.

31

Excel Graphics
32
Do the same for the green slice. I am choosing a light tan. You may also alter the blue slice, but I am not.
Also, sometimes the data label is setting on a dark color background. Double lick on the data label in the blue pie slice, and format it as a white colored font.
The results are on the next slide.

32

Excel Graphics
33

33

Excel Graphics
34
Consider moving the “1 2 3” legend to below the pie.
Right click on the legend text box. A new popup dialog box appears (see next slide).

34

Excel Graphics
35
I prefer the legend below the x-axis. To accomplish this, I click on “Legend Options” and then “Bottom.” You may experiment with various positions for the legend.
The graph after moving the legend to the bottom is on the next slide.

35

Excel Graphics
36
There is one more thing to do. The legend shows the numbers 1, 2, and 3. Those need to be changed to the degree types.
Right click over any pie slice, click on “Select Data,”

36

Excel Graphics
37
The left popup is first shown. Click on “Edit” and the second popup appears

Left popup Second popup
In the second popup, highlight the cells from the table L16 to N16, and click on “OK.”. This will properly label the legend.

37

Excel Graphics
38
This is how the new Select Data Source popup appears before clicking on “OK”
This is how the newly updated graph looks like after clicking on OK.”

38

Excel Graphics
39
Sometimes it can be helpful to label each pie slice, as below. See the next slide for the procedure to do this.

39

Excel Graphics
40
Right click any pie slice, then click on “Format Data Labels.”
When this popup appears, click on “Label Options,”
“Category Name” (i.e., the degree type), and “Value.”
Then “Close.”
The final graph, ready for presentation, is on the next slide. It can be copied and pasted into either Word or PowerPoint.

40

Excel Graphics
41

41

Excel Graphics
42
There is a lot more in Excel than covered here but this should be enough to get you started in preparing very good presentation graphics. If you have basically mastered this, then go onto the Homework.

42

Excel Graphics
43
HOMEWORK
Prepare a bar chart for the data in the following table. This hypothetical table shows the number of home runs hit in 2017 by these three baseball players. The next slide shows a possible presentation graphic of this data.

Prepare a pie chart of the data. The second slide following shows a possible presentation graphic.
Submit these online. Put both graphs and supporting calculations into a single Excel file.

43

15
K
L
M
N
O
16
Row Labels
Jones
Baker
Smith
Grand Total
17
Home Runs in 2017
43
51
39
133