No VBA Archives - Excel TV

Category Archives for No VBA

Microsoft Excel Visio Data Visualizer Add In for Excel: A Quick Guide

IntroductionI wanted to let you know about a really cool new add in for Microsoft Excel. This is one of those modern add-ins you can download from the Microsoft store. This add-in can allow you to easily create hierarchy type process maps and flow charts. The free version creates standalone type charts. The paid version, the […]

Continue reading

Visual Analytics in Microsoft Excel Webinar: Raw and Uncut

Get the Download FilesClick here to download the files used in this webinarWhat Happened? Last week, I recorded a webinar for BhamQuants. Unfortunately, the video did not come through well on the webinar. So I rerecorded it on my own.  Unfortunately, I didn’t really have time to edit it this week. So you get to watch it […]

Continue reading

Excel Requests – HTTP for Humans

Written by Bjoern Stiel APIs are the lifeblood of the internet, providing access to bits and bytes that make our personal and professional worlds work. For example, Quandl provide a vast amount of financial and economic data through their API, while GitHub and StackOverflow let you pull out all sort of user and repo data through their API. But how do we communicate […]

Continue reading

Auto highlighting Excel charts

On your dashboards, reports, and charts it’s a good idea to alert the user to certain types of values. In a recent post, we discussed how to alert users to certain records on tables using custom formats. We’ll build upon that here but transition to how we might achieve a similar effect on a chart. Take […]

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 reading

Custom Formats for Dashboards and Spreadsheet Applications

Conditional formats are volatile. If you have too many, you see the effects of this volatility in serious calculations slowdown. As the function guru Charles Williams puts it, “…conditional formats seem to be super-volatile: they are evaluated each time the cell that contains them is repainted on the screen, even in Manual calculation mode….” But […]

Continue reading

Another way to do bullet graphs in Excel

Those of you who know me, know I’m a huge fan of Stephen Few. (I mean, I had him autograph my copy of Information Dashboard Design!) Few is highly regarded as an expert on data visualization and dashboard design. He is the original designer of bullet graphs. Bullet graphs are not native to Excel’s chart […]

Continue reading

An Alternate Tip: Removing Blanks Rows from a List in a Spreadsheet

You were handed a spreadsheet of exported data from some legacy system. The data aren’t in one continuous list but rather blank rows appear randomly throughout. You’d really like to get rid of those blank rows. This isn’t a new problem for Excel. The most common approach to remove these blanks is to: Select the […]

Continue reading

Directly link Excel form controls to backend data with dynamic references

Form controls are great for reporting information about groups of items, like a list of programs or accounts. They are often used on Excel dashboards and reports that demand interactive capabilities. One such type of capability provides the user with a list of items to choose from. When the user makes a selection, a macro […]

Continue reading

Easy One-Way Sensitivity Analysis on Weighted Sum Models in Excel (Part 2)

Wow, it’s been a while since my last article. I’ve actually been pretty busy the last few weeks putting the finishing touches on an Excel deliverable for one of my company’s clients. We delivered the product about a week ago and things have just now started to slow. So now it’s back to blogging. If […]

Continue reading

Easy One-Way Sensitivity Analysis on Weighted Sum Models in Excel (Part 1)

This article describes what I like to call “Easy One Way Sensitivity Analysis” in Excel.  I’ve used it before in several dashboards.  If you want to try it out for yourself before reading ahead, both Healthcare dashboards on the Fun Downloads page implement my Easy One Way Sensitivity Analysis method.  Here’s a pic of one of […]

Continue reading

Change the Font Size, Color, and Style of an Excel Form Control Label

Anyone who has used a Form Control Label likely knows its limitations: you can’t increase the font-size, -color, or style.  Below, you can see that these formatting items have been “grayed out” in the Font group on the Excel Ribbon. To be sure, the Label control has received a lot of flack for these deficiencies. […]

Continue reading

Numbering Grouped Data for Pivot Tables

We’ve all had some experience with Excel’s automatic numbering.  For example, if you have a simple list and you would like to add consecutive numbers to a column next to your data, you’d simply select the cells containing your first two numbers and then drag accordingly.  Take a look at the sheet below to see […]

Continue reading

Truncating Long Text to Fit into Cells

Have you had a list of names but found your cell width too small to show every name?  Take a look at this: I had my nonexistent secretary print out this fake itinerary for me.  Nothing lines up neatly.  Look how some of my events overlap the cell boundary between columns B and C.  My 2012 End-of-world Steering […]

Continue reading

A Dynamic Dashboard for Project Durations and Costs in Excel (part 2)

In the last post, A Dynamic Dashboard for Project Durations and Costs in Excel, we talked about using VLOOKUP as means to map references to their costs.  At the end of the post, I admitted that I didn’t really like the solution.  Specifically, I didn’t like how the user had to type in each reference manually and […]

Continue reading
>