All articles in 'VBA Macros' Category
Learn two techniques to conditionally format the background of a chart based on some external value.Continue »
Today is Holi, the festival of colors in India. It is a fun festival where people smear each other with colors, water balloons, tomatoes and sometimes rotten eggs. This year we wanted to play with only water guns, but kids vetoed that idea vehemently. So we ended up driving to my sister-in-law’s place to play with colors (there were no rotten eggs or tomatoes, thankfully).
Let me smear a few colors on you
I would love to splash a jug full of color water on you and say Happy Holi. But the internets have not advanced thus far. So I am going to give you the next best option.
An Excel workbook to play holiContinue »
Last week, I asked my email newsletter readers to submit “one data analysis problem you are struggling with”. We called it BYOD – Bring your own data. More than 100 people have emailed various interesting (and often very difficult) problems. This week (between 16th of February to 20th of February), let’s take a look at some of these problems and solve them.
Consolidating data in different shapes
We can use either VBA or Excel’s consolidation features to combine data that has same shape (ie same number & type of columns). Here is one way to do it.
But what if we need to consolidate data that is in different shapes?
Something like above.
In such cases, we can use 3 powerful tools.
- Multiple Consolidation Ranges – Pivot Tables
- Power Query
So let’s examine how to use these approaches to consolidate data in different shapes.Continue »
Here is a common problem. Imagine you are looking a complex spreadsheet, aptly titled “Corporate Strategy 2020.xlsx” which as 17 tabs, umpteen formulas and unclean structure. Whoever designed it was in insane hurry. The workbook has formulas like this, =SUM(Budget!A2:A30, 3600)+7925 .
It was as if Homer Simpson created it while Peter Griffin oversaw the project.
So how do you go about detecting all cells containing formulas with hard-coded values?Continue »
We analysts like to compare. If you ever want to keep an analyst busy, just give her 2-3 options. She won’t return to your desk until the cows come home. My wife uses this trick all the time. Picture this:
[In late 2013]
Me: I want to buy a new phone
She: Do you want Nexus 5 or Galaxy S5 or iPhone 5s?
Its late 2014 and I am not done comparing.
So today, let’s talk about an interesting comparison scenario.
Comparing by letter or word
See above demo to understand the concept. Read more to learn how to do this.Continue »
Did you know you can Calculate Pi by throwing Frozen Hotdogs !
Learn how to calculate Pi by throwing Frozen Hotdogs with this Excel Simulation of Buffon’s Needle.
Recently, we discussed about the case of unwieldy data and how we lookup what we want using formulas like SUMIFS. Today, let us learn few more ways to solve the same problem.
Suitable structure spawns simple solutions
Poorly structured is the 2nd biggest problem of analysts. The first one is not enough coffee. That is why there is a dictum in the data analytics world.
Structure is everything
So, we can easily solve our lookup problem, if our data were to magically re-arranged in 2 column fashion – Data & Value.Continue »
Howdy folks. Jeff here, bringing you a Public Service Announcement: Thanks to the magic of VBA , Structured PivotTable References are coming to a PivotTable near you! Structured References for PivotTables? So what? Well, because PivotTables are the best bit of ‘old’ Excel, and Tables are the best thing about ‘new’ Excel, and it’s about […]Continue »
In the 22nd session of Chandoo.org podcast, lets do some macros.
What is in this session?
VBA (or macros, automation) is a mystery for many of us. So in this podcast, lets unravel the mystery behind it and get you started with the awesome world of automation.
In this podcast, you will learn,
- What is a macro?
- What is VBA then?
- Reasons for using VBA Macros
- Extending Excel’s capabilities
- How to get started with VBA Macros?
- Using Recorder
- Example Macro
- Going beyond recorder – Learning VBA
This is a guest post by Krishna, a football lover & one of our readers.
The wait for lifting the most valued priced in football for Germans was finally over. For a football fan, world cup is best time that is scheduled every four years and that if your favorite team lifting the trophy is like your crush is going on a date with you.
A sneak-peek at the final dashboard
Here is the final dashboard (it has more functionality than depicted). Click on it to enlarge.Continue »
On July 4th this year, I published an animated fireworks chart for you. Many of you liked it. Quite a few wanted to know how its made.
So here is a video explaining the construction of fireworks.
(You can see this video on our YouTube Channel too)Continue »
To all our readers & friends from USA,
I wish you a happy, fun & safe 4th of July.
For the last 4th of July (2013), we (Jo, kids & I) were in USA. We went to Washington DC to meet up a few friends for that weekend. And we had one of the most memorable evenings of our lives when we went to national mall area in the evening to watch beautifully choreographed fireworks. Kids really loved the amazing display of fire-crackers and enthusiasm.
While we all are back in India this time, it doesn’t mean we cant celebrate 4th of July. So I made some fireworks. In Excel of course.
Here is a little Excel animation I made for all of us.
4th of July Fireworks – Excel animation
First watch this quick demo (<15 secs)Continue »
Recently we asked you, what do you struggle doing in Excel? 170 people responded to this survey and shared their struggles. In this post, lets examine the top 10 struggles according to you and awesome remedies for them.Continue »
Hello everyone. Stop reading further and go fetch your helmet. Because what lies ahead is mind-blowingly awesome.
About a month and half ago, we held our annual dashboard contest. This time the theme is to visualize state to state migration in USA. You can find the contest data-set & details here.
We received 49 outstanding entries for this. Most of the entries are truly inspiring. They are loaded with powerful analysis, stunning visualizations, amazing display of Excel skill and design finesse. It took me almost 2 weeks to process the results and present them here.
Click on the image to see the entries.Continue »
This article is written by Michael Hutchens from Best Practice Modelling.
In the first article on Modular Spreadsheet Development, we got a high level overview of Modular Spreadsheet Development principles. This article discusses the practical implementation of these principles in Excel.
From my experience using Modular Spreadsheet Development over the past decade, there are three increasingly-efficient methods of implementation in Microsoft Excel:
1. Manual implementation;
2. VBA automated implementation; and
3. Commercial add-in implementation.
This article provides a comprehensive overview of each of these methods and a summary of their advantages and disadvantages.Continue »