A not so advanced, but useful, Excel tip.

A friend of mine was faced with this problem when preparing an offer to a customer, and asked me to save him from doing tedious manual entries for some 50 products.


He had to make this from his own Product Master File

SKU SKU Description Width mm Depth   mm Height  mm
Prod1 Funny Stuff 158 210 242
Prod2 Funnier Stuff 281 264 277
Prod3 Funniest Stuff 256 224 252


look like this in the customer supplied Offer Form

SKU SKU Description Pack Size
Prod1 Funny Stuff 158x210x210x242
Prod2 Funnier Stuff 281x264x264x277
Prod3 Funniest Stuff 256x224x224x252


I suggested the following solution based on the KISS-principle.

Step 1 – Concatenate to create a column that matches the customer “Pack Size” format.


You can lock the columns by adding $ before the letters, example $C2 to protect your formula. If the order of measurements in the Product Master file is different from the customer supplied Offer Form just adjust the order in your CONCATENATE.

This column should be in the Product Master file to the right of the SKU id column.

Let’s see what we have done:

CONCATENATE is a formula for combining the content of one cell with the content of one or more other cells. The & is the workhorse here, just like + is for adding. The cool thing is that & let’s you put more than just the contents of another cell into your destination cell. This means that as long as you use the double quotation marks “ ”around it you can add any string of letters or numbers you need, or just add a space if you are making a list of names for example.

We combined Cell C2 with an x and cell D2, added an x and cell E2. NOTE! You have to use all these & to make it work, and there is no technical limit to the CONCATENATE string, if needed it could be a mile long.

Now we have a column in the requested format, time to get it into the customer supplied Offer Form!

Step 2 – Vlookup the Pack Size to get it into the Offer Form.


What we tell Excel to do here is to search for

Value in column A for the row number of the current cell in sheet 1, Columns A through C, and then return the contents from the 3rd column and the cell in the row that matches the Column A value in the Offer Form. Building it this way will make the formulas perform their tasks for every row you need to submit. At times Excel tries to lock cells with this type of $A$2 behaviour – don’t accept that, your row numbers must be flexible, so remove the $ in front of the row number!

Step 3 – making the Offer Form portable.


It is easy to forget, but absolutely necessary to do this when your source file for the VLOOKUP is not included in the send-out to the recipient. If you don’t, your “Pack Size” column will only display ######### or !ERROR!

Hope you enjoyed this simple tip, my inspiration for this was Chandoo at http://chandoo.org/ where I learned how to see the patterns that can be used with many of the functions available in Excel, like the &”x”&.


Posted in Power BI | Leave a comment

Small investment, low maintenance, high yield = Power Pivot

1. Small investment

The vast majority of businesses in the world already use Excel so the big investment has already occurred.

a. Companies still using Office 2007 or older are nearing the time when an upgrade to a newer version will be needed anyway and including Power Pivot will not make it massively more expensive. Choose Office 2013 Professional +, or if you are ready for the cloud, choose Office 365 +.

b. Companies using Office 2010 Professional already have full access to Power Pivot and Power Query, just download and install.

c. You should also invest in some training for those who will be building your data models.

Continue reading

Posted in Power BI | Leave a comment

Perfect Reports and Dashboards

Creating reports can be a lot of fun, but there are some very important points to take into account.

Continue reading

Posted in Power BI | Leave a comment

More Power Query–using the fabulous .csv-file

There are lots of fancy data sources that Power Query can grab data from, like the Web, Databases, Excel



and “Other Sources”


but the day to day, bread and butter problem solver is most likely to be .csv since almost every self respecting application around has the option to export .csv-files.

Continue reading

Posted in Power BI, Power Query | Tagged , , | Leave a comment

Power BI–the awesome expansion of Excel!

Excel has been the dominant tool for analysis and reporting for a long time now, updates and improvements have been “software normal” until recently. However, in a rare “hop,skip and jump” Microsoft surprised us with some exciting changes.

In Office 2010

1. Good old Pivot Tables went from “user hostile” to “user friendly”

2. In the background Power Pivot was released as a free add-in

With Office 2013, and 365+, the Power BI suite developed and this is where new ground is being broken.

Power BI consists of 4 parts:

Power Query – for grabbing and loading data from a very large number of possible data sources

Power Pivot – for modeling and analysis of the data

Power View – for visualizing and reporting

Power Map – for geographical visualization

Continue reading

Posted in Power BI, Power Query | Tagged , , , , | Leave a comment

(It’s not [WHAT] you know), (it’s [WHO] you know)!

Never have so few =(1 person[me]) had so many =(4 musketeers[below links]) to thank for so much BIntelligence.

(Paraphrased from the fat guy with the fat cigar)

In order to expand your toolbox you need friends, including friends who might not even know that they are your friends. I have found some fantastic sources for learning Power BI by reading what these guys are writing, and I want to take this opportunity to thank them for unknowingly being my friends.


Hey guys, you are my “Who” helping me to get the “What”:


Rob Collie http://www.powerpivotpro.com/ I start out every day checking for new posts and new insights.

Matt Allington http://exceleratorcbs.com.au/ The new kid on the block, but already a great contributor to my learning. I recommend his series on the TechEd days in Houston.

Bill Jelen http://www.mrexcel.com/ This is where it all started for me, both with Power Pivot and with Excel in general. Bought a DVD on Excel 2007, bought a book, bought a DVD on Excel 2010 and I expect to buy more.

Chandoo http://chandoo.org/wp/ Power Pivot U was great and Advanced Power Pivot U was even better. Chandoo also keeps on sending out exciting Excel challenges which keeps us on our Excel-toes.

Ler I will always maintain links to you on my blog!

Posted in Power BI | Leave a comment

Business Intelligence förr och snart.


Under många år har jag arbetat med att ladda in .txt-filer i Excel,omvandla text till siffror samt att bygga CONCATENATE, VLOOKUP, INDEX, MATCH och SUMIF formler. Detta är tidskrävande och inte på något vis foolproof, en missad eller felaktig CONCATENATE eller en cell som hamnat utanför formelområdet kan generera ett inkorrekt resultat och under tidspress är det lätt hänt att den egna valideringen inte är hundraprocentig. Power BI löser dessa problem galant! Efter att ha upptäckt Power Pivot med kompisarna Power Query och Power View har jag nu ägnat det senaste halvåret åt att studera och lära mig dessa helt fantastiska verktyg.

Continue reading

Posted in Power BI | Leave a comment