Creating a table of all combinations in Excel [Video]

Power Query solution

Let’s assume you have three state codes and four department codes and you want to create a table of all the possible 12 combinations (3 x 4). How do you do it so that it is flexible? i.e. if you add a new state or department it must be easy to update the combination table.

Filtering blanks made easy

In Excel the “Blanks” option is usually at the bottom of the list. This slows down selecting it.

If you have a lot of entries you need to scroll all the way down to bottom of the list to choose it – see image below.

But the word “Blanks” is searchable, so if you type b in the Search box – your work is done – no scrolling required – see image below.

If your column contains text you might need to type in bla.

Copying a Date Down a Long List in Excel

Two techniques make it quick and easy

There are a number of mouse and keyboard shortcuts for copying. But there is one type of copy that can be frustrating. Copying dates can be challenging because, in general, Excel wants to increment them, not copy them. There is a simple technique to instruct Excel to copy a date.

Columns in a Text Box

You learn something new every day.

Today I discovered that you can have columns within a text box.

If you right click the text box and choose Format Shape the Task Pane below should open on the right.

Click the third icon (Size and Properties) at the top and then open up the Text Box options.

 

The Columns button allows you to specify how many columns plus the gap between them.

Have fun.

Handling DR and CR at the end of numbers in Excel

Sorting out debits and credits

Some systems add DR and CR to the end of numbers when they export into Excel. This renders the values useless for normal calculations. You can use data cleansing techniques to remove the characters using formulas or Power Query. There is one function however that can perform calculations on these types of entries.