All articles with 'dynamic named ranges' Tag

Earth Venus cosmic dance – Animated chart in Excel

Published on Apr 25, 2016 in Charts and Graphs, VBA Macros
Earth Venus cosmic dance – Animated chart in Excel

Recently I saw an interesting Earth Venus cosmic dance video on Facebook. See the original video below or here.

Although this is not entirely accurate from physics & astronomy perspectives, the dance is a stunning example of patterns that are generated by simple things.

I wanted to recreate this cosmic dance in Excel. How else am I to get my spreadsheet fix on a Saturday?

Here is a quick demo of the final outcome. Read on to learn more about the Earth Venus cosmic dance.

Continue »

Dynamic Print Areas

Published on Aug 24, 2015 in Excel Howtos
Dynamic Print Areas

Yesterday, you learned about Print Areas – a time & paper saving feature of Excel. While print areas are great, you can only set up one print area per sheet. What if you want to print either report or data based on user selection? 

In such cases, you can set up dynamic print areas. 

That is right. See above demo to understand how it looks. Read on to learn how to set up dynamic print areas.

Continue »

Narrating the story of change using Excel charts – case study

market-share-changes-over-time

Here are three questions you often hear from your boss:

  1. What changes are happening in our business and how do they look?
  2. Do you know how to operate this new coffee machine?
  3. Why does every list has 3 items?

Jokes aside, our urge to find change in environment predates cave drawing, slice bread and Tommy Lee Jones. So, today let’s examine a very effective chart that tells the story of change and re-create it in Excel.

Continue »

Robust Dynamic (Cascading) Dropdowns Without VBA

Published on Feb 25, 2014 in Learn Excel, Posts by Jeff

Recently I posted about how you could construct dynamic (cascading) dropdowns that could easily handle multiple levels, like this: …and we saw that users could subsequently change upstream dropdowns in a way that would make downstream choices invalid, like this: In my previous post I used some VBA to clear out any ‘downstream’ choices if […]

Continue »

Dynamic (Cascading) Dropdowns that reset on change

Published on Feb 13, 2014 in Excel Howtos, Posts by Jeff, VBA Macros

Dynamic dropdowns are a handy way to get your users to make choices based on what they’ve previously chosen, while steering them away from making invalid choices. Today we’re going to look at one that easily handles multiple levels, and we’ll take a look at what could go wrong. Let’s see one in action, shall […]

Continue »

OFFSET formula – Explained

Published on Sep 17, 2012 in Learn Excel
OFFSET formula – Explained

Today, lets learn OFFSET formula.
OFFSET formula gives us reference to a range, from a given starting point with given height and width in cells.

OFFSET formula syntax
OFFSET formula looks like this:
=OFFSET(starting point, rows to move, columns to move, height, width)

Starting point: This is a cell or range from which you want to offset
Rows & columns to move: How many rows & columns you want to move the starting point. Both of these can be positive, negative or zero. More on this below.
Height & width: This is the size of range you want to return. For ex. 4,3 would give you a range with 4 cells tall & 3 cells wide.

Read on…,

Continue »

MLB Pitching Stats Dashboard in Excel+VBA by our VBA Class Student

Published on Aug 10, 2011 in VBA Macros
MLB Pitching Stats Dashboard in Excel+VBA by our VBA Class Student

Nothing gives a teacher more satisfaction than seeing a student apply the knowledge to do something awesome. So naturally, I jumped with joy when I got this email from Dan, one of my VBA Class students,

Hi Chandoo,
After going thru your VBA Classes, I realized that we can lots of awesome stuff with pivot tables + simple macros. I recently created an Excel Dashboard to depict MLB (Major League Baseball) Pitching Stats. I could not have done this had I not learned VBA. Thank you so much for teaching the class.

I got curious and requested Dan, if I could share the file with you all. Being a lovely person, Dan agreed immediately.

Continue »

Remove duplicates & sort a list using Pivot Tables

Published on Sep 27, 2010 in Analytics, Learn Excel, Pivot Tables & Charts
Remove duplicates & sort a list using Pivot Tables

Removing duplicate data is like morning coffee for us, data analysts. Our day must start with it. It is no wonder that I have written extensively about it (here: 1, 2, 3, 4, 5, 6, 7, 8). But today I want to show you a technique I have been using to dynamically extract and sort […]

Continue »

Make your data validations dynamic! [quick tip]

Published on Sep 13, 2010 in Excel Howtos
Make your data validations dynamic! [quick tip]

Ok, since excel school 3rd batch is going to open on 15th, I wasnt going to write anything today. I have slept just 4 hours last night, blame it on work (and that funny video on youtube). But I found 30 minutes free time, so here you go, a quick but delicious tip on making your data validation dynamic.

Dynamic Data Validation?!? What in the name of slice bread and peanut butter is that?

See the demo aside to understand and read on to master the trick.

Continue »