Real-Time Dashboard in Excel for IT
In this article, you will use Excel to create a dashboard. We’re going to go over how to link and add different graphical widgets to leverage the features of Excel to create the dashboard with real-time features.
This is a video from our YouTube channel you can use to help accomplish this step:
Step 1: To begin, click on the dropdown chart under ‘Select the database that contains the data you want’
Step 2: Select the multidimensional database (MDB) ‘ITIL Metric for ITSM 4075 – RemDash’
Step 3: Select the ‘Change Management’ cube
Step 4: Now click on the ‘Next >’ button
Step 5: Now click on the checkbox next to ‘Always attempt to use this file to refresh data’
Step 6: Now click on the ‘Finish’ button
Step 7: Now click on the ‘OK’ button in the pop-up to finish importing the data.
Step 8: We’re going to start off by changing the style of Excel a bit to make the dashboard features more focused. Click on the ‘View’ tab at the top of the page
Step 9: Simply click on the checkbox next to ‘Gridlines’ to remove them from Excel
Step 10: Now we’re ready to begin building the dashboard. Select the search field and type in ‘changes closed’
Step 11: Now select the ‘Changes Closed’ field to add it to our pivot chart.
Step 12: Click the ‘Clear Search’ button to the right of the search bar to make it ready for more input
Step 13: Now click on the search field and type in ‘percentage of successful changes’
Step 14: Scroll to the bottom of the list of field inside the ‘Percentage of Successful Changes’ KPI. Inside the KPI, select the ‘Value (Percentage of Successful Changes)’ field
Step 15: Now at the top of the page, select the ‘Insert’ tab
Step 16: Click on the ‘Filters’ icon at the top of the page
Step 17: Under that, click on the ‘Slicer’ icon
Step 18: Now I’m going to go through a list of some slicers to bring in. It’s always good to bring in more than you need. I’m going to bring in a lot that I’ll end up deleting later. Feel free to select whichever are most useful to you. For now, under ‘Hierarchy – Assignee’ select ‘Assignee Support Company’, ‘Assignee Support Organization’, and ‘Assignee Support Group’
Step 19: Under ‘Hierarchy – Location’ select ‘Region’, ‘Site Group’, and ‘Site’
Step 20: Now under ‘Hierarchy – Product’ select ‘Product Categorization Tier 1’, ‘Product Categorization Tier 2’, and ‘Product Categorization Tier 3’
Step 21: Finally under ‘Hierarchy – Operational Category’ select ‘Operational Categorization Tier 1’, ‘Operational Categorization Tier 2’, and ‘Operational Categorization Tier 3’
Step 22: Now click the ‘OK’ button to bring them into the dashboard
Step 23: Now all the slicers have been brought in, but it’s probably not in the arrangement you want. Spend some time moving them into better spots. I move around the dashboard pieces a bit in between steps, so just make sure you’re arranging it in whatever way is best for you
Step 24: This is much better. And now I’m going to delete the ones that I decide I don’t want. For this example, I’ll delete everything except ‘Assignee Support Group’, ‘Region’, ‘Site Group’, and ‘Product Categorization Tier 1’
Step 25: Now we’re ready to bring in a date filter. It’s very important to click on one of the cells inside the table we created earlier. If you forget to do that, the date filter will not work. Begin this next step by selecting the PivotChart icon at the top of the page
Step 26: Now click on the ‘Timeline’ icon
Step 27: Scroll to the bottom of the list of timelines and select ‘Submit Date’
Step 28: Now click on the ‘OK’ button to confirm our choice
Step 29: Now click on the chart again so we can bring back our selection of fields
Step 30: Now go to the search field and type in ‘submit date.month’
Step 31: Click and hold on the ‘Submit Date.Month’ field
Step 32: Drag it down into the ’Rows’ area
Step 33: Now our table contains a lot of new rows, so we’re going to use the timeline to select a subset. Click on the faded ‘Month’ dropdown in the top right corner of the timeline
Step 34: Select the ‘Year’ filter to change the type of our date
Step 35: Select a year that you would like to examine. For this example, we’ll use 2014
Step 36: Now click on the pivot chart. We’re going to copy it and make three more so we can highlight different data fields
Step 37: Press CTRL-A on your keyboard to highlight the whole chart. Then press CTRL-C to copy it to your keyboard.
Step 38: Select a new space for the next chart by clicking on a cell. Then paste the next chart by pressing CTRL-V on your keyboard.
Step 39: Repeat the process in another spot, again pressing CTRL-V on the keyboard
Step 40: Finally, choose one more position for a final copy.
Step 41: Notice how these two charts are smaller than the other two. If you wish to resize them, use the cell size changer at the top or side of the Excel spreadsheet. If you wish to move any of the charts, simply press CTRL-A to highlight it, CTRL-X to cut it from the dashboard, and CTRL-V to paste it to a new location.
Step 42: Now if we’ve done everything right, we can test out the slicers. I’m going to click on the ‘Broadcast Service’ selection under the ‘Product Categorization Tier 1’ slicer.
Step 43: And we can see that the slicer affects all the pivot charts we’ve created
Step 44: Now click the top right corner of the slicer with the ‘Clear Filter’ button to reset the changes
Step 45: Now we’re going to start editing the copied pivot charts. Left-click one of the pivot charts to begin editing it.
Step 46: Now click and hold on the ‘Month’ field in the ‘Rows’ area
Step 47: Drag the selection back into the ‘PivotTable Fields’ section to take it out of the table
Step 48: Now click on the search bar and type in ‘assignee support organization’
Step 49: Now under the ‘Assignment’ folder click and hold on the ‘Assignee Support Organization’
Step 50: Now drag the selection down into the ‘Rows’ area
Step 51: Now select the next chart by left-clicking it
Step 52: Now go back to the ‘Rows’ area to click and hold on the ‘Month’ field
Step 53: Now select the search field and type in ‘product categorization tier 1’
Step 54: Now click and hold on the ‘Product Categorization Tier 1’ field
Step 55: Now drag it down into the ‘Rows’ field to bring it into the chart
Step 56: Now right-click on the final chart
Step 57: Now this time click and hold on the ‘Changes Closed’ field under the ‘Values’ area
Step 58: Drag the selection up into the ‘PivtotTable Fields’ area to clear it from the current chart
Step 59: Now click and hold on the ‘Percentage of Successful Changes’ field under the ‘Values’ area
Step 60: Now click on the search field and type in ’changes completed emergency’
Step 61: Now click and hold on the ‘Changes Closed Emergency’ field
Step 62: Drag the selection down into the ‘Values’ area
Step 63: Now click on the original table that we didn’t modify
Step 64: Now we’re going to click on the ‘PivotChart’ icon
Step 65: Now click on the ‘PivotChart’ selection
Step 66: In the left section of the ‘Insert Chart’ click on the ‘Combo’ selection
Step 67: Click on the ‘Secondary Axis’ checkbox next to the ‘Percentage of Successful Changes’, and click on the ‘OK’ button to confirm our selection
Step 68: Now we can move around the new chart. I’m going to spend a bit of time cleaning up the dashboard so we can finalize it.
Step 69: Now that the whole dashboard is arranged, we can see all of the relevant data presented in a dynamic manner
Support by website
You can obtain technical support from Northcraft Analytics 24 hours a day, 7 days a week at https://northcraftanalytics.desk.com. From this website, you can:
- Find the most current information regarding Northcraft Analytics products.
- Manage your account information
- Upload documents for content sharing exchange.
- View shared documents from other users.
- Order or download product documentation.
- Download products and maintenance releases.
- Report an issue or ask a question.
Support by telephone or email If you need technical support either by phone or email:
Call (+1) 800 565 1375
or by email using:
Every effort has been made to make this as complete and as accurate as possible, but no warranty or fitness is implied.
The information is provided on an “as is” basis. The author and Northcraft Analytics, LLC, shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this manual or from the use of the discs or programs that may accompany it.
Copyright © 2010 Northcraft Analytics LLC. All Rights Reserved.
Trademark Legal Notice. All product names, trademarks and registered trademarks are property of their respective owners. All company, product and service names used in this website are for identification purposes only. Use of these names, trademarks and brands does not imply endorsement.