Excel Roundup 20141006

In this video, Annie Cushing shows how to create a scrolling table, by using the INDEX function and a scroll bar form control. This technique would work nicely in a dashboard, where space is limited.

For more dashboard tips, take a look at Mynda Treacy’s Excel Dashboard course which has just opened for registration, until Oct. 23rd. Details are in the Announcements section, below.

Continue reading "Excel Roundup 20141006"

Add Your Own Icon to an Excel Custom Ribbon Tab

Are you building custom tabs for your workbooks yet? Last year, I wrote a couple of articles about custom tabs:

Continue reading "Add Your Own Icon to an Excel Custom Ribbon Tab"

Excel Roundup 20140929

I’ve seen lots of non-spreadsheet items with “Excel” in their names, such as gum, and bowling gloves, and hand dryers.

However, this is the first time that I’ve seen a product named after a pivot table. Do you think that they help you analyze your data more efficiently? I wonder if they have a women’s version too.

Kenneth Cole REACTION Men's Pivot Table LE Oxford

Contextures Posts

Here’s what I posted recently:

Other Excel Articles

Here are a few of the Excel articles that I read recently, that you might find useful:

  • To enhance your dashboard, use the tips from Gašper Kamenšek, to show the Top 5 or Bottom 5 results, by clicking a radio button.
  • Bill Jelen (Mr. Excel) shows how to look for 3 specific names, in adjacent cells of a lookup table, by using an array formula that I hadn’t seen before. Watch the video from 1:15 to 2:45, if you’re pressed for time.

Excel Announcements

Here are some upcoming events, courses, recently published books, and other new items, related to Excel.

Practical Numerical Methods for Chemical Engineers: Using Excel with VBA by Richard A. Davis

“This latest 3rd edition continues to expand the breadth of practical numerical methods with over 100 VBA macros for extending Excel's power for engineering & scientific analysis. Engineers & scientists will find the enhanced coverage of computational tools applicable to a variety of problems in their own disciplines.”

   
Financial Modeling for Business Owners and Entrepreneurs, by Tom Y. Sawyer.

"You’ll learn to create operational and financial models in Excel that describe the workings of your company in quantitative terms."

   
jankarelpieterse Excel VBA for Financials, 2-day course led by Excel experts, Jan Karel Pieterse and Tony de Jonker

This Dutch course is specifically aimed at the Finance professional. It has no frills, and no technical mumbo-jumbo – just the VBA you need to achieve more success in your daily job.

Date: November 19 and December 3, 2014

Location: Utrecht (Netherlands)

   
Beginning Power BI with Excel 2013, by Dan Clark

"Guides you step by step through the process of analyzing and visualizing your data. Daniel R. Clark, an expert in BI training and a regular speaker on these topics, takes you through each tool in turn, using hands-on activities to consolidate what you’ve learned in each chapter."

   
modeloff2014 Registration is open for this year's ModelOff competition.

Test your financial modeling skills against top modelers from around the world. Participants from over 100 countries progress through two Online Qualification Rounds with a Live Finals Event held in New York.

  • Round 1 (online) is Oct. 25th, and Round 2 (online) is Nov. 8th.
  • The Top 16 finalists will compete in New York in December.

Share Your Events and Articles

If you read or wrote any other interesting Excel articles recently, or have upcoming Excel events, please share a link in the comments below, with a brief description. Thanks!

_____________________

Links to Recent Excel Books on Amazon.com

101excelformulas   excelfunctionsheld01  

_________________________________

Show Color With Conditional Formatting

I like to color data entry cells, so they’re easy to spot on an Excel worksheet. If you know that you should type a value in every blue cell, it’s quicker, and safer, to fill in a worksheet.

Recently, I was asked how to color a specific number of data entry cells, based on a number that someone selected from a drop down list. So, instead of manually coloring the data entry cells, I used conditional formatting to color them.

In the screen shot below, 3 was selected in cell C2, so 3 data entry cells are blue, in cells C5:C7.

Continue reading "Show Color With Conditional Formatting"

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"

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"