Advanced Excel Archives - Excel TV
Excel TV
Share The LOVE

Category Archives for Advanced Excel

“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 reading

A 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 reading

A 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 reading

Developing 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 reading

A 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 reading

A 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 reading

It’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 reading

It’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 reading

I’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 reading

I’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 reading

Effective 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

Your First Maze

Disclaimer: The following tutorial requires Conditional Formatting which is a feature of Excel 2010 and greater. If you are using an older version of Excel, please note that formatting will not work correctly for you. This article is part of a series called “VBA4Play,” written in conjunction with Excel game developer, Cary Walkin, for his […]

Continue reading

Handling Excel Rollover Popups

Various people have written to me asking what they should do about popups generated by the rollover method that don’t go away on their own. Essentially, the rollover method provides for an “on mouse over” style routine but not for a “on mouse out.” The “moat” method One method to address this is to create […]

Continue reading

A Dynamically Linked Checkbox Matrix with Microsoft Excel

Edit: sorry for some of the squashed images you see in this and other posts. I’m still working on my layout. In the meantime, click on an image to view it in full if you’re having trouble. Today, I want to show you what I liked to call a “ dynamically linked matrix.” I’ll post […]

Continue reading

The Excel Lab: Soundex Search

Every once in a while, I have a spreadsheet that sits in my ‘lab’ for way too long. At a certain point, I have to admit that I’m never going to finish it completely or do a full blog post on it. I’ve decided just to release one such spreadsheet as is. Perhaps you will […]

Continue reading
1 2 3
>