From the course: Excel Data Visualization: Designing Custom Visualizations
Unlock this course with a free trial
Join today to access over 23,100 courses taught by industry experts.
Automation with OFFSET and COUNTA - Microsoft Excel Tutorial
From the course: Excel Data Visualization: Designing Custom Visualizations
Automation with OFFSET and COUNTA
- [Instructor] The next demo is one of the most important exercises in the entire course and it's about automating charts using OFFSET, COUNTA, and named ranges. Now the reason this lecture is so important is because functions like OFFSET and COUNTA are the basic building blocks to creating truly dynamic charts and graphs. Basically, they allow you to create fluid, dynamic chart source data rather than traditional fixed arrays, which really lets you bring your visualizations to life and integrate some much more advanced functionality. So head to the automatic chart updates tab, and as you can see here we've got a simple data set, two columns, date and temperature. And let's say my job is to update a trending temperature chart with every day that I collect new data. What you could do is select cell A1 through B24 and insert a line chart just like normal, and that's totally fine. The only challenge is that as you add more days worth of data and manually enter new temperatures, the chart…
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
Contents
-
-
-
(Locked)
Setting expectations1m 8s
-
(Locked)
Image overlay chart6m 1s
-
Binary ranges5m 2s
-
(Locked)
Automation with OFFSET and COUNTA7m 8s
-
(Locked)
Interactivity with form controls12m 30s
-
(Locked)
Animating changes over time14m 3s
-
(Locked)
Building a dynamic dashboard, part 116m 10s
-
(Locked)
Building a dynamic dashboard, part 212m 8s
-
(Locked)
Value-based formatting10m 10s
-
(Locked)
Dynamic series selection10m 26s
-
Custom pacing chart10m 23s
-
(Locked)
Custom gauge chart9m 58s
-
(Locked)
Visualizing percentages with arrays7m 16s
-
(Locked)
-