How to Create an Excel Dashboard Using Slicers and Timeline


Excel has 2 fantastic filter tools: slicers
and timeline. Both are used frequently in dashboards and
help us to read the data better, filtering them. We use slicers to filter data visually and
timeline to filter dates interactively fast and easy. Let’s start by creating a timeline. Go to Insert tab and click timeline. A new dialog box appears, asking us to make
a connection between timeline and data model. Click on data model tab and select this workbook
data model. Click open. Check the box order date from quarters sheet
and OK. Move the timeline below this pivot table. Highlight the timeline and from options tab,
choose a timeline style. It is very important to enable the connections
between filter, pivot tables, and pivot charts. So press report connections button and check
all the boxes. OK. The timeline is ready and working very well. Do some tests, for example, insert years and
see the results. Great. Now it’s time for the slicers. Go to Insert tab and click slicer. A new dialog box appears, asking us to make
a connection between slicer and data model. Click on data model tab and select this workbook
data model. Click open. We would like to filter sales per country
and per product category. So we check only these boxes. Our slicers are ready. Move them and change the style from option
tab. Don’t forget to connect the slicers with
the pivot tables and charts, from Report connections. For the finish, we’ll add titles for the
2 charts. Go to G7 cell and type ‘sales per product
container’. Then go to G22 cell and type ‘sales per
quarter’. Format these 2 titles and we’re ready. I always recommend to do some tests if all
are working fine. So click on slicers and check how the values
are changing

18 Comments

Add a Comment

Your email address will not be published. Required fields are marked *