Charts and a one-page dashboard in Excel

A table of monthly figures holds every answer, but a reader has to work to find it. A good chart makes one point at a glance: revenue fell behind expenses in April, wages are half of all spending, net income is drifting down. This page covers the four decisions behind every chart on a financial dashboard: the chart type, the ranges, the title and the alt text. It works through an example and lists the mistakes that send the wrong message.

Start with the question, not the chart

Before you click anything, say in one sentence what the chart is for. Almost every chart on an accounting dashboard answers one of three kinds of question:

• How did something change over time? Net income month by month, cash at each month end, sales by quarter. This is a trend.
• How do two or more figures compare? Revenue against expenses each month, budget against actual for each account. This is a comparison.
• What share of the whole is each part? Expenses by category, revenue by product line. This is a composition.

The sentence also gives you the title, and most of the alt text. If you can't write the sentence, the chart isn't ready to be made yet.

The MO-220 objective this covers

These exercises are MO-220 practice for "Prepare and analyze financial statements", under presenting financial data, which covers dashboards and charts along with accessibility and usability. MO-220 tasks describe what a chart should show rather than naming the chart type, so choosing the type is part of the skill. Accounting Exercises is not affiliated with Microsoft or Certiport.

In the practice exercises you build each chart in a chart builder next to the sheet: you choose the type, type the category and series ranges, and enter the title and alt text. A preview draws the chart as you go. Your work is checked on what the chart shows (its type, the cells it reads, its title and whether it has alt text), never on how the picture looks. The same choices appear in Microsoft® Excel® workbooks under Insert Chart, Select Data and the chart's alt text setting.

Choosing the chart type

Two rules keep you out of trouble. First, a pie chart only makes sense when the slices add up to a meaningful whole and none are negative: six months of net income are not parts of anything, and a month with a loss can't be a slice. Second, keep pie charts to a handful of slices; with more than five or six, a bar chart sorted by size reads better.

Selecting the ranges

A chart needs two kinds of range:

• Category labels: the names along the axis or on the slices, such as the months in A2:A7 or the expense categories in F2:F6.
• Series values: the numbers to draw. Each series is one set of figures, usually one column, such as Revenue in B2:B7. A comparison has two or more series.

The heading above a column becomes the series name in the legend. If you select from the heading row down, Excel uses the heading as the name and charts the numbers below it, so B1:B7 and B2:B7 chart the same values. When two series sit next to each other you can select them as one block, B2:C7, or as two ranges separated by a comma.

The range must stop where the figures stop. Accounting sheets almost always end with a Total row, and that row is the most common reason a chart looks wrong.

Worked example: a two-chart dashboard

Copperleaf Studio's owner wants a one-page dashboard for January to June 2026. The sheet has the monthly figures in A1:D8 and the period's expenses by category in F1:G7:

The owner asks for two charts: one that compares revenue with expenses each month, and one that shows what share of total expenses each category makes up.

Chart 1: revenue against expenses

1. Purpose: a comparison in each month, so a clustered column chart.
2. Category labels: the months, A2:A7. Not A8: "Total" is not a month.
3. Series values: Revenue B2:B7 and Expenses C2:C7, entered as two ranges or as the block B2:C7. Net income is not part of the question, so column D stays out.
4. Title: "Revenue and expenses by month", as requested.
5. Alt text: "Column chart comparing revenue and expenses for each month from Jan to Jun 2026. Expenses were higher than revenue in Apr."

The chart now shows six pairs of columns. April is the one month where the expense column is taller, which is exactly the point the alt text states.

Chart 2: expenses by category

1. Purpose: parts of a whole, so a pie chart.
2. Category labels: F2:F6, the five category names.
3. Series values: G2:G6. Stop at row 6: row 7 is the Total.
4. Title: "Expenses by category".
5. Alt text: "Pie chart of expenses by category for Jan to Jun 2026. Wages is the largest share at 53% of $83,600."

Wages of 44,308 out of 83,600 is 53% [My calculation]. Now see what happens if the range runs on to G7. The Total of 83,600 becomes a sixth slice, the pie adds up to 167,200, the Total slice takes exactly half, and wages appear to be about 27%. Every slice is wrong, and nothing on the chart warns you. A quick check prevents it: the categories' slices should add up to the total in the sheet, not include it.

Also notice that the category table's total, 83,600, agrees with total expenses in C8. A dashboard that pulls from two tables should tie them together; if they disagree, fix the figures before you chart them.

Titles that say something

A chart title is the first thing a reader sees. "Chart 1" or the default series name tells them nothing. A good title names what is shown and, where it matters, the period: "Revenue and expenses by month". On a dashboard the period can sit in the page heading instead of every chart title. When a task gives you the title, use it word for word, because the dashboard is often laid out around it.

Alt text: the accessibility step

Alt text (alternative text) is a short written description attached to the chart. Screen readers read it aloud to people who can't see the picture, and it is shown when the image can't be displayed. Excel's accessibility checker flags charts without it, and MO-220 lists accessibility as part of presenting financial data.

Good alt text does two things in a sentence or two:

• Says what the chart is: the chart type, what is measured and the period. "Line chart of monthly net income from January to June 2026."
• Gives the main point: the thing a sighted reader would notice. "Net income was highest in February and fell to a loss in April."

Repeating the title is not enough, and neither is "chart" or "graph". You don't need to list every number either; the table is on the sheet for anyone who wants the detail. In Excel, right-click the chart and choose the alt text option, then type the description.

Laying out a one-page dashboard

• Put the most important chart first, top left, where people start reading.
• Keep related charts consistent. If revenue is blue in one chart, keep it blue in the next, and use the same months and order.
• Leave out decoration. 3-D effects, shadows and heavy gridlines make values harder to read.
• Keep it to one page. Two to four charts and a few headline figures are plenty. If it needs scrolling, it is a report, not a dashboard.
• Check every chart against the sheet, and keep the source figures on the workbook so the chart can be traced.

Common mistakes

• Including the Total row. The total is drawn as one more month or one more slice. In a column chart it towers over the real months; in a pie it swallows half the circle.
• A pie chart for figures over time. Months are a sequence, not parts of a whole. Use a line or column chart.
• A column chart for parts of a whole. It compares sizes, but the reader can't see each category's share of the total. Use a pie chart.
• Charting the wrong column. Revenue charted when the question was about net income. Read the heading above each series range.
• Missing a series. A comparison of revenue with expenses that shows only revenue compares nothing.
• No alt text, or alt text that only repeats the title.

The exercises recognize these mistakes and point you to the part of the chart that needs fixing.

Where charts fit with the rest of the course

Charts sit at the end of the reporting chain. The figures come from a trial balance and an income statement, and the comparisons often come from a budget vs actual sheet. If the numbers under a chart are wrong, a well-chosen chart only makes the wrong answer more convincing, so check the figures first and chart them second.