Excel

Standard Excel

You might have the right tool sitting on your desktop right now. Excel is free (for you) and you are probably familiar with how to use it. You can do a lot using pivot tables and slicers. (Check out this 4 minute video on slicers if you’ve never heard about them - think of them as visual filters).

There are lots of resources on using Excel for dashboarding. Watch this 16 minute video to see how you can build an interactive dashboard using pivot tables and slicers. Our Excel Sequence through Data Academy, can help you master the basics of excel.

You can save Excel dashboards to shared drives so internal staff can view. This is a great solution for small, internal teams with smaller datasets.

If your source data is public on DataSF's Portal, you can automatically update the dashboard using OData. Read our article on Using OData to connect to desktop software.

Excel with Power Tools

In recent years, Excel has been adding on a bunch of tools to enhance Excel. Again, these are free(ish) tools but they need a bit more learning. Be sure to start with a basic understanding of pivot tables and slicers before you experiment with Power tools.

The Power terminology can be a bit confusing so we break it down for you:

• Power Query: Get and clean up your raw data. This tool allows you to clean up, reshape, create new columns based on functions and prepare your raw data. For example, you need to flip your columns and rows to do the analysis and you have to do it every time. Power Query tries to solve that problem so you do it once.

• Power Pivot: Transform, model, and summarize your data. This is pivot tables on rocket power. This allows you to create rich summary tables built on your data. It also lets you create relationships between different datasets.

• Power View: Visualize and report your data. Think of this as the charting tools in Excel but enhanced.

• Power Map: Map your data. This is a newer add on that lets you map data in Excel.

• Data Analysis Expressions (DAX): Define custom calculations using this query language.

Here are links to learn more about these options:

• Introduction to Power Query (8 min)

• PowerPivotPro’s video playlist on PowerPivot

• Power View - getting started video (5 min)

• Power Map

  • Getting started with Power Map (Microsoft article)

  • Introduction to Power Map (8 min video)

• DAX Language overview (Microsoft article)

Online Training

Below are links to training from Lynda.com. Learn how to sign up for an account through the SF Public Library.

• Power BI Features in Depth Chapters 1-8 focuses on Excel Power Suite; last chapter covers PowerBI (5h 57m)

• Pivot Tables in Depth Versions: 2010 (3h 43m), 2013 (4h 20m), 2016 (3h 42m)

• Data Modeling with Excel Power Pivot (1h 15m)

• Creating Excel Power Pivot and Power View Dashboards (1h 12m)

• Creating Interactive Dashboards in Excel 2013 (2h 34m)

• Excel Power Pivot DAX in Depth (2h 25m)

• Visualizing Geospatial Data with PowerMap (37m)