How to make Excel data visualization board, template and layout ideas

Key points of this article

First write down three to five questions that readers need to answer, and then let each question correspond to a number and a picture. If there are too many indicators, split it into two boards.
The recommendations on the Kanban board are arranged from top to bottom according to "key figures, trends, split details". Readers should read the conclusion first, and then decide whether to ask further questions.
The original data is placed in an independent worksheet, the summary table is calculated using formulas, and the dashboard only references the summary results. The chart can change when the data is updated.
The title of each picture should be written as a conclusion sentence, such as "East China has been higher than other regions for three consecutive months", which can help readers make quick judgments better than "Sales Chart".
Before sending it to others, set the Kanban area as the print area and check whether it can be displayed completely on one page to avoid the chart being cut off by page breaks.

Many people are already very proficient in making Excel charts. What really gets stuck is after putting several pictures into the same worksheet: which picture to put on top, where to put the key numbers, how to align the pictures, and which ones should be kept and which ones should be deleted. You can find the method of making a single picture by searching online, but to create an Excel visual dashboard composed of multiple pictures, you need to think about the layout first before you start.

This article is written for those who want to make weekly reports, monthly reports or project schedules, especially those who want to hand the data to management or other departments for reading. After reading, you can get a set of ideas for judging what content should be placed on the Kanban board, a partition layout that can be directly applied, and the order of operations from raw data to Kanban board. These ideas apply whether you download a ready-made Excel visualization data template or start from a blank sheet.

How to build a visual dashboard in Excel? One sentence conclusion

Conclusion in one sentence: First determine three to five questions that readers want to answer, let each question correspond to a number and a picture, and then layout it from top to bottom according to "overview, trend, split".

your situation	Recommended practices	reason
Monthly report for management	Place 4 key number cards on the top and 2 trend charts in the middle	Readers should read the conclusion first and then the change process.
To compare multiple regions or products	Place a ranking bar chart in the middle with a detailed table next to it	The ranking is visible at a glance, and the details are left for those who need to ask.
Data updated weekly	The original data is placed in an independent worksheet, and the Kanban board only refers to the summary table	No need to modify charts one by one when updating data
Need to print or send PDF to others	The billboard is controlled to be one screen wide and arranged horizontally according to A4	Avoid pagination cutting off charts
More than six indicators	Split into two Kanban boards, or add a filter drop-down box	There are too many indicators on the same screen, making it difficult for readers to find the key points.

What parts does the Kanban board consist of?

Key figure area

This area contains several numbers that should be seen first, such as current period sales, month-on-month changes, goal achievement rate, and the number of abnormal items. The numbers should be calculated from the summary table using formulas, and only quoted on the bulletin board, and the font size should be enlarged so that people can read it at a glance. You can leave a line of small words next to each number to explain the caliber, such as "tax included, returns not included."

Trend and split area

Use a line chart for trends, and a column or bar chart to compare different categories. Pie charts are only suitable for scenarios where there are few categories and you only want to see the proportion. If there are more than five categories, it is easy to see clearly. It is recommended that the title of each picture be written as a conclusion sentence, such as "Eastern China is higher than other regions for three consecutive months", so that readers no longer have to guess what this picture wants to illustrate.

Three misunderstandings that are easy to avoid

The more pictures the better. With each additional picture, the reader has to judge the cost one more time. The value of the Kanban board lies in screening, not in stacking.
Kanban is just a big table. The Kanban board is a summary view, and the details should be placed in another worksheet and checked again when needed.
Just copy and paste the chart. If the chart is just a manually pasted picture or static value, it will not change after the data is updated. The chart should reference the data area and convert the data area to a table, so that new rows will be automatically included in the chart range.

📊 Core comparison

Different layouts have their own applicable scenarios. You can refer to the table below before choosing.

Layout	Suitable for the scene	Things to note
Single screen overview	Monthly report, management report	When there are too many indicators, it will be crowded. It needs to be controlled within six.
Upper and lower partition type	A sequential and logical analysis, such as "results, causes, countermeasures"	When you need to compare left and right, your eyes will jump back and forth.
Filter on the left, chart on the right	Switch to view by region or product	It is necessary to set up drop-down menus and formulas, and the threshold for beginners is high
Multiple worksheet pagination	The amount of data is large, and different roles have different concerns.	It is impossible to see the whole situation at a glance, so it is suitable as a supplement to the overview board.

How to use it in practice

When building a Kanban board in Excel, the core work is done in Excel itself. OmniDoc can help in two aspects: first, translating English tables into Chinese, and second, exporting tables in PDF reports as Excel data. Just follow the steps below:

Create three worksheets in the workbook: original data, calculation summary, and kanban.
Use formulas to calculate key figures and grouping results in the "Calculation Summary" table. The Kanban board only refers to these cells.
Convert the original data area to a table. The data source of the chart points to the table area. Then the newly added rows will automatically enter the chart.
Arrange the "kanban" table according to the previous three-stage layout: number card at the top, trend chart in the middle, and split details at the bottom.
Write a conclusion-style title for each picture, delete unnecessary grid lines and legends, and keep two to three colors.
If the table is in English and needs to be shown to colleagues who do not understand English, you can upload the .xlsx file on omnidoc.top, choose bilingual comparison or translation only, and check the numbers, formula references, and chart titles item by item after downloading. Machine translation results require manual review, especially professional terminology.
If the report is only in PDF version, you can use the PDF editor on the desktop client to export the table to Excel, then check whether the exported numbers are consistent with the original text, and then connect to the dashboard after confirming that they are correct.
Before sending, set the billboard area as the print area and use print preview to check whether it can be fully displayed on one page.

🎯 Which method is suitable for you?

If you update the data once a week and send it to multiple people, it is preferred to choose the structure of "the original data is separated into tables, and the dashboard only references the summary table", and use a single-screen overview layout. If you want to make a one-time analysis report, you can use the upper and lower partition type to display the analysis process in a logical sequence. If your readers need to switch regions or products by themselves, then consider adding a filter drop-down box, but first make sure someone on the team can maintain the formula.

❓ Frequently Asked Questions (FAQ)

1. What is the difference between a Kanban board and an ordinary chart?

Ordinary charts answer one question, while Kanban boards put the answers to multiple questions on the same page and allow readers to complete their judgments within one screen. Kanban boards typically contain key figures, trend charts, and breakdowns rather than just a graph.

2. Can you make an Excel visual dashboard without any programming knowledge?

Can. Kanban mainly relies on basic formulas (such as sums and averages), table functions and charting tools, and does not require writing code. The key is to first define the indicators clearly and then build them step by step.

3. How to change the downloaded Excel visualization data template to your own data?

First paste the data into the original data table of the template, keeping the column names consistent. Then check whether the formula reference range of the summary table covers the new data and whether the data source of the chart points to the correct area. Finally, check a few key numbers and make sure they are correct before using.

4. Can the table in the scanned PDF be directly made into a billboard?

Text and numbers in scanned PDFs may not be directly selectable. You can use the OCR function to identify it first, or export it to Excel and then organize it. No matter which method is used, it must be checked item by item with the original text to avoid recognition errors being brought to the Kanban board.

5. Can the translated tables, formulas and charts still be used?

When OmniDoc translates a table, it retains the table structure and formulas, but the translation changes the text content. Once you open the results, you should check that formula references are correct, chart titles and legends are accurate, and key figures should be checked.

📝 Summary

The key to Excel's visual dashboard is not the chart style, but to first figure out what questions the readers want to answer, and then use the structure of "key numbers, trends, and split details" to organize the content. The original data is separated from the summary table, and the chart refers to the table area, so that the dashboard can still be used after the data is updated. According to the layout ideas and operation sequence of this article, you can use the same structure to build your own Excel visual Kanban template.

Open OmniDoc and work with your spreadsheet file