top of page
Search
  • Writer's pictureThomas Mouquot

Master Excel Dashboards: Building a Powerful Reporting Tool

Updated: May 10


Introduction


When starting over a decade ago in the advertising field, I quickly understood that the manipulation of data and the process of pulling insights from raw data was important. Learning how to use Microsoft Excel efficiently was necessary and my analytical skills helped me to make better decision across all of my job positions. In this step by step tutorial, we’re breaking down exactly how to create an Excel dashboard with no stress or headaches required.




Why should you use Excel as a Reporting Dashboard? An Excel dashboard is a one page (but not always necessary) that involves next to zero cost as opposed to the new fancy data visualisation dashboards from PowerBI, Tableau or Google Data Studio (which I absolutely love!) It is powerful, fairly easy to design and a great way to improve your Excel and data visualization skills. Because of its flexibility, you can design virtually any dashboard in Excel exactly the way you, or the users, imagined. And, best of all, you may want to implement it yourself or consider it a prototype and ask IT to implement it What should you know before building your Dashboard? Naming Conventions

Regardless of the project you’re working on, naming conventions or taxonomies are one the most important parts of your project and will determine how you’ll be able to manipulate the data. In digital advertising, we usually use files called “naming convention builder” or “URL builder” to ensure we remove any human mistake. Whether it’s a misspell or wrong order. This document is a good example on how it can be done for simple activities





Identify your Source Data

Always know where the data comes from and in what format. In one of my projects, the data was provided as PDF files in the French language. This completely changed the scope and most of our time was sucked up in manually culling the data. Here are the questions you should ask: – Who owns the data? – In what format will you get the data? – How frequently does the data update? Once you have a good idea of what you need to create, the next steps are to get your hands on the data and export it to Excel.



Import your Data

In most of my project, I ensure the least manual steps are involved and the answer is either Web Query URL or APIs. Web Query URL reports are no different to any typical report that you would manually download from within the platform’s interface, except for one critical detail: location. Rather than saving the data you are trying to access locally on your PC, the data is stored and automatically updated online, accessible through a unique URL that is generated upon initial set-up. The address of this URL is what Excel will use to locate the data that you want to report on. Usually, PPC bid management tools such as SA360 or Marin Software offer this option:





Clean and Manipulate the Data

When working with data within Excel, it’s important that each piece of information lives within its own cell. If your existing spreadsheet is a bit of a jumbled mess, take some time to clean it up and ensure that things are organized into their appropriate rows and columns. It’s also wise to briefly analyze your data and make sure that no glaring typos or errors jump out at you. Now is also an excellent time to search for any duplicate information that needs to be deleted, because each row of data needs to be unique in order to utilize the dashboard feature—otherwise you’ll be double counting. For instance, a lot of campaign names, placements, ad groups… might not have the most relevant description. Hence, you might want to call your non-brand PPC campaigns another name such as “Generic”: you’ll need to create new columns that will be used across chart & tables in your dashboard. In the example on the right hand-side is extra columns I have added to create consistency and based on the naming convention file



Choose your KPIs/Metrics

There are thousands of KPIs that can be viewed online and the size of your file can be pretty heavy if you don’t focus on the objective of your excel dashboard. In my previous experience when working on performance campaigns, ROAS was the business metric we focused on. Therefore, you’ll need to break this down on how ROAS is impacted: Revenue and Cost. For more information, you can look at Clicks, Impressions and/or Quality Score. Here is a list of metrics I’ve pulled for performance reports:




Tips:

  • Whenever you select the metrics, do not choose calculated metrics such as CTR, ROAS, Completion Rate, VTR and so on. If you make sum/average of calculated metric, it won’t show exact figures: this is called a Simpson Paradox

  • You are now able to see DBM cost in DCM by ticking Report Display & Video 360 cost to Campaign Manager checkbox


Ready to Build your Excel Dashboard? Once you have the data in Excel, you will know exactly what you can and can not use in your Excel dashboard. At this stage, it’s a good idea to circle back with your stakeholder with an outline of the Excel dashboard. As a best practice, I create a simple outline in PowerPoint along with additional notes. The purpose of this step is to make sure your stakeholder understands what kind of dashboard he/she can expect with the available data. Starting from scratch is not easy therefore I often google “excel dashboard example” or even better, access Google Data Studio Gallery: They are free template which would provide really good inspiration on how to layout the relevant information. I used to spend too much time wondering how data could be easy to read or be more visual. Once you have the outline worked out, it’s time to start creating the Excel dashboard. Below is a simple mock-up I recently worked on a client:




As a best practice, divide your Excel workbook into three parts:

  • Data – This could be one or more than one worksheets that contain the raw data from Facebook, DCM, SA360 and so on

  • Calculations – This is where you do all the calculations. You may have one or more than one sheet for calculations such as dynamic tables and data validation

  • Dashboard – This is the sheet that has the dashboard. In most of the cases, it is a single page view that shows analysis/insights backed by data. In this case, I often create a Master Dashboard including topline information and then, Deep-Dive dashboards per channel where the stakeholders are able to pull insights and update an optimisation log


Important functions for your Excel Dashboard


The formulas below helped me to create interactive tables and charts so here are the most importants to my knowledge:


  • SUM, COUNT, AVERAGE: SUM allows you to sum any number of columns or rows by selecting them or typing them in, for example, =SUM(A1:A8) would sum all values in between A1 and A8 and so on. COUNT counts the number of cells in an array that have a number value in them. This would be useful for maybe determining if someone has paid, or in other database situations. AVERAGE does exactly what it sounds like and take the average of the numbers you input

  • IF STATEMENTS: IF statements are super useful in a lot of situations, and this function allows you to output text if a case is valid, or false. For example, you could write =IF(A1>A2, “GOOD”, “BAD”), where A1>A2 is the case, “GOOD” is the output if true and “BAD is the output if false.

  • SUMIF, COUNTIF, AVERAGEIF: These functions are a combination of the SUM, COUNT, AVERAGE functions with the attachment to IF statements. All of these functions are structured the same way, being =FUNCTION(range, criteria, function range). So in SUM, you could input =SUM(A1:A15, “GOOD”, B1:B13). This would add B1 through B13 if the values of A1 through A15 all said GOOD. You may be starting to see how many of these formulas can be applied on top of each other to create some complex spreadsheets.

  • VLOOKUP: This function allows you to search for something in leftmost column of a spreadsheet and return it as a value. An example of how to use this would be as follows: =VLOOKUP(lookup value, the table being searched, index number, sorting identifier). The downside to this function is it requires the information being searched to be in the leftmost column, but don’t worry, we have a solution further down in this list! This function is a little more complicated than this article will allow for but check you INDEX MATCH and CONCATENATE

  • CONCATENATE: Concatenate is not only a fantastic word to say, but it is also a useful function if you need to combine data into one cell. Say for example you had a first and last name, in cells A1 and A2 respectively. You would type =CONCATENATE(A1,” “,B2), which would combine the names into one cell, with the ” ” adding a space in between

  • AND/OR: This is another logical function in Excel, and it will check if certain things are true or false. For example, =AND(A1=”GOOD”, B2>10) would output TRUE if A1 is GOOD and the value of B2 is greater than 10. You can have it check more values than two as well, simply add it on with another comma

  • INDEX + MATCH: This combination of functions allows you to work around VLOOKUP’s annoying limitations. By combining these functions like this, =INDEX(list of values, MATCH(what you want to lookup, lookup column, sorting identifier)), you can search a whole spreadsheet for values instead of being forced to only search the left-most column


These other functions helped improving to read data and visualise trends in the tables and charts:


  • CONDITIONAL FORMATTING: It is an incredibly useful tool that is built right into Excel. If you go Home –> Styles –> Conditional formatting, you can select many options that will give outputs if certain things are true. You can do a lot of this with the formulas mentioned before, but why not let Excel do the hard work

  • DROP-DOWN MENU: You can create the same type of control for your users in an Excel sheet, but the process isn’t intuitive. The option is in the Data Validation feature. Fortunately, once you know the feature exists, it’s easy to implement. You need only two things: a list and a data entry cell. The following sheet shows a simple drop-down list in an Excel sheet. Users click the drop-down arrow to display a list of items from A1:A4. If a user tries to enter something that isn’t in the list, Excel rejects the entry. To add this drop-down list to a sheet, do the following:

    1. Create the list in cells A1:A4. Similarly, you can enter the items in a single row, such as A1:D1.

    2. Select cell E3. (You can position the drop-down list in most any cell or even multiple cells.)

    3. Choose Validation from the Data menu.

    4. Choose List from the Allow option’s drop-down list

    5. Click the Source control and drag to highlight the cells A1:A4. Alternately, simply enter the reference (=$A$1:$A$4).

    6. Make sure the In-Cell Dropdown option is checked. If you uncheck this option, Excel still forces users to enter only list values (A1:A4), but it won’t present a drop-down list.

    7. Click OK


  • SLICER: You can insert slicers in Excel to quickly and easily filter pivot tables. However, using the report filter gives the exact same result so it’s purely for visualisation and manipulation. To insert a slicer, execute the following steps.

    1. Click any cell inside the pivot table.

    2. On the Analyze tab, in the Filter group, click Insert Slicer

    3. Check the information you want to be displayed and click OK


  • NAME RANGE: A named range is just a human-readable name for a range of cells in Excel. For example, if I name a range “Impressions”, I can use SUMIF to get the maximum value with a simple formula. The beauty of named ranges is that you can use meaningful names in your formulas without thinking about cell references. Once you have a named range, just use it just like a cell reference


Analysis of the Dynamic Elements


Once your dashboard is created, it’s time to make it interactive so every stakeholder is able to change the dashboard view. For instance, in a weekly report, you’d like to see the performance of the last 7 days vs last 14 days, last 14 vs last 28 or last month vs last 2 months so you can pull trends, orthodoxies, gaps, opportunities and so on. This is possible with the use of drop-down menu in your dashboard but also slicers. These components work directly with dynamic ranges setup in the calculation tab we’ve seen earlier.




Optimize Your Excel Dashboard for Better Performance


To ensure your Excel dashboard runs smoothly and efficiently, here are three key optimization techniques:

  1. Reduce File Size: Over time, as data accumulates, your workbook can become heavy and prone to crashes. To address this, consider hosting your data on Excel Online and having your formulas, pivot tables, and other functions reference this external file. Another approach is to remove unused data, such as historical records or irrelevant metrics.

  2. Improve Speed: Excel can slow down with large datasets, but there are several tips to enhance performance:

  • Avoid Volatile Functions: Functions like NOW, TODAY, INDIRECT, RAND, and OFFSET recalculate whenever any change occurs in the workbook.

  • Use Helper Columns: Utilize helper columns, an underutilized design construct in Excel, to streamline complex calculations and improve readability.

  • Minimize Array Formulas: Array formulas can consume significant processing power, so use them judiciously to optimize performance.

  • Exercise Caution with Conditional Formatting: While it provides visual cues, excessive use of conditional formatting can slow down your workbook. Use it selectively for effective data comparison.

  • Convert Unused Formulas to Values: If you have formulas that are not actively used, convert them to static values to improve workbook speed.

  • Manual Calculation Mode: Switching to manual calculation mode allows you to control when Excel recalculates, saving time by preventing unnecessary recalculations.

  1. Enhance Visualization: Optimize the readability of your data by avoiding unnecessary and overwhelming chart elements:

  • Remove non-essential elements like logos and excessive text.

  • Minimize the use of 3D objects and overly detailed visuals.

  • Limit color usage and focus on simplicity.

  • Consider using effective visualization techniques such as line charts, slicers, drop-down menus, and gauges for impactful presentation of insights.

Regularly review your dashboards every 6-12 months to ensure their relevance and maintain simplicity.


I invite you to share your favorite Excel formulas in the comments section. If you found this article helpful or have further inquiries, feel free to reach out to me for more information.


Thank you for reading, and I hope these optimization techniques prove beneficial for your Excel dashboard.

15 views0 comments

コメント


bottom of page