Author: Debra Dalgleish

Excel Roundup 20140922

If you love Jambalaya, you’ll appreciate the Excel Jambalaya Calculator that Jay Crush has worked on for several years. He just released version 7.0, and you can read about the calculator, and get the file download link, in Judy Walker’s article in the New Orleans Times-Picayune.

I downloaded the file, and you can see a screen shot below. Now, if 150 people drop by for dinner, I’ll be able to cook up a giant batch! Please call ahead, to let me know if you’ll want a full plate, or just an 8 oz bowl.

excel jambalaya calculator

Continue reading “Excel Roundup 20140922”

Worksheet Combo Box Problem in Excel 2013

On of my favourite Excel tricks is to show a combo box, when you click a cell that has a drop down list. The combo box can have larger font, and it autocompletes, so it’s easier for people to use.

There is only one ActiveX combo box on the worksheet, and it is hidden until you click on a drop down cell. When it appears, it shows the data validation list for the selected cell.

combo box with drop down list

Continue reading “Worksheet Combo Box Problem in Excel 2013”

Excel Roundup 20140915

Are you brave enough to enter the ModelOff competition, that tests your financial modeling skills in Excel?

To help you decide if you’d like to enter, there are workbooks with questions from previous years, available for download. For example, can you find and fix the errors in a complex workbook?

ModelOff question find errors

Continue reading “Excel Roundup 20140915”

Clear Dependent Drop Down Cells

You can use data validation to create a drop down list on an Excel worksheet. For example, show a list of countries, if you click a cell in column B.

After you’ve set up that first list, you can make another list, in column C, to show the cities for the selected country.

Continue reading “Clear Dependent Drop Down Cells”

Excel Roundup 20140908

If you have Excel, do you even need any other programs? In this week’s video, David Buchanan, from the Chef’s Resources website, shows how to use an Excel workbook to plan every step in an event, from the idea phase, to prep and ordering, to recipes. I like his description of this process as “mental mise en place” worksheets, to help you organize tasks.

Continue reading “Excel Roundup 20140908”

Excel VBA Problem With Step Into F8

After you record or write a macro in Excel, you can run the macro, or go slowly through it, line by line, to see if it is working correctly. I use the F8 key, in the Visual Basic Editor, if I’m troubleshooting a macro, to see where something is going wrong. You can use also use the Step Into command, in the VBE’s Debug menu.

f8problemfix04

However, for the past few months, something was going wrong with the “Step Into” command in Excel 2010, whether I used the menu, or the F8 key.

Continue reading “Excel VBA Problem With Step Into F8”

Excel Roundup 20140825

There is a free app available for Excel (2013 or Online version), that lets you create 8 different chart types, including some that aren’t standard Excel charts, such as box plots. The app is called XLMiner Data Visualization, and it’s created by Frontline Systems, which also makes the Solver add-in.

If you log in to your Microsoft account, you can download and install the app from the Microsoft Store. Then select data, and the chart appears in the app’s window. In the screen shot below, you can see the box plot chart that I created, in just a few seconds.

The free version of XLMiner lets you do the basics, and if you want more features, you can upgrade to the Pro version. It costs $995, about the same as Tableau Desktop. And if you want quick, fancy charts, at a much lower cost, take a look at Jon Peltier’s charting utility.

xlminer free app contexturesblog.com/

Continue reading “Excel Roundup 20140825”

Create Random Text in Excel

Last week, I was creating an Excel file with sample data, to use for a few experiments. But don’t worry, they weren’t mad-scientist-type experiments – I was doing Power Pivot experiments, and needed some data to play with.

I needed 2 types of data:

  • Numbers: sample test scores in one column
  • Text: random Region names and Gender in other columns.

Create random text in Excel http://blog.contextures.com/

Continue reading “Create Random Text in Excel”

Excel Roundup 20140811

Does Ed Bott include your favorites in his list of 6 Excel power tips?

He included one of my favourites – Paste As Values. However, I usually Paste Values by dragging with the right-mouse button pressed, instead of using a keyboard shortcut. You can see that technique in the very short video below. Do you use the mouse or keyboard for Paste As Values?

Or watch on YouTube: Change Excel Formulas to Values With Mouse

Continue reading “Excel Roundup 20140811”

Top 5 Items in Advanced Filter List

When you try to use the Top 10 filter, on a list that already has some filters applied, the results probably won’t be what you want. The Top 10 feature ignores the filters on other columns, and just returns values that are in the overall Top 10.

Recently, I showed a workaround for that problem in this blog post: Top Ten Values in Filtered Rows. In that example, I added a new column, and used the SUBTOTAL function to show the value, then filtered that new column. Hidden rows would have a value of zero, thanks to the SUBTOTAL function, so they wouldn’t be included in the ranking.

autofiltertop10filtered05

Continue reading “Top 5 Items in Advanced Filter List”