• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to create excel dashboard without using pivot and slicers

Chihiro

Excel Ninja
Sure there is.

Though "visually appealing" is very subjective. My recommendation as always, is to design dashboard to tell a meaningful story through data.

Visual appeal can be applied later, but make sure to not detract from data itself.

You can find bunch of tutorials and samples in the site. Many of dashboard samples made by Chandoo does not use Pivot Tables.

Some examples: Some use Pivot/Slicer in first link, but many example where it does not
https://chandoo.org/wp/kpi-charts-dashbaords/
https://chandoo.org/wp/fifa-2014-worldcup-dashboard/
https://chandoo.org/wp/design-customer-service-dashboard/
 

Tarak Mehta

New Member
Thanks for the quick reply, Chihiro. Appreciate it.
What I am looking for is to show sales of various products, based on male/female, vs visits to the website. Conversion rate etc.
 

Chihiro

Excel Ninja
I'd recommend at least sample of your raw data (enough data to create sample dashboard). Along with how each metrics are calculated from your raw data.

Assume that we know nothing of your business (or data).
 

Tarak Mehta

New Member
Thanks for replying, Chihiro. Attaching the file for your ready reference. Many thanks in advance for the help.
By the way, as your name suggests, you are a HERO for me :)
 

Attachments

Chihiro

Excel Ninja
Hmm, in your data sample. I don't see any time segment. Typically speaking, unless dashboard is for fixed duration (i.e. Fiscal Year etc) for summary of the duration... you'd want that info included in raw data.

Also as I have mentioned...
Assume that we know nothing of your business (or data).
Can you explain what each of your abbreviation/short code means?

TY, LY, GR ... I assume it's This Year, Last Year and Growth Rate, but don't want to guess.

Also AUR, L2B, what does these mean?
 

Tarak Mehta

New Member
Oops, sorry forgot to mention what the abbreviations mean. But you are right TY, LY, GR is This Year, Last Year and Growth Rate. AUR is Average Unit Revenue and L2B is Look to Book (i suppose that means, sales to visits)
Many thanks for your help.
 

Chihiro

Excel Ninja
Here's bit of demo on how to set up dynamic ranges etc.

I was going to do comparison chart on 2 metrics, but decided against it as data did not flow well.

For now, I've just added picture tool to dynamic range. Just to show something.

Few notes on controls:
1. GR - Up/Down - This controls whether to rank ascending or descending based on Metric 1's GR.
2. Metric 1 - Primary metric (left side)
3. Metric 2 - Secondary metric. Shows corresponding stats for same item.

Interestingly, Some items show high Collection_View, but low Revenue.

Ex: Male - BOTTOMS - JEANS
This one may be due to significant decrease in AUR (-93%).

Will see if I can think of something else.
 

Attachments

Tarak Mehta

New Member
Thank you Chihiro. Will study the same to understand it and make something on my own too. Await your further insights on the same. Thanks
 

Chihiro

Excel Ninja
It's been a while since I did purely formula/built-in tool dashboard without use of Data Model.

This reminded me why :p

With wide range of data values, it's bit difficult to chart all data points in same chart while making it readable. You could... use panel chart with changing axis values... but I don't like them, as visualization will be misleading at a glance.

In these type of scenario, I like to dynamically group items into TopN + Other. Which I use DAX measure with pivot table to accomplish.

I suppose I could just show AUR & L2B in chart corresponding to Camera tool and dropdown selection.
 

Tarak Mehta

New Member
You are right Chihiro; all data points in 1 / 2 chart will make it difficult to comprehend.
Maybe we could go in for a 5/6 chart dashboard, that are dynamically linked, so that change in 1 parameter, gets reflected in all the other charts too.
DAX measure is something that I am reading now (thanks to you) for the first time, will learn more about it.
From the view point of a manager, what would you like to see on the dashboard from the data that is available?
 

Attachments

Tarak Mehta

New Member
And also maybe we can add three master parameters, to see data based on 1) Male + Female 2) Only Male 3) Only Female.
What do you think? Will that make it more presentable? Awaiting you insights on how to make this more interesting.
Thanks & Regards, Tarak
 

Chihiro

Excel Ninja
I'm not familiar with your business.

But it really depends on who your audience is.

Is your audience executives (CEO, CFO etc)? Then I'd prepare top level report which gives overall business performance and it's trend.
So that they can make large business decisions based on data.

For Ops managers. I give more data that's pertinent to their day to day running of the business. Data set will depend on their responsibilities.

I'll typically have overview/summary at top. Then have key influences below (i.e. Top N, Bottom N). But based on volume/$ value rather than rate (%).

Ex:
Product B may be growing 50%, but only 1% of total revenue. Where as Product A is declining at 5%, but 50% of total revenue. Which do you think is more important to the business?
 

Tarak Mehta

New Member
It would be a report with overview / summary at the top and then on total visitors and revenue generated through sales across different product categories, comparing periods to understand growth pattern.
So basically an interactive dashboard with appropriate charts & graphs for senior management consumption that provides relevant insights from the data.
 

Chihiro

Excel Ninja
You already have that. For this type of data, visual really doesn't help much. In my opinion, it just adds fluff.

You could use conditional format etc to bring attention to problem areas.

Oh and I would sort based on value, rather than name. Data should flow, to make it easier to read report (eye should naturally fall on most important data in a set).
 

Tarak Mehta

New Member
Yup, you hit it. Still, your thinking is pretty cool. Would be great to see what comes out of your view -

"Then I'd prepare top level report which gives overall business performance and it's trend.
So that they can make large business decisions based on data.

For Ops managers. I give more data that's pertinent to their day to day running of the business. Data set will depend on their responsibilities.

I'll typically have overview/summary at top. Then have key influences below (i.e. Top N, Bottom N). But based on volume/$ value rather than rate (%)."
 

Tarak Mehta

New Member
This looks good.
Just whacking my brains, on how to present the same in charts and graphs on a single dashboard. Instead of showing the data in tabular format.
 

Chihiro

Excel Ninja
There's too many metric to take it all in at a glance.

May be break it into sections and into separate sheets. With control at the top.

Or just focus on 3~4 key derived metrics that influences business decision.
 

Chihiro

Excel Ninja
But I'm not sure on your business needs and who the audience is. What's actionable and what' not.

I'd suggest, that you have stakeholder meeting and find out what their needs are first.

I'm not fond of visualization for the sake of visualization. Sometimes, data table is superior to chart in narrating the story.

In order to create visualization, you must first understand your business objective and what drives it.

Then by analyzing data, you'll decide on the story it's going to tell. From there, you will then decide on what visual works best for telling that story.

I can give you suggestions and techniques, but you'll need to tell me what story you want to tell.
 
Last edited:

GraH - Guido

Well-Known Member
I'd suggest a small tweak in the report of @Chihiro in #19.
For the grouping change the settings (untick) "Summary rows below details".
62805

It is not a big deal, I simply find it more intuitive that a "menu" drops open downwards.

some top of mind things that popped up...

This report contains so much info, the information drowns itself. "Whacking your brain..." indeed. I believe for a data analyst this is a great report though. You might find suggestions that some products are not successful. Shirts and swimming clothes might not be selling well, but perhaps you had 2 great summers or 2 warm seasons in a row, so people did not need to buy again or sales was more spread? Is there a correlation between visits and sales? You might be having more visits, but lesser or declining sales. => great website, but lousy products? Would you have loyal consumers (if that data is at hand)?
Those are the things a data cruncher can present to decision makers.

In the line of #15...
I'm not a great fan of simply comparing this year vs last year. At least a trend over several years might show a different story. As in "we did better then last year" versus "yes, we do better then last year, but in the overall trend our sales is slowing down".

MALE SEASONAL APPAREL only contains 1 sub item, you might group is in another way, like add it to MALE OTHER.
 
Top