Microsoft Excel is an extremely capable tool for organizing and analyzing data, but in its raw form this data can be hard to read. If you want to make a presentation using your Excel data, you’re going to need graphs, and lots of them, but if you want to stay within Excel, the best way to do it is to create a dashboard for the most relevant data you want to highlight.
What is an Excel dashboard, you may ask? Well, it’s essentially a page to bring together your most relevant information in an easy-to-understand format. So here are some tips to make an Excel dashboard that looks great and delivers the information you need to present.
Related
How you can make interactive dashboards in Microsoft Excel (and why you should)
Transform your data into insights by harnessing the power of pivot tables to make interactive dashboards
4
Use PivotTables
As many as you need
First things first, you’re probably going to want to use PivotTables to get just the data you need. PivotTables let you take data from a regular table and filter it into a separate table that only contains the subsets of information you need within that table.
For example, in the image above, I want to compare just the benchmark scores from PCMark 10, so I created a PivotTable that only includes those benchmark scores, instead of working with all the benchmarks I compiled on the source table. I can then use this new table to graph the scores I care about.
What you’ll want to ensure you do is that when you create a new PivotTable, you create it in a new worksheet, so it’s separated from the main table. The cool thing about this is you can then clone the new worksheet as many times as you want to create more PivotTables with different data sets. It really saves you a lot of trouble.
PivotTables are found under the Insert tab, and duplicating a worksheet is as easy as holding Ctrl (or Option on macOS) and dragging the worksheet at the bottom of the Excel window.
3
Separate your graphs from your raw data
Your dashboard should be in a dedicated page
If you want to make an easy-to-read presentation, it’s best to keep your graphs separated from the tables. As you create more PivotTables and graphs based on them, you can copy those graphs and insert them in a new worksheet by themselves. That way, you can have all the pretty graphs in one place, while the source data stays hidden and doesn’t clutter up the screen.
Of course, that means first creating the graph in the same sheet as the table is in, and then copying it over. Since you can easily switch between worksheets, you can also dive into the raw data at any time to focus on specific points of interest.
2
Customize your graphs as needed
Excel has a ton of options
If you’re creating graphs to showcase different types of data, you’re going to want to make sure you create distinct graphs that look good and keep things interesting. For example, if you want a holistic view that represents the share of each product in your total sales, a pie chart may be the best way to demonstrate what your biggest strength is. But if you want to specifically compare the benchmark scores of different laptops, a bar chart is the best way to go about it. And if you’re trying to see trends in how values have changed over a certain period, a line graph is the best way to observe how the values have progressed, so you can highlight strengths or weaknesses in your strategy.
Of course, it’s also important that the dashboard is pleasant to look at, so you’ll also want to make sure you’re using a visual style that’s easy on the eyes. Using a unifying color scheme across the different graphs, including adequate legends, and using grid lines can all help make your data easier to understand and capture people’s attention better.
If you want to go a little wild, Excel also lets you use custom images for bar graphs, so for example, you could use art of a stack of cash to depict the revenue for each month in a bar chart. Probably not something you want to do in a professional environment, but if you want to have some fun, why not?
1
Use slicers to easily filter data
Dive into specifics more easily
Finally, another tool that can greatly improve your presentation quality is slicers. Slicers let you focus in on specific data in a given graph, so for instance, you can have a graph that includes the best-selling products overall, but you can use a slicer to hone in on products from a specific brand so you have a better idea of what your strengths are.
You can also create timelines, which let you filter data based on the time that data is from, so you can look more closely at specific periods. It’s a great way to get more detailed insight into your data.
You can make great presentations with Excel
As long as you keep these tips in mind, you can create great presentations in Excel that are both insightful and look good (I wouldn’t say all my graphs above look great, they’re just examples). These are tools not everyone knows about, but getting familiarized with them will definitely pay off if you work in Excel a lot. This is one of the most valuable tools in
Microsoft Office
, and it’s still very widely used, making this kind of knowledge very valuable in the job market.
#ways #design #beautiful #Excel #dashboards #presentations
source: https://www.xda-developers.com/ways-design-beautiful-excel-dashboards-presentations/
