Excel MVP, Blogger and Author Ken Puls
Ken originally appeared on Excel TV during Season 1 where we discussed training methods for Excel teachers.
Ken is the host at http://ExcelGuru.ca The site is popular for the Excel Guru forum as well as Ken blog posts. Most recently, Ken’s posts have focused on Power Query
This brings us to Ken’s most recent ventures, that we will discuss on the show.
- His new book “M is for (Data) Monkey – The Excel Pro’s Definitive Guide to Power Query
- Power Query Training website
Scroll down to the comments section and leave a question or comment for Ken.
My greatest excel challenge is always having to combine several data extracts from several various databases and creating one click solutions for our Executive Management Team. I have learned so many various techniques from the Industry Experts (Excel MVP’s) that I have become my companies biggest evangelist for Business Intelligence. My greatest accomplishment has some of the following features included:
– 3 Cascading Filter Controls (=index)
– 20 Sort Radio Buttons (=offset, =rank)
– Countless vlookups
– Smart Charts & visually pleasing Conditional Formatting Colors.
When all said and done, the spreadsheet displayed 1 worksheet and I had 15 supporting worksheets. We now have a solution that gives our C-Suite Team the ability to gauge performance for each individual entity.
I also had to ensure the solution worked in versions of Excel 2013, 2010, 2007 and dare I say 2003. Yep, we have a few individuals in the field who have older versions of excel that we must continue to support.
Please consider this my entry for the autographed copy of M is for (Data) Monkey book.
I have learned so many various techniques from the Industry Experts (Excel MVP’s) that I have become my companies biggest evangelist for Business Intelligence. Where is this information?
My most challenging endeavor in excel was the development of a computational fluid dynamic model of the air distribution within a data center and if color code if there was sufficient air being delivered to the face of server equipment. Considerations built into the model was real-time data acquisition of cooling equipment, power draws within racks, size of perforated tiles and air flow characteristics, distance from the closest piece of equipment, static pressure and many other considerations.
Result, model reflected actual distribution within 10% and actively mitigates risk due to insufficient cooling.
1. Downloading multiple extracts from a 3rd party vendor site and create dashboards using these extracts on excel using tables, charts and KPIs such that it gives executive summary as well as department level, country level, etc. information with which the end user needs to get insights. Using excel modelling, formulas and macros.
2. Make use Google Translate through macros to translate multiple field names in multiple excel files. Google will block the IP if so many requests come from a single IP. Hence, all the field names in multiple excel files are merged with demiliter and sent to google translate as a single translate. Using this feature, the end user can view the excel application in his local language provided its available in google translate.
A company interested in being acquired sent me their sales history. I don’t know what POS they had but it was a 9,000-page PDF which was terribly formatted and not easily pasted into Excel — even if you tried putting into Word first.
My boss assumed we’d have to keypunch the whole list which would literally have taken a solid working month or more. Instead I was able to use VBA to transpose the list of data into a workable table of data. But the formatting was not consistent — missing fields, etc. — so I had to add a lot of rules and criteria into the command to get it to work. It worked and saved a month’s labor!
Hi! First sorry about my bad english!
One of the most time expensive things is normalizing data after importing. Splitting and merging data, inserting new data, etc are REALLY COOL, because we have to learn all that logical thinking around formulas, but might cost a lot of time to learn and for the business too. So, Flash Fill SOLVE QUICKLY A LOT OF these situations. All other complex data transformation situations, which require time to learn logical complexity, are not so important as the power of flash fill feature.
I am somewhat of rookie but excel changed my life when I discovered I could use it to import data into my access database if I set up an excel worksheet properly. I learned that I was able to copy large amounts of data relating to laboratory results ( date, value, test typre,ranges etc) from another program an and past into word ( I’m still not sure why it would not format correctly when I try to go straight to excel but the table pasted into word easily. I then set up a workbook with macros that allows me to import the word document table into excel in the correct format ( I did this using Google to find VBA code to import word table to excel and then modified until I got the code to import the data exactly how I wanted it , then developed macros to clean up the data and convert data into data that may access database can easily recognize. When I started the job I had to enter each specimen in by hand …and this was extremely time consuming and mind numbing and error prone ..I reduced my time spent abstracting at least by at least 75 percent and the less I have to input by hand the more accurate the data is!!! I have been hooked ever since! Wow I am really a nerd!