HOW TO CREATE AN INTERACTIVE DASHBOARD ON EXCEL- PART TWO
Welcome to the second and more interesting phase of creating interactive dashboards! If you haven’t checked out the part 1, kindly click here so you can ride on more smoothly.
CREATING DASHBOARD…continued
Once you’ve selected a nice color for your dashboard,
Using the sample we have; we go ahead and insert some shapes and titles to make it even nicer.
At this point you either use your “Text box” or “Shapes” icon on the “insert” tab.
If using the Text box, just click and drag to desired size
If using the Shapes icon, simply select the rectangular shape, then click and drag to the desired size.
Then fill with desired color and write the title of the dash board in it. You can also use the desired font and color of choice here.
Sample is shown below for guidance:
Having done that, you should do something similar but this time it’ll be vertically positioned so that we can insert some symbols into it as indicated in the figure below:
At this point, we need to insert a symbol to represent the data set as well as the pivot table so they can be linked from there.
So, go to the “Insert” tab and click on “Icons”
You can type in “data” in the search button.
Select the data Icon you prefer and click on “insert” to have it on your dash board.
Resize and position in the vertical triangle by dragging.
Repeat the same process for the Icon for the Pivot table.
Voila! You’re done with that phase.
CREATING CHARTS
Now let’s start creating those charts and decorating our dashboard some more!
Remember your pivot table form part one? Yeah, we gon be needing that now…lol.
Also remember I told you the pivot table is like you x-y table for your graphs used back then in high school in mathematics.
Let’s put that knowledge into some beautiful use right here.
First thing is to go to the pivot table sheet
You might want to arrange the figure in each table in ascending or descending order, this gives your charts a neater outlook and also easier to interpret.
To sort i.e., arrange in ascending or descending order,
First highlight the figures in a selected table
Go to the Home tab,
Click on “sort & filter”. It’s usually located on the far-right side depending on the window you’re using.
Then select “smallest to largest” or “largest to smallest” as the case may be.
Do this for every other table on the pivot sheet.
Now let’s start the main deal.
To create a chart for any particular table,
Click on the table
Go to the Insert tab
Click on “Pivot Charts”
Then select the type of chart you want and click OK.
(Remember you can select any type of chart you deem fit at this point)
You should have something similar to the figure below:
There you go, your chart is created effortlessly!
You can decide to pimp your chart too.
Simply click on the plus sign at top right corner of the graph
Then check and check what you want out and want in
In this case the only option I checked was the Chart Title and Chart Labels.
Then I edited the title by clicking on it.
You can also change the color and style of the chart by clicking the brush symbol just under the plus sign at the top right corner of the graph.
Play around with whatever style and color you prefer.
At this point, your chart should be looking colorful and ready for the dashboard.
Cut the image from the pivot sheet by using Ctrl+x and then paste it on the dash board and adjust accordingly.
Do the same for every other table
Remember you get to select any type of chart you desire and paste on your dashboard accordingly.
Wow! You’ve done an awesome job so far.
In the next write up,
We shall discuss how to create and use slicers, as well as creating cards and more importantly, linking the dashboard with our pivot table sheet and data set sheet without having to flip through the sheets manually.
Sounds interesting already. See you in the next article…
