Category Archives for Formulas
“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 readingChange 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 readingNumbering 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 readingTruncating 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 readingA 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 readingA High Ranking Function
Excel really doesn’t have an explicit “between function” to allow users, given a set of ranges, to find in what range a selected number falls. The obvious and messy workaround is to use a bunch of nested IFs, which is tedious and error-prone. That’s where the RANK function comes in. Here’s a breakdown. RANK(number,ref,[order])number – […]
Continue reading