Skip to content

Management Accounting · Spreadsheets

Presenting Data with Charts, Tables and Formatting in Spreadsheets

Updated 11 October 2026 · Fact-checked

Presenting data in a spreadsheet means formatting it clearly, sorting and filtering it to find what matters, summarising it with a pivot table, and choosing a chart that fits the message. To answer exam questions, identify the task, the data type and the audience, then pick the matching tool.

Understand Presenting Data with Charts, Tables and Formatting

A spreadsheet holds raw data in rows and columns. Raw data is hard to read. Presentation tools turn it into information that a manager can use to plan, control or decide.

Formatting makes figures readable. You apply number formats (currency, percentage, decimal places), column widths, headings, borders and consistent units. Formatting changes how a value looks, not the value stored in the cell. A cell showing 12.5 may hold 12.4837.

Sorting puts rows in an order, such as largest cost first or alphabetical by department. Filtering hides rows that do not meet a condition, such as only region = North. Filtering does not delete data. The hidden rows are still there and can be shown again. When you sort, you must select the whole data range so each row stays together.

A pivot table summarises a large list without writing formulas. You choose which field goes in rows, which in columns, and which in values. The values field is summed, counted or averaged. For example, a list of 5,000 sales transactions can become a table of total sales by product and by region. A pivot table does not update automatically when source data changes. You must refresh it.

Charts show patterns that tables hide. Match the chart to the message:

  • Line chart: change over time (trends).
  • Bar or column chart: compare categories or periods.
  • Pie chart: parts of one whole, with few categories.
  • Scatter chart: relationship between two variables.
  • Histogram: frequency distribution of continuous data.
  • Stacked bar: totals and their components.

Every chart needs a title, labelled axes with units, and a legend if there is more than one series.

Key formulas to remember

Chart choice: trend over time
Time series data → line chart
Time goes on the horizontal axis. Use for sales by month or cost trends.
Chart choice: comparison
Categories compared → bar or column chart
Best for comparing departments, products or periods side by side.
Chart choice: proportion
Parts of one whole → pie chart
Use only with a small number of categories. Parts must add to 100%.
Chart choice: relationship
Two variables → scatter chart
Used to spot correlation, for example advertising spend against sales.
Pivot table layout
Rows + Columns + Values (+ Filters)
Values are summarised by sum, count or average. Refresh after the source data changes.
Percentage of total
Part ÷ Total × 100
Used for pie charts and for pivot tables showing percentage of grand total.

How to solve Presenting Data with Charts, Tables and Formatting questions

Use this method for any objective test question on presenting data in a spreadsheet.

  1. 1Read the question and underline the task: format, sort, filter, summarise or chart.
  2. 2Identify the data type: time series, categories, parts of a whole, or two linked variables.
  3. 3Match the task to the tool. Summarise a long list: pivot table. Show a subset: filter. Rank or order: sort. Show a trend: line chart.
  4. 4Check the conditions in the question, such as the number of categories, the audience or the need for exact values.
  5. 5Eliminate options that change or delete data when the task only asks for display.
  6. 6For number entry, work out the figure from the data given, then check the units and the decimal places requested.
  7. 7Review the remaining option against the question wording before you confirm.

Quickest way: Task-to-tool matching

When to use it: Use for most multiple choice and multiple response questions where you must pick a tool or chart.

  1. Spot the keyword: trend, compare, proportion, relationship, summarise, hide, order.
  2. Link it at once: trend to line, compare to bar, proportion to pie, relationship to scatter, summarise to pivot table, hide to filter, order to sort.
  3. Cross out any option that deletes data or needs formulas when the question wants a quick summary.
  4. For multiple response, select exactly the stated number of answers.

Common mistakes in Presenting Data with Charts, Tables and Formatting

  • Using a pie chart for time series data or many categories

    Pie charts look familiar and students pick them for any share of something.

    Fix: Use a pie chart only for parts of one whole with few categories. Use a line chart for time and a bar chart for many categories.

  • Thinking filtering deletes rows

    Hidden rows seem to disappear from the sheet.

    Fix: Remember that a filter only hides rows. The data stays in the file and returns when the filter is cleared.

  • Sorting only one column

    Students select a single column and sort it, which separates values from their labels.

    Fix: Select the whole data range, or click inside the table, so every row moves together.

  • Expecting a pivot table to update by itself

    Students assume it is linked live like a cell formula.

    Fix: Refresh the pivot table after changing the source data. Also include new rows in the source range.

  • Believing formatting changes the value in the cell

    A rounded display looks like a rounded number.

    Fix: Formatting changes appearance only. The stored value keeps its full precision, so use a function such as ROUND if the value itself must change.

  • Leaving out chart titles, axis labels or units

    Students focus on the chart type and forget the details that make it readable.

    Fix: Check title, axis labels with units, and legend before you finish. Exams often test these as features of a good chart.

Worked examples

Example 1

A company has 12 months of sales data in dollars ($) and wants to show management how sales have changed through the year. Which chart is most suitable: pie chart, line chart, scatter chart or histogram?

Show the solution
  1. The task is to show change over time.
  2. The data is a time series: 12 monthly values.
  3. A line chart puts months on the horizontal axis and joins the points, which shows the trend.
  4. A pie chart shows parts of a whole, not change over time. A scatter chart shows a relationship between two variables. A histogram shows a frequency distribution.

Answer: Line chart.

Example 2

A pivot table shows total sales by region. The North region sales are $45,000, South $30,000, East $15,000 and West $10,000. A manager wants a pie chart of the pivot table. What percentage of total sales does the North region represent?

Show the solution
  1. Total sales = 45,000 + 30,000 + 15,000 + 10,000 = $100,000.
  2. North share = 45,000 ÷ 100,000 × 100.
  3. North share = 45%.

Answer: 45%

Exam tips

  • Read for the keyword that signals the tool: trend, compare, proportion, summarise or hide.
  • Remember the exact behaviour of each tool: filters hide, sorts reorder, pivot tables need refreshing, formatting does not change values.
  • In multiple response questions, select exactly the number of answers asked for. Extra selections lose the marks.
  • For number entry questions on percentages of a total, add the parts first, then divide, and give the answer in the format requested.
  • Do not spend long on one question. Flag it, move on and return if time allows.

Practice questions from Spreadsheets

Presenting Data with Charts, Tables and Formatting in other exams

The same ground in other exams, if you are preparing for more than one or want another angle on it.

Presenting Data with Charts, Tables and Formatting: frequently asked questions

Which chart should I use to present data?

Match the chart to the message. Use a line chart for trends over time, a bar chart to compare categories, a pie chart for parts of one whole, and a scatter chart for the relationship between two variables.

How do I create a pivot table in Excel step by step?

Click inside your data list, which should have headings and no blank rows. Choose Insert, then PivotTable, and confirm the range. Drag fields into Rows, Columns and Values, and set the values to sum, count or average. Refresh it when the source data changes.

How do I sort and filter data in Excel?

Click inside the data range and turn on Sort and Filter from the Data tab. To sort, choose a column and an order such as smallest to largest. To filter, use the drop-down arrow on a heading and tick the items you want to see. Sorting keeps whole rows together, and filtering only hides rows.

Does formatting change the numbers in a spreadsheet?

No. Formatting changes only how a number is displayed, such as decimal places or currency symbols. The cell still stores the full value unless you use a function to round it.