All articles with 'macros' Tag

Use the getBoldText() function in Excel to extract bolded portion of a cell automatically. Saves you time and helps with data cleaning.

Filtering a list is a powerful & easy way to analyze data. But filtering requires a lot of clicks & typing. Wouldn’t it be cool if Excel can filter as you type, something like above.

Let’s figure out how to do this using some really simple VBA code.

We can select a few cells in Excel and quickly see their count, sum etc. in the status bar. Ever wanted to customize the status bar to show something else, say difference? You can use VBA add-ins with application level events to achieve this. In this VBA Example, we will look at how to set up a class module, application event in our personal macro add-in to customize status bar.

Let’s say you have data like this in a spreadsheet. Don’t roll your eyes, I am 102% sure, right at this moment, someone is (ab)using Excel to create similar messy data.

How do you reshape it to one column?

You could use formulas, VBA or Power Query. Let’s examine all these methods to see what is best. All these methods assume your data is in a range aptly named myrange.

As part of a my ongoing consulting gig, I often run painfully long queries on SQL Server to fetch data. This data obviously ends up in Excel for further analysis. Now, some of these queries return NULL values in several columns (did I tell you that the queries have a gazillion left joins on them, oh yeah, they do). Although technically NULL is nothing, when you import this data to Excel, we get the text value NULL in the cells. And I don’t need these NULL values messing up all the calculations and pivots.

Of course, we can go ahead and use the isnull() SQL function to deal with them at the query level. But since the queries have 100s of columns and used by various teams for different purposes, changing them causes a lot of pain. So I did what any sensible Excel user would do. Just kill those NULLs mercilessly once they are in Excel.

I started a new consulting gig with NZ Ministry of Business (aside: when I told my daughter about this, she widened her eyes and said ministry of MAGIC!!! ). On my first day, while having lunch in breakout area, I chatted with the gentleman sitting opposite me. We got talking about this and that and eventually the topic turned to What I do at MB. So I told him that I am helping the HR with some data analysis and reporting using Excel & SQL Server. He asks me, “So you must be familiar with Excel object model”. I said, “oh, why yes”. He then asks me, “I have this problem that is bothering me for years. You see, I get a lot of data. And I use Find (Ctrl+F) to find all the cells that contain certain code. But the results are all over the place. I want to know how to extract all the finds to a target worksheet – value & address format.”

I explained him how to do this while chewing mouthfuls of rice & veggies.

But once I am home, I thought, “hey, maybe there are others out in the world who want to do this”.

So here we go.

It is election time in USA, and that means there is a whole lot of drama, discussions and of course data analysis. There are tons of cool visualizations published on all the data. Previously, we talked about “How Trump happened” chart.

Today let’s take a look at the beautiful decision tree chart by NY Times explaining what would happen if each of the 10 swing states vote for Democrats or Republicans. Go ahead and look at that chart. And when you are done playing with it, come back.

My first thought after looking at the chart is: Wow, that is cool. I wonder how we can recreate that experience in Excel?

But as you can guess, making a dynamic tree visualization in Excel is pretty hard. You can create a bubble chart mixed with XY chart to show all the nodes of the decision tree, but as this tree has 2^10 nodes at the bottom level (and 2^11-1 total nodes) our chart would look very clumsy and busy.

So, instead of replicating NY Times chart, why not make our own version that explains the data? You can reuse this idea when visualizing outcomes of several what-if scenarios.

Anyone who has made a pivot table and their grandma knows that formatting them is a pain. Let’s recap the steps to apply one of the most common formats – currency format.

1. Right click on any value field
2. Go to Value field settings
3. Click on “Number Format” button
4. Choose Currency format
5. Close the boxes, one after another

Unless you get paid per click, you wont be happy with all those clicks.

Wouldn’t it be cool to just click once and apply most common format to your pivot fields?

Of course you can. Just add oneClickCurrency macro to your personal macros workbook. And then add this to your Home ribbon as a custom button and you have a one click format option for any pivot.

This is third episode of our Monthly Master Class.

In this one, you will learn how to get maps in to your Excel workbooks. Understand 5 key techniques for making maps based visualizations in Excel – from regular charts to cell grids to VBA to Power Maps, everything is covered in this intense Master Class.

My twins (Nishanth & Nakshtra) are now almost 7. They are super keen to learn how computers work. So the other day, I showed them Code.org where there are several coding exercises disguised as games. They loved those games … err coding exercises. So that got me thinking… why not make a game in Excel that teaches kids simple programming concepts.

So I built a Snowman & Hot Chocolate Maze game. In this post, let’s understand how to build such a game using Excel VBA.

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.

As announced earlier, here is the first one of our Master Classes. In this one, you will learn how to create advanced interactive charts in Excel by blending pivot tables, slicers, timelines, VBA, conditional formatting and charts. The final outcome is BEAUTIFUL!!! Monthly Master Class – Episode 1 – Trailer Check out a quick trailer of the […]

This is a guest post by Chirayu, a member of Chandoo.org forum.

Animating Charts in PowerPoint cannot be done without the help of 3rd party software’s that create a flash file of the chart & embed it into the presentation.

However there is a workaround for this. Save your chart as multiple images & insert them (overlapping on top of each other). Use VBA on Developer tab Controls such as Combo Box, Option Button, Check Box etc. to “Bring To Front”” the corresponding image. Thus giving the illusion of an Animated Chart in PowerPoint.

This guide will teach you how to animate the charts, using the three Developer tab Controls that were mentioned before. The code & functionality only works in Slide Show Mode. File must be saved as PowerPoint Macro-Enabled Presentation (*.pptm)

During last week, an alert reader of our blog, Jørgen emailed me a link to “How Trump happened“.  It is an interactive visualization by Wall Street Journal. Jørgen asked me if we could replicate the visualization in Excel. My response: “Making a new chart in Excel? Hell yeah!”

Read on for awesome visualizations and full explanation.

Here is an interesting problem to keep you busy.

Transpose the address data in column A into the format indicated in C:G using either VBA, formulas or Power Query. Once done, post your answers in comments section.