Excel Q&A with an expert
The Excel mailbag is constantly full—no surprise, as every admin wants to become the go-to problem-solver for when the boss needs spreadsheet magic conjured. Let's take a crack at answering five great questions:
Q. I keep getting endless pound signs (#) in my cells. What am I doing wrong?
A. This usually occurs when Excel thinks you're trying to calculate something that results in negative days or negative time. It can be more a format problem than a calculation problem. If you are expecting a value representing the difference between two times or dates in a numeric value, all you have to do is clear the formats (Home, Editing, Clear, Clear Formats). If you want the result to be formatted as a time or a date, then you will need to reverse your calculation. In other words, it will need to result in a positive number.
Q. What is a circular cell reference and how can I prevent it?
A. A circular cell reference results when a formula refers back to the cell it is being typed into. For example, if you are typing the formula A1+B1+C1 into C1, you will get a circular cell reference.
Unfortunately, it's not always that straightforward. Sometimes, your formula is just A1+B1, but B1 contains a formula that refers to another formula that refers to C1. Don't despair; there are some tools to help you.
On the Formulas tab in the Formula Auditing group, you'll find the Evaluate Formula button. You can evaluate each part of the formula. When it arrives at the part that is a circular reference, it will tell you this in the dialog box and italicize the value in the Evaluation box. Now, you can use Trace Precedents to find where the offending cell is located.
Admin Pro Forum 2016 will feature skill-building sessions on the office tech you need to master to get ahead. Melissa Esquibel will lead both "MS Excel for Administrative Pros: Pivot Tables, Databases, and More" and "You're the SharePoint Administrator: Now What?" There's no better trainer anywhere to show you how to wrangle data and make it shine! See what other training will be offered!
Q. How is the new Excel Sumlfs different from SumIf?
A. SumIfs is configured almost completely in reverse from a SumIf. A SumIf begins with what range to examine, then by what criteria to examine it and concludes with what range to sum (if it's different from the first range). SumIfs starts with the range to sum and then, in pairs, a range to examine and criteria to examine it by. You can have multiple pairs of these in a SumIfs. A SumIf can only have one.
Here is a sample of each one. The first one creates a sum with the values in B2:B9, but only if the corresponding value in A2:A9 is equal to "Red.” The second one creates a sum with the values in B2:B9 if the values in A2:A9 are "Red” and if the value itself is over 10.
Q. I'm getting unpredictable results in a formula. How do I "dissect” a formula to troubleshoot?
Several great tools are available in Excel to reverse engineer formulas. Some can be found in the Formulas tab in the Formula Auditing group. Let's look at Trace Precedents.
Clicking Trace Precedents while in a cell with a formula will draw arrows that point to cells being used, if they are on the same worksheet. If they are on a different worksheet in a different workbook, Trace Precedents will draw a dashed arrow pointing to an icon, indicating cell references that are not on this worksheet. If you double-click the arrowhead or the dashed line, it will bring up the Go To dialog box, which lists all the "foreign” cell references. Select the one you want to trace, and click OK to navigate there. Clicking Remove Arrows in the same Formula Auditing group should return it to a normal look.
Admin Pro Forum 2016 has put together a lineup of trainers famous in the admin field for their ability to teach and inspire. Lucy Brazier, Joan Burge, Julie Perrine and Laura Stack have carefully chosen the topics you've requested most—and
we've chosen the most beautiful Florida setting you can imagine. Reserve your space today!
Q. How can I create a multiselect dropdown list in an Excel worksheet?
A. Data Validation works great with single selections. To allow for a multiple selection field, you need to turn on your Developer tab and insert a form field.
First, I recommend typing your selections in a different worksheet and naming the range. Then insert a List box control. To make it usable, you need to stretch it tall enough to accommodate all your selections. Then, with your list box selected, click Properties, and type your range name in Input Range. Be sure to click the Multi radio button before clicking OK.
You've learned Excel through videos, books, articles, webinars ... now come learn hands-on and face-to-face at Admin Pro Forum 2016 in Orlando, Florida. Bring your own tough questions—the immediate answers are waiting.
Moderator, Admin Pro Forum 2016
P.S. Four FREE Bonus Gifts! Admin Pro Forum 2016 attendees receive six free months of the ADMINISTRATIVE PROFESSIONAL TODAY and OFFICE TECHNOLOGY TODAY newsletters, along with members-only access to the AdminProToday.com and OfficeTechAnswerCenter.com websites — all included with your registration.
P.P.S. Your satisfaction is unconditionally guaranteed. If Admin Pro Forum 2016 fails to meet your needs, we will refund 100% of your tuition — no questions asked. Your course materials and all four FREE bonus gifts are yours to keep. You have my word.