Learning how to create charts and graphs in Excel is super simple with our step-by-step instructions. Graphs put your numbers, data points, and data series into a convenient image. Most people don’t like numbers, but they do like pictures with bright, friendly colors and labels. Today we’ll take a simple housing budget, and turn it into a pie chart and a bar graph. To learn more, follow this lesson below!
How To Create Charts and Graphs in Excel
Create Monthly Housing Budget Spreadsheet
Create this basic spreadsheet, format it as a table, and save it to match the name in Cell A1, “Monthly Housing Budget.” If you need help formatting the spreadsheet as a table, go to the previous lesson, Excel Formatting Cells For A Better Understanding Of Information.
Insert Pie Chart
A pie chart is a circle cut into pie wedges. When you add all the wedges of your budget together, they make up a whole pie or 100% of your budget.
Drag from our header row, Cell A3, down to our last item, Cell B10. DO NOT include the total in your chart range. Click on the Insert tab, and you will see an entire section of charts from the drop-down list. There is even a “Recommended Charts” icon to let Excel guess what type of chart you want. With your data selected, click on: Insert tab>Charts section>Recommended Charts.
The Insert Chart window appears and gives us some samples using our data. Click through the examples to see what they look like, then click on the circle in the pie chart.
Snap to Cells
When you click OK, Excel creates a pie chart from our data and places it on top of our spreadsheet. It is not inside a cell, just floating on top with handles. Excel treats it like a picture. You can make it bigger or smaller by dragging the handles. If you hold down the “Alt” button, it will “snap” to a cell box. Hold down “Alt” and drag the top left corner to Cell D3’s left top corner.
We want the chart title to match the spreadsheet title in Cell A1. Select the word “Amount” at the top of the chart. With “Amount” selected, click in the Formula Bar. Type “=” to start a little formula. Now click on Cell A1 where our title is. Excel will add some extra information to our formula, the sheet name, and some dollar signs to our Cell A1 coordinates, so it shows “=Sheet1$A$1.” Press Enter. Your Chart name now matches Cell A1.
Click anywhere on your chart to select it – the eight little handles will appear, as well as formatting buttons at the top right. Hover your mouse over each button: Chart Elements, Chart Styles, and Chart Filters. We will talk about Chart Elements and Chart Styles today.
Chart Elements include our title, data labels, and the legend. The legend tells you which chart colors match which description. Let’s move the legend to the right. Click on Chart Elements and hover your mouse over Legend. A right-arrow appears. Click on the right-arrow for more choices, and select Right.
Click on the Chart, then select the Chart Style button – the small paintbrush on the top right. This option has two tabs: Style and Color. Select the Color tab.
Pick a monochromatic color you like; I’ll pick orange. To make the table orange, too, first click anywhere on your table, the original data not the chart. Go back to the: Home tab>Styles section>Format as Table drop-down arrow, and pick an orange table format
You can look through various pie chart styles in the Style tab of the Chart Style button, or you can click on your chart, and a special hidden Chart Tools>Design tab will appear. Select your chart, then look at your Design choices on the Chart Tools>Design tab.
Several styles are shown in the drop-down menu, and more are available if you click the drop-down arrow, highlighted yellow. Hover your mouse over the different choices, and your chart will give you a preview. When you find one you like, click. Some show the percentages, some have labels on the wedges. If you like a scheme, but want to return to the legend on the right or want different colors, you may customize it with the Chart Elements button.
Sort the Table and Chart
With our Legend on the right, the budget descriptions match the order in our table. Does the order change if we sort our table? Click the Amount drop-down arrow, and sort “Smallest to Largest.”
The chart changed to match our table. Now switch it to “Largest to Smallest.”
Let’s just look at our utilities. Use the Description drop-down arrow to filter our table, so we only see our three utilities by typing “util” in the Text Filter box.
Our table only shows three utilities, so now the chart shows our three utilities. We have an average amount of $100, but let’s say our actual bills came in. On the table, change the Elect Utilities to $125 and the City Utilities to $75. What happens to pie chart? Excel changes the chart to reflect the new amounts because the chart is “relative” to our data; the picture always matches the data.
Change to a Bar Graph
When only three utilities are showing, the pie chart may not be the best selection. Click Chart Tools>Design tab>Change Chart Type. In the Change Chart Type window, select Bar from the list of charts and click OK. This bar graph is better for comparing the three utility bills.
Remove our “util” filter by clicking the Description drop-down arrow and checking the “Select All” box. Our chart will add all the descriptions but stay in the bar format. Using Chart Tools>Design tab>Change Chart Type, try a few more Chart Types before changing it back to a pie chart.
Change the Title
The new utility numbers we used were from our October bill, so edit the title in Cell A1. Click on Cell A1, go to the Formula Bar, and insert “October” and space at the beginning. When you hit Enter, both Cell A1 and our chart title will change, because the chart’s title is “relative” to Cell A1. Double-click between Columns A and B to fit the new longer title if necessary.
Now it’s your turn to practice on your chart. Make the numbers match your actual housing budget. Change the title to the correct month and year. Sort largest to smallest or smallest to largest. Filter out any “0” amounts, so they don’t show up on your table.
Did you enjoy this lesson? Which is your favorite chart to use? Let us know what you think in the comment section below!
Up Next: Work Headers And Footers