Excel Unplugged

Amsterdam Excel Summit 2016 report

This post is a tribute to the event, that is one of a kind in Europe, and gives Excel professionals a rare opportunity to meet brilliant people, that have dedicated their lives to Excel and have a knowledge-base to prove it. Keep in mind that this kind of access is rarely given to “near mortals” that love Excel. To give you a taste, I’m going to casually include three great tricks in Excel that are the tip of the “iceberg of knowledge”, from which the participants had the opportunity to draw from, during the two day event. I will also try to give you a taste of the MVPs that were there.

So the Amsterdam Excel Summit is a sort of Extension of the Amsterdam Excel MVP Summit that took place two days before the Excel Summit. So the MVP’s just prolong their stay in Amsterdam for two more days with the noble goal of enlightening the simpletonsJ. But still an event like this would never see the light of day if it wasn’t for the two Dutch Microsoft MVPs called Jan Karel Pieterse and Tony De Jonker. A big thank you to them for giving the non-MVP crowd a chance to meet this inspiring group of MVPs. And if an event takes place in Amsterdam, that’s not too bad either J

Amsterdam Excel Summit 2016 report

This year the event did differ from last year a bit. The first day was a regular summit with two tracks of sessions where the only real downside was that, if you went to one, you had to miss the other and sometimes, that really was a Sophie’s choice. Day two was designed as a workshop and in my opinion that was a touch of brilliance. Just imagine attending workshops led by the MVP’s – if ever there was a fountain of Excel knowledge, that was it. And in contrast to day one, only one track of workshops, so no Sophie’s choices to be made J

Day one

I couldn’t believe 13 months have passed since the 2015 Amsterdam summit and certainly you would never had guessed it if you saw all the participants coming together again. It was as if the last year’s event finished one week ago. After the initial greetings, we dove right into Excel fun with Yigal Edery, a Group Program Manager at Microsoft that gave a quick tour of Excel and the current roadmap for Excel. He showed some great Power BI and Excel connectivity tools, The Power BI Add in and a great technique to make parts of Excel interactive in your Power BI reports which gave me a great idea for an article that I’m guessing I’ll publish in about two weeks.

Next up was Jon Peltier. The best charting guy out there. His Charting tools really extend Excel’s abilities when it comes to charts and even gives you some new general tools in Excel. The session was Excel Charts and VBA and it was great to see the master at work. And it was funny how this basic session actually solved a problem with my non-working chart in an Add-Inn I made many years ago. I wish I’d known Jon back thenJ. This was the first session John did on that day, but not the last… As the last session of the day, he talked about creating your own Add-Ins and customizing the Excel ribbons or just creating them out of the box for your own Add Ins.

He was followed by Henk Vlootman, who talked about Power Pivot, dimension tables and the use of slicers in your Data Model. He gave me another great idea about disconnected slicers and the use of Switch function to show many different measures within one.

Tips and Tricks session closed the day in style. It was a joint effort of Jan Karel Pieterse, Tony De Jonker and the king of tips and tricks Bob Umlas (the title is informal J ). The number of tricks was far too big to write them all here. Listening to Bob one even gets the feeling that if he had 8 hrs, he would still have some extra tips up his sleeve J. As promised, I will give you one carefully selected trick from each of them.

Sum one cell over multiple sheets (Jan Karel Pieterse)

This is one of those tricks, where the trick build-up takes ten times longer than the solution itself. Here’s our workbook.

Amsterdam Excel Summit 2016 report

The key point is that we have a few Sheets with two things in common. All Sheet names are three characters long and cell A1 on each of them contains a number. Our goal is to SUM up those numbers (all A1 cells on all Sheets that meet those criteria). Before we write and understand the magic formula, the use of wildcards in formulas should be clear to the reader. ? and * are the ones we’re going to focus on. Here’s what they do:

? – replace exactly one character

* – replace any number of characters.

At this point we have all the tools to write the magic formula, which is…

=SUM(‘???’!A1)

(The apostrophes are important!)

Amsterdam Excel Summit 2016 report

This formula can be translated as follows: “SUM up all A1 Cells through all Sheets whose name is exactly three characters in length”. When you press Enter Excel will immediately reshape that formula into a properly formed formula.

Amsterdam Excel Summit 2016 report

At this point you might think to yourself: “Ok, so it works if I have Sheets with names of the same length and those sheets are placed together since the final formula is Jan:Jul!A1. Here’s the situation where the Sheets are not all next to each other, and as you can see, Excel can still reshape the formula.

Amsterdam Excel Summit 2016 report

The second point can be managed by the * wildcard. Imagine having sheets called Project Home, Project School, Project Work, All together and you want to sum up A1 over all Sheets where the name of the sheet contains “Project”. I’m guessing you already know the answer, but here it is anyway.

=SUM(‘Project*’!A1)

(Again, the apostrophe is important!)

Calculate MINimum Value within specific column of desired Named Ranges (Tony De Jonker)

Another big build up J. Imagine having a range of named ranges by rows. So B1:G1 is a range named January, B2:G2 February and so on up to 6th row and range called June.

Amsterdam Excel Summit 2016 report

Now in Cell B7 you want to get a minimum of cells from all six named ranges that occupy column B. A very optimistic formula in B7 would be something like this:

=MIN(January,February,March,April,May,June)

Amsterdam Excel Summit 2016 report

But that is obviously not the right result, since the result comes from G5 and not from column B. Now here’s what adding one character can do:

Amsterdam Excel Summit 2016 report

=MIN(+January,+February,+March,+April,+May,+June)

A little “positive” attitude goes a long way J

Making Excel Speak (Bob Umlas)

I’m guessing you already know about Excel’s Speak Cells command. This trick gives you an even faster alternative to using the same command. It’s done by openning the VBA editor (Alt + F11) and by writing the following command in the Immediate Window (if you don’t see the immediate window, you can turn it on with the Ctrl + G keyboard shortcut):

Application.Speech.Speak(“Excel Unplugged”)

Or if you select some cells with content, you can write

Application.Speech.Speak Selection

Amsterdam Excel Summit 2016 report

Ain’t that sweet J

Day two

Day two was Workshop day, and the topic of the day was Charts and Data visualisation. Workshops were presented by the synonym for charting in Excel Jon Peltier, the only non MVP to take the stage David Hoppe (imagine how good he must be, to be given this opportunity), and Tony De jonker who showed us how to create interactive charts that were made to impress!

I already talked about Jon and Tony, but now I will take some time and talk about David Hoppe. In last year’s report I talked about him as a dashboarding wizard since he took second place in Chandoo’s State migration dashboard challenge but upon speaking to him, you could see, that he has ideas way bigger than that. Back then I told him, that he should launch his own website with a blog and one year later during this year’s Amsterdam Excel Summit he did just that. He launched his site called excelusive.

Amsterdam Excel Summit 2016 report

On his website you can find his blog, but more than that, he has an Add In on the way that will make complex processes in excel automated. There are still a few kinks but in my opinion it’s one of the most innovative things I’ve seen in Excel in quite some time. Keep an eye out for David because great things are yet to come.

And with that in mind, thanks again to Jan Karel and Tony. I can’t wait for Amsterdam Excel Summit 2017 which will take place in May. For the exact date, keep checking this page. I think that the workshop day was a “hole in one” and I think it’s on its way to becoming a classic. When new things and sessions coming next year, sky is the limit!