Category Archives for Advanced Excel
Highlighted Timeline Chart in Excel Without VBA: Raw and Uncut
IntroductionI built a chart that let’s you highlight a series from a smaller chart (take a look at that the chart under the timeline) and show it in more detail on a larger chart. Yahoo! Finance used to have something similar, although I can’t find it anymore. But anyone who has looked up stocks online […]
Continue reading
Make Your Excel Spreadsheet into a Bitmap Image With This Excel Magic Trick
Turn any image into an Excel file?Ever think about turning your face or that picture of your neighbor’s dog into an Excel file? Well, even if you haven’t, you now can! Years ago I created this little utility that can turn any image into an Excel file. Click here to download it for yourself.How do we […]
Continue reading
Excel Data Visualization: Presidential Approval Ratings with Slicers & Power Query – Chart Tricks
No matter where you fall on the political spectrum, you’ll love the chart I’ve created, which you can adapt and use in your own work. The instructions are a bit complicated, so rather than go through every element with figures, I’ve written up what I would do assuming you’re watching and following along in the […]
Continue reading
Advanced Excel Tutorial – VLOOKUP() Speed in Excel | Excel TV, Episode 54
CORRECTIONSeveral people pushed back on the video I release this morning and I want to thank them (and in particular Wim Gielis). This caused me to open my laptop on the train while on the way to a client’s office (so I could see if I was wrong … which I was!). When I realized […]
Continue reading“Everyday” Random Shuffling with Formulas
In this article, we’ll talk about how to complete random shuffling with only formulas. Shuffling methods via VBA or a dime a dozen. But it’s much harder to perform a formula-based random shuffle since formulas don’t really allow for iteration. Non-VBA methods therefore require we think about these problems differently (what I describe in Dashboards […]
Continue readingA method for quickly importing and working with CSV files (part 2)
This is part 2 in which I explain how I solved the problem. Make sure to read the previous blog posts. Taken together, these show I though through and ultimately solved the problem. Query Tables to the Rescue! To solve this problem, I stumbled on something I rarely use, and that’s our good ol’ friend […]
Continue readingA method for quickly importing and working with CSV files (part 1)
So many folks have asked me to write more on my coding style. So here it is: I’ve developed a two-parter blog post to present my thinking style and how I ultimately solved the problem. The Problem I have a client with several CSV files that must be checked and validated for certain data specifications. […]
Continue readingDeveloping a JSON Excel Add-In With VB.NET
Written by Bjoern Stiel Add-Ins are a great way to increase Excel’s firepower. Add-Ins not only add additional functionality but are also an ideal vehicle for distributing your custom functions across multiple workbooks and users. If writing a new Add-In is on your to-do list anyway, this is the perfect opportunity to look beyond VBA. VBA has […]
Continue readingA VBA Coding Manitesto (Part 2)
In the first part of my manifesto, I outlined why I think we should really change the way we code. The important takeaways of that article were: (1) the Hungarian Notation coding style is old and should no longer be preferred; (2) we cannot simply code for ourselves anymore; and (3) the implications of the […]
Continue readingA VBA Coding Manifesto (Part 1)
A little while ago, I made a blog post on why I think we no longer need Hungarian Notation. Truthfully, it’s not the first time I’ve advocated for doing something against the grain. My blog on the principles for app/game development with Excel argues you should keep most of your procedures in the sheet object […]
Continue readingIt’s time to say “Goodbye,” to Hungarian Notation
Most of us code using the Hungarian notation style when writing VBA code. Basically, that’s when you prefix an abbreviated description of a variables’ type or class in front of its name. For example, the ‘d’ below is a prefix for a variable of type double. You get the idea. 1: Dim dDouble As Double […]
Continue readingIt’s time to say “Goodbye,” to Hungarian Notation
Most of us code using the Hungarian notation style when writing VBA code. Basically, that’s when you prefix an abbreviated description of a variables’ type or class in front of its name. For example, the ‘d’ below is a prefix for a variable of type double. You get the idea. 1: Dim dDouble As Double […]
Continue readingI’m coming to San Francisco!
That’s right, if you’re in the San Francisco bay area, don’t miss an exciting Excel workshop on March 20th, 2014. I’m now a team member of Excel and Access LLC, a full-service Excel and Access training and development consultancy. We’re doing an Excel workshop at the eMetrics Summit and we’d love it if you joined […]
Continue readingI’m coming to San Francisco!
That’s right, if you’re in the San Francisco bay area, don’t miss an exciting Excel workshop on March 20th, 2014. I’m now a team member of Excel and Access LLC, a full-service Excel and Access training and development consultancy. We’re doing an Excel workshop at the eMetrics Summit and we’d love it if you joined […]
Continue readingEffective means of display in Excel
There are many great articles and tutorials on how to build interactive data displays in Excel. However, there are few articles on how best to effectively display data in Excel. I think this is an important subject that every Excel user should at least think about. On the other hand, I personally don’t like any […]
Continue reading