Excel With Interactive Excel Dashboards

Learn how to create amazing interactive Excel Dashboards that will wow your boss & take your career to the next level. Lifetime access with no subscription on Udemy.

What you'll learn

I teach you the 5 key questions to ask in the planning stage of your report so you get it right first time.

Learn how to choose the right chart for your data; I teach you over 20 charts and show you which type of chart will best display your data.

Learn how to write reports that are easy to read and interpret; plus I share with you a heat map of your page that shows you where your most important data should go and where will get the least attention.

Be able to build Dashboards that are quick and easy to update, in fact they can update themselves (imagine being able to confidently say that in an interview), by pulling in data direct from your external database, Microsoft Access, the web etc. at the click of a button if you set them up right (I show you how).

Animate your charts with some simple VBA code that I teach you how to edit to suit your charts. If you want 'Wow factor', this is it.

Learn how to create reports that are fully interactive so the reader can change the view themselves; they can filter by product, time period or any parameter you choose, they can show and hide data using check boxes, drop down lists, and option buttons to name a few.

Get your skills up to date with our supplementary Excel tutorials on PivotTables, Lookup and conditional logic formulas, macros and more.

Requirements

Ideally you already know how to use Excel and are familiar with basic formulas and how to build them. You’ve created basic charts using the standard ones available in Excel, and you’re familiar with Pivot Tables and Pivot Charts but not an expert.

You need Excel 2007 or later so you can open the workbooks and practice what you learn.

Mac users - this course is recorded in Excel for the PC. If you're a savvy Mac user then you will still benefit from this course and be able to map the menus you see in the tutorials to those on your Mac. I have had many Mac users successfully take this course.

Description

Excel Dashboards are a powerful tool to communicate and summarize complex Excel data. The price of this course reflects its value and I'm confident you'll get results. Just read some of the unsolicited testimonials I've received:"Financial Analyst - $52,500 (before taking your course) Sr. Financial Analyst - ($60,000) After finalizing my dashboard Sr. Business Analyst - ($70,000) Start that this MondayTaking your course has honed my skills and led to two promotions and a reputation. I am also energized to learn more. James C""I originally signed up for this as continuing professional education for my CPA license but I have learned so much, easily more than any other technical class I've taken in years. Thank you for a wonderful class, you are a great teacher. Dielle B"Dashboards can be created to summarize important information and are excellent for sharing a “snapshot" view among teams. Master the Creation of Interactive Excel Dashboards and Learn to Build and Distribute Dashboard Reports From the Ground up. Advanced Features Design and Display Principles Interactive Controls Analyzing Data Automating your Dashboard so it updates in seconds Impressive Content that Will Provide Insight, Analysis and Alerts Dashboards are fully interactive and dynamic and can help with project management, customer service, retail management, financial forecasting and much more. In order to learn Excel Dashboard students should have access to Excel 2007 or later. Students should also be familiar with putting together any type of report in Excel to be able to apply the time saving and visualization techniques taught here. Students should be able to build and apply basic formulas in Excel as well. Pivot Table and Pivot Chart knowledge is helpful, but expertise is not necessary to take this course. Contents and Overview This course includes 107 lectures and over 11 hours of content + 5 sample Excel Dashboards in order to give students a working knowledge of utilizing Excel Dashboard visuals to communicate their data. 3 hours of the tutorials are optional supplementary Excel lessons and only for students who need to fill any knowledge gaps.I don't waffle on, my tutorials get to the point so you're up to speed fast.Included are all the workbooks from the course which contain extensive notes, so you don't have to worry about taking notes while watching the tutorials. You can use the workbooks to practice what you learn and as a quick reference guide later on. The course covers advanced features of Excel Dashboards, such as display and visualization principles for clear dashboard creation, using interactive controls to keep information informative and relevant, analyzing and automating information for a fully functional and complete dashboard. It also includes key considerations for distributing dashboards like password protections, locking and unlocking cells and protecting workbooks. Upon completion, students will have a variety of techniques and examples that can be applied to their own datasets. Students will be able to choose the right chart for their data, write easy to interpret reports, know which areas of the dashboard will get the most attention, animate charts using VBA, create interactive reports (so users can change views and filter information), among a host of other abilities taught in this course.

Overview

Section 1: Introduction

Lecture 1 Course Overview and Introduction to Excel Dashboards

Section 2: Getting Your Data Ready

Lecture 2 Ideal Data Formats

Section 3: Design Principles

Lecture 3 Getting Your Design Right

Section 4: Display Principles

Lecture 4 Charts and Tricks for Trending

Lecture 5 Secondary Axis

Lecture 6 Smoothing Data

Lecture 7 Sparklines

Lecture 8 Formatting Tricks

Lecture 9 Highlighting Comparisons

Lecture 10 Top and Bottom Ranking

Lecture 11 Frequency Distribution

Lecture 12 Target vs Variance Charts

Lecture 13 Performance Against Target Range

Lecture 14 Bullet Graphs Excel 2007/2010

Lecture 15 Bullet Graphs Excel 2013/2016

Lecture 16 Win/Loss/Draw Conditional Format

Lecture 17 Custom Number Formats

Lecture 18 Dynamic Labels

Lecture 19 Text Formulas

Lecture 20 Symbols in Formulas and Charts

Lecture 21 Adding Series to Charts

Lecture 22 Embedding Graphics in Charts

Lecture 23 Small Multiples Charts

Lecture 24 In Cell Charts

Lecture 25 Panel Charts Excel 2007/2010

Lecture 26 Panel Charts Excel 2013/2016

Lecture 27 Step Charts

Lecture 28 Supplementary Excel Lecture: Excel 2013 Chart Formatting

Lecture 29 Supplementary Excel Lecture: Excel 2013 Chart Labels

Lecture 30 Supplementary Excel Lecture: Excel 2013 Combo Charts

Section 5: Interactive Controls

Lecture 31 Form Controls Introduction

Lecture 32 Check Boxes

Lecture 33 List Boxes

Lecture 34 Option Buttons

Lecture 35 Group Boxes

Lecture 36 Scroll Bar

Lecture 37 Buttons

Lecture 38 Combo Box with Macro

Lecture 39 Dynamic Date Filters 4 Ways

Lecture 40 Animating Charts

Lecture 41 INDIRECT Function Tricks

Lecture 42 Dynamic Named Range with OFFSET

Lecture 43 Dynamic Named Range with INDEX

Lecture 44 Dynamic Ranges for Charts

Lecture 45 Rollover Technique

Lecture 46 Scroll and Sort Table

Lecture 47 Dynamic Unique List

Lecture 48 Supplementary Excel Lecture: Inserting Drop Down Lists

Section 6: Analysing the Data

Lecture 49 PivotTables and PivotCharts

Lecture 50 Slicers

Lecture 51 GETPIVOTDATA Function

Lecture 52 Capture Slicer Selection in Formula

Lecture 53 Slicer & PivotChart Olympic Dashboard Overview

Lecture 54 Supplementary Excel Lecture: Creating PivotTables

Lecture 55 Supplementary Excel Lecture: Choosing Fields

Lecture 56 Supplementary Excel Lecture: PivotTable Layout

Lecture 57 Supplementary Excel Lecture: Filtering PivotTables

Lecture 58 Supplementary Excel Lecture: Modifying PivotTable Data

Lecture 59 Supplementary Excel Lecture: Insert a Table and Style Options

Lecture 60 Supplementary Excel Lecture: Add Rows and Columns to a Table

Lecture 61 Supplementary Excel Lecture: Perform a Function in a Table

Lecture 62 Supplementary Excel Lecture: Summarize a Table with a PivotTable

Lecture 63 Supplementary Excel Lecture: VLOOKUP Formula Sorted List

Lecture 64 Supplementary Excel Lecture: VLOOKUP Formula Exact Match

Lecture 65 Supplementary Excel Lecture: HLOOKUP Formula Sorted List

Lecture 66 Supplementary Excel Lecture: HLOOKUP Formula Exact Match

Lecture 67 Supplementary Excel Lecture: CHOOSE Function

Lecture 68 Supplementary Excel Lecture: INDEX and MATCH Formula

Lecture 69 Supplementary Excel Lecture: IF Function

Lecture 70 Supplementary Excel Lecture: Nested IF Formula

Lecture 71 Supplementary Excel Lecture: IF AND Formula

Lecture 72 Supplementary Excel Lecture: IF OR Formula

Lecture 73 Supplementary Excel Lecture: IF NOT Formula

Lecture 74 Supplementary Excel Lecture: IFERROR Function

Lecture 75 Supplementary Excel Lecture: SUMIF and SUMIFS Formulas

Lecture 76 Supplementary Excel Lecture: AVERAGEIF and AVERAGEIFS Formulas

Lecture 77 Supplementary Excel Lecture: COUNTIF and COUNTIFS Formulas

Lecture 78 Supplementary Excel Lecture: SUMPRODUCT Formula

Section 7: Automating Your Dashboard

Lecture 79 Linking to Access

Lecture 80 Incorporating New Data Automatically

Lecture 81 Auto Refresh PivotTables

Lecture 82 Supplementary Excel Lecture: Introduction to Macros

Lecture 83 Supplementary Excel Lecture: Recording a Macro

Lecture 84 Supplementary Excel Lecture: Assign a Macro to a Button or Shape

Lecture 85 Supplementary Excel Lecture: Run a Macro Upon Opening a Workbook

Lecture 86 Supplementary Excel Lecture: How to Inspect and Modify a Macro

Section 8: Putting It All Together

Lecture 87 Quick Re-cap

Lecture 88 Example Dashboard Tour

Lecture 89 Pyramid Chart

Lecture 90 Array Formula Alternative to SUMIFS

Lecture 91 LARGE and SMALL Array and DAVERAGE

Lecture 92 Rank Values with PivotTable

Lecture 93 Scatter Chart

Lecture 94 DMAX, DMIN, DAVERAGE

Lecture 95 Formatting Quickly

Section 9: Distributing Your Dashboard

Lecture 96 Checking and Publishing

Lecture 97 Excel Web App - Introduction

Lecture 98 Preparing Your File for Excel Web App

Lecture 99 Publish with Excel Web App

Lecture 100 Password Protection

Lecture 101 Supplementary Excel Lecture: Workbook Passwords

Lecture 102 Supplementary Excel Lecture: Protecting Workbooks

Lecture 103 Supplementary Excel Lecture: Unlocking Cells

Section 10: Bonus: Tour de France Dashboard

Lecture 104 Dashboard Overview

Lecture 105 SUMIFS Formula

Lecture 106 INDEX, MATCH and OFFSET Formula

Lecture 107 INDEX, MATCH Multiple Array Formula

Lecture 108 Zoom Images VBA Code

Section 11: Homework

Lecture 109 Homework Challenge

Lecture 110 Mynda's Homework Dashboard Part 1

Lecture 111 Mynda's Homework Dashboard Part 2

Section 12: Course Wrap Up

Lecture 112 Thanks for Joining

This course is not for complete beginners because building Dashboards, and I mean proper interactive dashboards in Excel, requires some intermediate to advanced skills.,Any course that says it’s for ‘All Levels’ is either going to cover a load of basics you probably already know, or it won’t get to the advanced topics that are going to make your dashboards truly dynamic and interactive.,I know everyone is at different levels of Excel ability, which is why I’ve included a load of optional supplementary Excel lessons so you can fill in any knowledge gaps, like PivotTables, lookup formulas, Conditional Formatting etc. ,I recommend this course for people who have put together any type of report in Excel before, as you will definitely benefit from the time saving tips and data visualisation techniques I share with you.