All articles with 'screencasts' Tag

Here is a quick tip to reduce the time you spend on chart formatting – use chart styles & color themes.

Excel offers various pre-defined color schemes and chart styles. Using them is very simple.

1. Select your chart
2. Go to Chart Design ribbon
3. Click on the style or color scheme you want.
4. Your chart changes instantly.

Yesterday I saw a tweet from @JanWillemTulp with random snow flake generator.

That got me thinking…? Why can’t we make a snow flake pattern in Excel?

This is what I came up with.

Read on to know more about this snow flake and download the example workbook.

Here is an interesting twist on the good old VLOOKUP. How to find the pricing applicable for given quantity of a product?

Something like above.

Looks interesting? Then read on…

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 48th session of Chandoo.org podcast, let’s make some animated charts!!!

What is in this session?

In this podcast,

• Announcements
• Why animate your charts?
• Non-VBA methods to animate charts
  • Excel 2013’s built-in animation effects
  • Iterative formula approach
• VBA based animation
  • Cartoon film analogy
  • Understanding the VBA part
• Example animated chart – Sales of a new product
• Resources and downloads for you

Power Query (Get & Transform data in Excel 2016) is a must have tool, if you wrangle with data every day. Here is a quick introduction, in case you are new.

Let’s learn how to use Power Query to unpivot data.

Essentially, we are trying to go from left to right in the above picture.

Doing something like this thru either formulas or VBA can be very complex. But Power Query can get you unpivoted data in just a few clicks. Sounds interesting? Read on.

Let’s say you made a chart to show actual and forecast values. By default, both values look in same color. But we would like to separate forecast values by showing them in another color.

If you are a seasoned Excel user, you may be thinking, “Oh, that’s easy. I will just create 2 sets of data (one for actual and one for forecast), make a chart from them and apply separate colors.”

But here is a really simple way to get the same effect.

Use a semi-transparent box to mask the forecast values, as shown above. Read on to learn how to do this.

Cascading drop downs enhance usability of your dashboards & interactive workbooks. A cascading drop-down is a 2 or more level selection mechanism. When you have 100s of selection choices, instead of creating one massive drop down or combo-box, you can set up multiple levels of drop downs, so that users can narrow down their selection. For example, users can select Country, State and then City using cascading drop downs.

There are many ways to setup cascading drop downs. You can use formulas coupled with either data validation or form controls. You can also use Slicers. In this video we will review these techniques.

Here is a familiar scenario: You are building a dashboard. Naturally, it has a few worksheets – data, assumptions, calculations and output. As you make changes to input data, you constantly switch to calculations (or output) page to check if the numbers are calculating as desired. This back and forth is slows you down.

Use Watch Window to reduce development time.

Here is an awesome planner template to help you manage activities over a month. It is useful for charity drives, activity planning, school schedules, marketing initiatives, project planning etc.

Read on to download a copy of the template & learn how to use it.

Pivot tables are very powerful analysis tools. They can summarize vast amounts of data with just few clicks. But they are lousy when it comes to output. Imagine the horror of putting a pivot table right inside your beautiful dashboard. One refresh could ruin the layout and create half-an-hour extra work for you.

How to combine the power of pivot tables with elegance of your dashboards?

The answer is: GETPIVOTDATA()

We all know the good old SUM() formula. It can sum up values in a range. But what if you want to sum up only filtered values in a range? SUM() doesn’t care if a value is filtered or not. It just sums up the numbers. But there are other formulas that can pay attention […]

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.

Here is a simple & effective tip on charting.

Give your charts descriptive & bold titles.

How to set up title that are smart & descriptive?

Simple, follow below steps.

1. Create the title you want in a cell
2. Select the chart title
3. Go to formula bar, press = and point to the cell with title
4. Press enter.

Conditional formatting is one of the most powerful & awesome features of Excel. It is very easy to setup. Naturally, people use it extensively. But the default conditional formatting rules can clutter your reports. Here is one tip that can declutter your reports.

Just show the formatting, not values.

See the above report.

By now, we know how to remove duplicates from data. You can use the Remove Duplicates button to do that.

But do you know that we can use remove duplicates button to get rid off duplicate combinations too?

Remove duplicate combinations – Tutorial

To remove duplicate combinations in your data, just follow below 4 steps:

1. Select your data
2. Click on Data > Remove Duplicates button
3. Make sure all columns are checked
4. Click ok and done!

See this demo: