Excel 2013 Dashboard Design will show you how to turn your organization’s data into a powerful and interactive dashboard. By learning these easy-to-implement, advanced techniques, you can impress and empower the decision-makers in your organization, making you an irreplaceable asset.
Excel 2013 Dashboard Design begins by creating a vision for the dashboard with the help of stakeholders, and then moves on to gathering the data and building each element of the dashboard. The course ends with comparing distribution strategies appropriate for every size and type of organization.
The first section, Outputs and Inputs, covers the strategic aspects of planning your finished product and bringing in all of the necessary data from your database or Excel workbook. We then explore PowerPivot, a powerful bridge between your raw data and Excel. PivotTables will help us slice our data in a flexible manner, and Charts give us at-a-glance comprehension of important metrics and trends. Stylizing and Interactivity enhance the user experience and present the information in a manageable format. Publication can be problematic without proper planning, but we’ll cover the most effective ways to distribute your dashboard to any type or size of audience.
Excel 2013 Dashboard Design will guide you through your entire project, start to finish, covering every part of the process in depth. Since you’ll not only learn how to do each task, but also why it would be beneficial for you, you’ll be able to easily translate your learning into your own dashboard design project.
Tony Kau is a Tableau Desktop 8 Qualified Associate, and he is putting its power to use in his role as a reporting analyst for a Fortune 500 company. His analytical background spans a decade, during which he has used a variety of business intelligence software, though none were better than Tableau. His passion is efficiently leveraging data to inform strategic business decisions.
He holds a degree in Business Administration from the University of Oregon, and his background includes web design, programming, and financial analysis.
Discover available features and limitations in Excel early in the planning process to create the best dashboard for your audience.
Single-dimension dashboards might work in a pinch, but the best dashboards put the control in the hands of the end user.
Your data is stored in Access, but you need to pull it into Excel for your dashboard. Create this connection in Excel.
Your data is stored in SQL Server, but you need to pull it into Excel for your dashboard. Create this connection in Excel.
Your data is stored in an Excel Worksheet, but you need to aggregate it in Excel for your dashboard. Create a PivotTable in Excel.
Your data is stored in Access, but you have a lot of data. To help us utilize it in Excel, we can set it up using PowerPivot.
Part of your data might be stored in the Excel Workbook, so we need to link it to our data model using PowerPivot.
You want to use data from multiple sources or tables in your dashboard and need to tie them together. Relationships and Hierarchy can link these together.
All your data is imported, but you need to perform additional calculations or functions before you can use it. We'll learn how to add calculated fields in PowerPivot.
You want to be able to see at a glance whether you are meeting certain business goals, so we'll create a KPI to measure and display this progress.
You have your data imported and preprocessed in PowerPivot, but now you need to display it in Excel. We'll create PivotTables to do this for us.
You are importing your PowerPivot data into PivotTables, but you need to use an advanced chart type to display your data. We'll create an intermediary table that will give us a higher level of control of the chart.
You have just created a PivotTable, but to use it effectively, you need to make some adjustments. We'll format, sort, and limit it to meet our desired output.
Your PivotTable is displaying the correct data, but is using the default style and options. We'll customize the table to match our desired look and feel.
You aren't using PowerPivot to manage your data model, but you still want to implement calculated fields and KPIs. We'll use PivotTable options and conditional formatting as a workaround.
After setting up your PivotTable, now you want to create a PivotChart. We'll create the chart and discuss key chart features.
PivotCharts almost always have to be customized to be useful. We'll cover the steps to modify the main chart elements.
Charts can be more useful when they are combined, for example, a bar chart and a line chart to easily compare multiple metrics. We will walkthrough how to create these combo charts.
Charts almost always have to be customized to be useful. We'll cover the steps to modify the main chart elements.
A written sentence can sometimes be necessary to make a strong point. We will create a dynamic sentence using static text and a formatted number from our data.
Arranging your dashboard effectively is a very important step. We'll cover techniques and tips to present our data and save you frustration in the process.
PivotTables can expand and contract with the data behind them, which can be useful, but also impractical. When you need complete control, reference the PivotTable in a range.
If you would like to highlight any range based on its value (or the value of a related cell), you can implement conditional formatting to change colors or fonts, or add icons or color bars.
You would like to show an underlying trend line without taking up much space. Sparklines allow you to create a basic chart in a single cell.
Sparkline source ranges remain static unlike normal and PivotCharts in Excel. In order to reflect the selected time period in our dashboard, we need to use named ranges and VBA code to make them dynamic.
To make an interactive experience with a dashboard based on PivotTables, we can use slicers to let the end user focus as wide or as narrow as they would like, at the click of a button.
If the default slicer settings and color scheme is not acceptable for the dashboard, we can customize them.
If we deal with a range of dates, we can easily extend filtering capabilities to the end user by including a timeline slicer.
If you need to quickly assemble a functional, interactive dashboard tied to your Excel data model or an external connection, Power View allows you to create a presentation-ready view in very few clicks.
If your data needs to be visualized in a hierarchy (for example, Category, Subcategory, and Item), consider using a drill down to provide context and details for your data.
In order to focus on just the relevant data, you can implement filters on chart elements or the entire view.
If you would like to see your data one slice at a time, you can use tiles to create a visual interface for selecting each slice.
If you would like to separate your data into slices such as tiles, but view all tiles at the same time, Multiples allows you to show all the slices together.
Your dashboard is complete, but we need to lock down the information that we don't want the users to focus on. We can do this by preventing the modification of cells and hiding sheets.
Your users may use different versions of Excel, which could cause serious functionality issues if they try to use the workbook on their own computer. Use SharePoint 2013 to allow users to navigate your dashboard reliably from their web browser.
If you need to share your dashboard on a small scale, or your dashboard does not use PowerPivot, you can conveniently host it on SkyDrive.
If your organization does not have a SharePoint 2013 server, but you need to distribute to a group who may not be using Excel 2013, use Office 365 SharePoint Online.
If you need to distribute a paper copy or PDF of your dashboard, as is sometimes necessary, utilize print area, scaling, and margins for the most optimal presentation.
Packt has been committed to developer learning since 2004. A lot has changed in software since then - but Packt has remained responsive to these changes, continuing to look forward at the trends and tools defining the way we work and live. And how to put them to work.
With an extensive library of content - more than 4000 books and video courses -Packt's mission is to help developers stay relevant in a rapidly changing world. From new web frameworks and programming languages, to cutting edge data analytics, and DevOps, Packt takes software professionals in every field to what's important to them now.
From skills that will help you to develop and future proof your career to immediate solutions to every day tech challenges, Packt is a go-to resource to make you a better, smarter developer.
Packt Udemy courses continue this tradition, bringing you comprehensive yet concise video courses straight from the experts.