How to Automate Excel Dashboard Quickly

We all wonder sometimes and think how we can automate / reduce our day to day repeating efforts quickly. Whether it is a corporate, IT industry, Customer analytics or Manufacturing industry we all need to think of automating our work as much possible. These challenges may come across when you need to think smartly and automation becomes complicated.

My Name is Amit Dhiman and I have been working as a Data Architect in a reputed software consulting organization in Gurgaon, India.

I am demonstrating an automation which will help you automate your excel / dashboards quickly.

Ms-Excel is a wonderful tool when we think of automation. However, 80% work is done by excel and rest you need to set your own requirements in variables and parameters. You can record the macro in exact steps you want to perform for automation and then you can set the variables/parameters in the code itself to make your automation dynamic in nature.

Let me discuss a case with you that I am going to automate step by steps.

Problem Statement:

ABC Company wants to show its customer profitability analysis on a dashboard. Company receives this data in 5 different csv files from it’s data center. Data analyst creates the dashboard everyday using these files and it takes almost 15 to 20 minutes to create the dashboard.

Final Output Dashboard:

I am going to automate the dashboard using macro/VBA. To automate this dashboard we need the following items ready before proceed:

1. Five CSV files
2. One Dashboard template

How the files will look like and so the template:

Five CSV Files:

CostOfSales.csv

CustomerActivity.csv

OtherCosts.csv

ProfitabilityAnalysis.csv

SummaryMetrics.csv

Dashboard Template:

Solution Approach:

Approach to automate this dashboard will be very easy for learners. I am gonna record (macro) the steps to generate the VBA code automatically and then I will update the code with some variable values so that code can work dynamically.

So, Let’s start:

Step 1: Create a folder and keep all 5 cvs files along with dashboard template

Step 2: Open excel template

Step 3: Record a Macro ( Goto “View -> Macros -> Record Macro”)
Now every steps will be recorded and you will get the code. 

Step 4: Insert the new worksheet as follows:

• To quickly insert a new worksheet at the end of the existing worksheets, click the Insert Worksheet tab at the bottom of the screen.
• To insert a new worksheet in front of an existing worksheet, select that worksheet and then, on the Home tab, in the Cells group, click Insert, and then click Insert Sheet.

Step 5: Import csv file associated with first section (CustomerActivity.csv) in the new sheet

• Goto “Data ->From Text -> (“Select the file”) -> Delimited -> Comma -> Finish”
• Select whole data with (Ctrl + Shift ) -> Right Key -> Down Key. This will select the whole data automatically
• Copy this data
• Go back to “Customer Profitability” sheet and find the text “Customer Activity”
• Press the down key
• Insert the using “Insert the copied cells” by (cells down)
• Delete the new sheet we just created for copying the data

Step 6: We need to fill “Summary Metrics” data now. We need to be a bit carefully here as we not need to insert the cells with the copied data but need to paste instead. We will have extra rows with the formula set for chart so that multiple new rows can be adjusted.

• Repeat the step 4
• Repeat the step 5 but replace the step “Insert the using “Insert the copied cells” by (cells down)” with “paste data as formula“

Step 7: Stop the Macro Recording

Step 8: Update the code so that file can be imported automatically.

Following is the recorded code for first file. We will update this code so that it can be managed dynamically.

' Creating new worksheet and inserting the data there
Sheets.Add After:=ActiveSheet
With ActiveSheet.QueryTables.Add(Connection:= _
     "TEXT;C:\ExcelAutomation\CustomerActivity.csv" _
     , Destination:=Range("$A$1"))
     .CommandType = 0
     .Name = "CustomerActivity"
     .FieldNames = True
     .RowNumbers = False
     .FillAdjacentFormulas = False
     .PreserveFormatting = True
     .RefreshOnFileOpen = False
     .RefreshStyle = xlInsertDeleteCells
     .SavePassword = False
     .SaveData = True
     .AdjustColumnWidth = True
     .RefreshPeriod = 0
     .TextFilePromptOnRefresh = False
     .TextFilePlatform = 1252
     .TextFileStartRow = 1
     .TextFileParseType = xlDelimited
     .TextFileTextQualifier = xlTextQualifierDoubleQuote
     .TextFileConsecutiveDelimiter = False
     .TextFileTabDelimiter = False
     .TextFileSemicolonDelimiter = False
     .TextFileCommaDelimiter = True
     .TextFileSpaceDelimiter = False
     .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
     .TextFileTrailingMinusNumbers = True
     .Refresh BackgroundQuery:=False
End With
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Customer Profitability").Select

' Inserting the data
Selection.Insert Shift:=xlDown
Sheets("Sheet41").Select
Application.CutCopyMode = False

' Deleting the worksheet
ActiveWindow.SelectedSheets.Delete

According to the steps, we will find “Customer Activity” text so that data can be pasted in the first section.

    'Below code finds the "Customer Activity" text so that data can be placed in the same section
 
    Cells.Find(What:="Customer Activity:", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate

    'Following code line will move the cursor point to the next row from the searched position.
    ActiveCell.Offset(1, 0).Activate
    
    Sheets.Add After:=ActiveSheet

    sheetName = ActiveSheet.Name
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\ExcelAutomation\CustomerActivity.csv" _
        , Destination:=Range("$A$1"))
        '.CommandType = 0
        .Name = "CustomerActivity"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Customer Profitability").Select
    Selection.Insert Shift:=xlDown
    Range("B9").Select
    Selection.End(xlDown).Select
    'This will hide the confirmation to delete the sheet
    Application.DisplayAlerts = False
    Sheets(sheetName).Delete

Now add the same code block for other files too. Just replace the file name and search text in above code as I have done in following code:

  '------ Profitability Analysis:
    Range("B9").Select
    Cells.Find(What:="Profitability Analysis:", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Activate
    
    Sheets.Add After:=ActiveSheet
    sheetName = ActiveSheet.Name
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\ExcelAutomation\ProfitabilityAnalysis.csv" _
        , Destination:=Range("$A$1"))
        '.CommandType = 0
        .Name = "ProfitabilityAnalysis"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Customer Profitability").Select
    Selection.Insert Shift:=xlDown
    Application.DisplayAlerts = False
    Sheets(sheetName).Delete
    
    ' --- Cost of sales csv file:
    Range("B9").Select
    Cells.Find(What:="Cost of sales:", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Activate
    
    Sheets.Add After:=ActiveSheet
    sheetName = ActiveSheet.Name
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\ExcelAutomation\CostOfSales.csv" _
        , Destination:=Range("$A$1"))
        '.CommandType = 0
        .Name = "CostOfSales"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Customer Profitability").Select
    Selection.Insert Shift:=xlDown
    Application.DisplayAlerts = False
    Sheets(sheetName).Delete
    
    ' ----- Other costs csv file
    Range("B9").Select
    Cells.Find(What:="Other costs:", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Activate
    
    Sheets.Add After:=ActiveSheet
    sheetName = ActiveSheet.Name
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\ExcelAutomation\OtherCosts.csv" _
        , Destination:=Range("$A$1"))
        '.CommandType = 0
        .Name = "OtherCosts"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Customer Profitability").Select
    Selection.Insert Shift:=xlDown
    Application.DisplayAlerts = False
    Sheets(sheetName).Delete
    
    ' ----- Summary Metrics csv file
    Range("B9").Select
    Cells.Find(What:="Summary Metrics:", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Activate
    
    Sheets.Add After:=ActiveSheet
    sheetName = ActiveSheet.Name
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\ExcelAutomation\SummaryMetrics.csv" _
        , Destination:=Range("$A$1"))
        '.CommandType = 0
        .Name = "SummaryMetrics"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Customer Profitability").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    Sheets(sheetName).Delete

Paste the whole above code and change the “file location” according to your csv files location, and done. Now run code and you will get the generated dashboard.

Last code helps update the chart adjacent to the sections. Once “Summary Metrics” section is updated with the data, chart automatically updates the dimensions.

So, good luck for your automation. You can get the sample demo files from here.

In my next post, I will automate this excel further without opening excel application so that we get the dashboard generated automatically.