Create a social media dashboard in Excel using our ready-to-use framework and stunning data visualization. This tool enables you to create various free templates in a short time.
If you have a Youtube channel, this article is for you! In the past years, this subject becomes timely because everyone is interested in what kinds of social channel activity can one’s metrics can generate.
If there are lots of visitors from social media on your site, then your channel can be popular in a short time. We cannot overlook that this can also have a positive financial impact.
Just a few words about the social media dashboard. We have chosen a classic grid layout to display 12 key Youtube metrics that belong to each major social media. Learn more about dashboard design and user experience.
Our framework enables you to stay on top of social media analytics from all of your favorite channels. Just replace your key metrics and you’ll able to create various reports for Twitter, Pinterest or Facebook analytics.
Social Media Dashboard – Metrics
We’ll track the following indicators:
- Total Watch Time
- Total Amount of Views
- Unique Views
- Impressions CTR
- Average watch Time (s)
- Average views per viewer
- Audience Retention
- Subscribers (Gain)
- Subscribers (Lost)
- Estimated Revenue ($)
- Monetized playbacks (%)
Discover the Structure
You can apply old-school solutions, but we can come up with something completely new that you have never seen before. We have chosen the latter one; it is worthwhile for you to do the same if you want to dazzle the audience or the bosses.
Let’s examine the Workbook structure! It’s simple.
The Data Sheet: Calculate the variance
As first, you can add your data to the Data Worksheet. We have created sections for the key performance indicators. Now we’ll apply a nested IF formula. Using this formula, we can identify and display the variance between the selected month and the previous month.
Build a Drop-down list
Let us see the dashboard sheet! If you need a monthly breakdown for your dashboard, the best choice to create a drop-down list, nothing special, we have used it many times before. We have chosen 12 social media metrics.
Let’s examine these in the given period. We have already talked about the menu that is in the upper right-hand corner of the template.
Create a Scorecard for Social Media Dashboard
Now we have to talk about the core component of our dashboard. Let us see the data behind the widget.
- As first, add a data label for the widget.
- After that, link the data from the Data Worksheet to the main Dashboard sheet.
- Create a simple line chart.
- Select the source range. Browse the cell which contains actual value. So we’ll get and display an annual trendline.
- Finally, link the variance from the calculation sheet.
Repeat these steps for every widget.
We have linked the scorecards to the calculated values on the data worksheet. All cards will be updated when you change the months in the drop-down list.
Apply Conditional Formatting
Jump to the calculation sheet! Now we’ll apply conditional formatting. Select the cell, then click to the Home tab and select conditional formatting.
Create new rules for the current selection. We can display the positive values green and the negative values red.
Tip: Don’t forget: Some metrics have inverse characteristics, for example, ‘Subscriber Lost.’
Choose an impressive chart type to display your most important YouTube metrics.
Social Media Dashboard – Final Words
Allow us a little Excel-related detour, so please don’t skip over it. One of the news media blogs has asked the currently best Excel professionals what they think about the relationship between BI and Excel.
That’s the fact that web-based solutions are on the rise, and the newest business intelligence tools of Microsoft Excel (Power BI, Power Query) get more and more emphasis.
Then again, all professionals harmoniously stated that Excel would be our colleague in everyday work. We recommend using custom productivity tools and free excel add-ins.
So, more haste less speed.
Of course, it is all free of charge and will not change in the future. There will be more novelty await you, so you should visit the site more often for the newest Excel tips and news.
Subscribe to our Youtube channel; we’ll publish dashboard how-tos frequently. Stay tuned.