Building a dashboard for reports: a practical guide

At the end of every month a manager or a donor asks, “How are things going?” If the answer is a spreadsheet dozens of sheets long, nobody will read it. A page that fits on one screen, with the key figures and a few clear charts, answers the question in a minute. That page is called a dashboard. In this guide we look at when a dashboard is worth building, how to start from the audience and the question, how to choose metrics, how to get your data into a clean table, and how to put together a simple dashboard step by step in Excel and Google Sheets using pivot tables, charts and slicers. After that come choosing a chart type and design rules, an introduction to Looker Studio and Power BI, common mistakes, a practice exercise and a checklist.

What a dashboard is and when you need one

A dashboard is a page that brings the important metrics together in one place, in a visual form. The name comes from a car’s instrument panel: a driver glances at the speed, fuel and temperature gauges and knows whether everything is fine. A report dashboard works the same way: people do not read it from top to bottom, they grasp the situation at a glance.

A dashboard differs from an ordinary table in three ways:

• Selected data. It does not hold everything, only the figures needed to make decisions.
• Visual clarity. Figures are shown through data visualisation: charts, colour and large labels.
• Easy updates. A good dashboard is linked to its source table: when new data arrives, everything refreshes in a few clicks.

You need a dashboard when:

• the same report is prepared regularly (every week, every month);
• several people look at the data and need to draw conclusions quickly;
• tracking change over time matters: is attendance falling, are enrolments rising?

For a one-off question or a very small amount of data, a dashboard is unnecessary: one chart or a few figures in the text will do.

Start with the audience and the question

The most common mistake is to open the software and start drawing charts straight away. The right order is the reverse: first decide who will look at the dashboard and which question it has to answer.

Write three questions down on paper:

1. Who will look at the dashboard? The centre’s director, the teachers, a donor organisation or mahalla activists each need a different level of detail. A director cares about the overall figures, a teacher about detailed data for their own group.
2. What decision will they make? For example: which group needs extra attention, which subject deserves a new group, on which days the library should stay open longer.
3. How often will they look at it? A daily dashboard should be simple and quick to read; a quarterly one can be more analytical.

An example (hypothetical). Dilshod Qodirov, director of the “Kelajak” training centre in Fergana, reviews the previous month at the start of each month. His main questions are “Which groups saw attendance drop?” and “Which subject are more people enrolling in?”. So his dashboard needs the attendance rate broken down by group and the number of enrolments by subject.

Write the question down in one sentence and check every element against it: if an element does not help answer it, remove it.

Choosing metrics (KPIs)

A metric is a specific figure you track. The most important ones are often called KPIs (key performance indicators). A good metric meets these requirements:

• It has a precise definition. Not just “attendance” but “the number of students who came to class, relative to all students in the group, over the month”. Write the definition at the bottom of the dashboard.
• You can influence it. If the figure gets worse, it should be clear what to do.
• It can be compared. A figure only becomes meaningful when compared with last month, a target or other groups.
• There are few of them. Three to six key metrics are enough for one dashboard.

Sample metrics for a training centre:

• number of enrolments in the month;
• average attendance rate;
• number of students who completed the course;
• number of groups with attendance below a set threshold.

For a mahalla library:

• number of books lent in the month;
• number of new readers;
• the most requested genres;
• number of books not returned on time.

Preparing the data: a clean table

A dashboard is only as reliable as its source table is clean. Pivot tables and charts work best with a “long” table: each row is one event and each column is one attribute.

The right structure for attendance (a hypothetical example; each row is one lesson for one student):

Date | Group | Subject | Student | Status
02.09.2026 | CL-1 | Computer literacy | Aziza Yusupova | Present
02.09.2026 | CL-1 | Computer literacy | Bekzod Nazarov | Absent

The wrong structure is a “wide” table with each date in its own column. It is hard to analyse with a pivot table. If your data looks like that, convert it to the long form on a new sheet.

Cleaning steps (spreadsheet basics are covered in detail in the Excel basics article):

1. One header row. Remove merged cells, empty rows and any subtotal rows inside the table.
2. Consistent spelling. “CL-1”, “cl1” and “CL 1” are three different groups as far as the software is concerned. Create a drop-down list with Data → Data Validation (in Google Sheets, Data → Data validation) so that staff can only pick from ready-made values.
3. Extra spaces. In Excel, use the TRIM function; in Google Sheets, Data → Data cleanup → Trim whitespace.
4. Duplicates. In Excel, Data → Remove Duplicates; in Google Sheets, Data → Data cleanup → Remove duplicates. Save a copy before deleting anything.
5. Dates. Values in the date column must be real dates, not text. Otherwise grouping by month will not work.
6. Table format. In Excel, select a cell inside the data and click Home → Format as Table (or press Ctrl + T). New rows are then added to the table automatically, and the pivot table picks them up when refreshed.

A simple dashboard in Excel and Google Sheets, step by step

It helps to split the work across three sheets: “Data” (the source table), “Calculations” (the pivot tables) and “Dashboard” (metrics and charts only). That way, the person entering data cannot accidentally break the dashboard.

1. Creating a pivot table

A pivot table groups and counts thousands of rows in a few seconds, without a single formula.

In Excel:

1. Select any cell inside the source table.
2. Click Insert → PivotTable and choose New Worksheet as the location.
3. The PivotTable Fields pane opens on the right. Drag the “Group” field to Rows, “Status” to Columns and “Student” to Values. The calculation type in Values should be Count.
4. You now see the number of “Present” and “Absent” entries for each group.

In Google Sheets, choose Insert → Pivot table. The Pivot table editor pane has the same sections: Rows, Columns, Values and Filters.

To get the attendance rate in Excel, click the field in Values and choose Value Field Settings → Show Values As → % of Row Total. In Google Sheets, pick ”% of row” from the “Show as” list in the Values section. The “Present” column now shows each group’s attendance rate.

Create a separate pivot table for enrolments: Rows set to “Subject”, Values set to the number of applications (Count). If you put the date field in Rows, Excel often groups it by month automatically; if not, right-click a date and choose Group → Months.

2. Adding a chart

In Excel, select a cell inside the pivot table and click PivotTable Analyze → PivotChart. This chart is linked to the pivot table: when the filter changes, so does the chart. In Google Sheets, select the pivot table output, click Insert → Chart and pick the type in the Chart editor on the right.

Give every chart a title that asks a question or states a conclusion: not “Chart 1” but “In September, attendance was lowest in group CL-2”. Remove the clutter: background gridlines, 3D effects, an unnecessary legend.

3. Key figure cards

Put three or four large figures at the top of the dashboard: “Enrolments”, “Average attendance”, “Completed”. On the “Dashboard” sheet, set aside cells with a large font for them and pull in the values with formulas from the pivot table or the source table. For example, the COUNTIFS function counts against several conditions: the number of rows for the selected month with the status “Present”. Add a small caption under each figure: “last month: 82 per cent” (a hypothetical figure).

4. Filters and slicers

A slicer is a panel of buttons on the dashboard. Clicking them filters the data by subject, group or month, and the linked pivot tables and charts change instantly.

In Excel:

1. Select a cell inside the pivot table.
2. Click PivotTable Analyze → Insert Slicer and tick the “Subject” field.
3. To let one slicer control several pivot tables, right-click it, choose Report Connections and tick the tables you need.
4. For dates, use PivotTable Analyze → Insert Timeline: a scale you can slide month by month.

In Google Sheets, choose Data → Add a slicer, then set the data range and the column to filter on. The slicer affects the pivot tables and charts built on that data.

5. Refreshing and assembling

After adding new rows to the source table, click Data → Refresh All in Excel and every pivot table and chart is updated. In Google Sheets, pivot tables usually update automatically. Then arrange the elements on the “Dashboard” sheet: the title and reporting period at the top, the figure cards below them, then the charts, with the slicers to one side. Turn off the gridlines you do not need with View → Gridlines (in Google Sheets, View → Show → Gridlines).

Chart types and design rules

Choosing the right chart type

Pick the chart type to suit the question:

• Bar or column chart. Comparing categories: which group has the highest attendance, which genre was lent most often. If category names are long, horizontal bars are easier to read. Sort the bars from largest to smallest.
• Line chart. Change over time: enrolments by month, library visits by week.
• Pie chart. Parts of a whole, and only with two to four parts. The eye struggles to compare a pie with many slices; use a bar chart instead.
• Table with conditional formatting. When exact figures matter, a colour-coded table (heatmap style) can be more useful than a chart. For example, attendance by group and by week.

Avoid 3D charts, complicated dual-axis graphs and decorative “speedometer” gauges: they take up space but say little.

Design rules

• Few colours. One main colour and one accent colour are enough. Use the accent only for what needs attention, such as a group with low attendance.
• Consistent colour meaning. If “Computer literacy” is blue in one chart, keep it blue in the others.
• Titles. Show the dashboard’s name, the reporting period and when the data was last updated.
• Order. Readers usually start in the top left corner. Put the most important figures there and the details further down.
• Consistent size and alignment. Charts should be the same width and aligned to a grid.
• Bar axis from zero. If a bar chart’s axis does not start at zero, a small difference looks large and misleads the reader.

Remember that some people cannot tell certain colours apart: do not rely on a red-green contrast alone; add labels or symbols as well.

An introduction to Looker Studio and Power BI

Excel and Google Sheets are enough for small and medium-sized reports. When the data grows or many people need to view the dashboard online, dedicated tools are more convenient.

Looker Studio (formerly called Google Data Studio) is a Google tool that runs in the browser; you sign in with a Google account. It connects directly to a Google Sheets spreadsheet: when the source table is updated, so is the report. You place charts, tables, figure cards and filters on the page with the mouse. A finished report can be shared via a link, just like a Google Docs document. For a small team already working in Google Sheets, it is the natural next step.

Power BI is Microsoft’s business analytics tool. Power BI Desktop is installed on a Windows computer; it connects to Excel files, CSV files and many other sources, includes the Power Query editor for cleaning data and produces interactive reports. The options for sharing reports online depend on which Microsoft services your organisation uses, so check this with whoever is responsible for IT.

Which should you choose? If your data is in Google Sheets and your team uses Google services, start with Looker Studio. If your organisation works in a Microsoft environment and has lots of Excel files, Power BI makes more sense. The rules stay the same either way: a question, a few metrics, a clean table, a simple design. Learning these tools in more depth is a natural continuation of data analysis skills.

Common mistakes

• A dashboard without a question. Charts drawn from all the available data. It looks nice but explains nothing.
• Too many metrics. Dozens of figures on one page, and the reader cannot find what matters most.
• A figure without comparison. “Attendance 78 per cent” (hypothetical): is that good or bad? Compare it with the previous period or a target.
• A messy source. If one group is written three different ways, the pivot table shows three groups and the result is wrong.
• Forgetting to refresh. If you do not click Refresh All in Excel, the dashboard shows old data. A “last updated” note next to the date helps you remember.
• Leaving personal data exposed. A shared dashboard should contain no names, phone numbers or addresses.

Practice exercise and checklist

Exercise: build a monthly dashboard for a hypothetical mahalla library. Every month, the librarian Mohira Ergasheva reports to the mahalla committee on book lending. All the data is made up.

1. On the “Data” sheet, write the headers: Date, Book title, Genre, Age group (children, teenagers, adults), Returned (Yes/No).
2. Enter at least 40 rows of made-up data covering two months. Create drop-down lists for genre and age group with Data Validation.
3. Turn the data into a table with Format as Table (Ctrl + T).
4. Write down the question: “Which genre and which age group use the library most, and is the number of unreturned books growing?”
5. Create three pivot tables: books lent by genre, books lent by age group, and books lent and not returned by month.
6. Build a sorted horizontal bar chart for genres and a column or line chart for the months.
7. On the “Dashboard” sheet, make three figure cards: total books lent, the most active age group, unreturned books.
8. Add a slicer on the “Age group” field and connect it to all the pivot tables.
9. Arrange the elements, turn off the gridlines, and add a title and the reporting period.
10. Add five new rows to the source, refresh and check that everything has changed.

Show the finished dashboard to a colleague for 30 seconds: if they cannot answer the question, remove whatever is unnecessary. If you need to present the dashboard at a meeting, the tips on preparing a presentation will also come in handy.

Checklist before sharing

• The dashboard answers one clear question, and it is clear who it is for.
• There are three to six key metrics, each with a written definition.
• Every figure is compared with something.
• The source table is clean: one header row, consistent entries, no duplicates.
• Chart types suit the question, titles state a conclusion, bar axes start at zero.
• There are few colours, and their meaning is consistent.
• The reporting period and the last update date are shown.
• There is no personal data, or it is hidden.

A dashboard is not complicated software but the result of orderly thinking: a clear question, a few well-chosen figures and a clean table. If you would like to develop this skill further, our data analytics programme focuses on exactly this.

Take a look at our association’s other free training programmes too. When you are ready, submit an application and our specialists will get in touch.