SUMIFS Rock!

In the spring of 2013, we took a look at the remarkably potent SUMIFS function.  It drew so much interest, a review of this outstanding tool is in order.

Creating reports that go on and on over dozens of worksheets is one of the most Egregious Ruts that Excel users sometimes find themselves.   On the other hand, the use of Excel’s many fine tools (such as Sumproduct, DFunctions, or Vlookup) can result in highly effective and professional Interactive Reports.  These reports are much more versatile and tend to be operated with more efficacy by the end user.

One often overlooked, but ultimately powerful and easy to use function, is SUMIFS.  Unlike its little cousin, SUMIF, the SUMIFS function allows you to conditionally sum values with Multiple Conditions, rather than just one.  This, of course, makes it a noble choice for interactive report.

The Syntax for SUMIFS is as follows:

SUMIFS(SumRange, [CriteriaRange1, Criteria1], [CriteriaRange2, Criteria2], …)

Let’s look at an example:  Assume that you have a table/database that has multiple entries of Sales for Months and Weeks. Using a SUMIFS formula, along with dropdown boxes for the Month and Week, you can make an Interactive Report that is contingent on the parameters you choose. Note that the Amount in the report on the right of the illustration below Sums up any value which has September for the month and Week 1 for the week.  Pretty Cool!

SUMIFS; Another powerful Excel tool that can make you look like a Rockstar!  Give it a try and find out what you might have been missing.

Behind the Scenes

Any good Excel guru wants to have complete control over what is going on in his or her workbook.  Knowing what is going on Behind the Scenes is crucial for maintaining order and value in your Excel creations.

There are times, of course, when you are not the original author of the worksheet, and having some good, basic investigation techniques are invaluable.  With the following methods, you can quickly transform yourself into an Excel Sherlock Holmes!

Here is How to Get a Look behind the Scenes:

1) Do I have any Formulas Operating in My Workbook?

Well, you could click on each cell to reveal the formula in the Formula Bar, but who wants to do that?!?  You can instantly reveal all of this information in your entire worksheet with the Keyboard Shortcut:

Ctrl + ~

This elementary keyboard combination unveils the formulas in All of your cells.  You can then adjust your Sherlock hat and determine if there is a flaw in their construction or if everything is Spot On!

2)   Do You Need an In-Depth Review of Precedents and Dependents?

If you need Sleuthing Power in seeing what your data is doing and where it comes from, look no further than the Formula Auditing group on the Formulas tab. There you will find Trace Precedents, Trace Dependents, (and even Show Formulas). The information is returned in easily-understood graphics that help you make sense of the workbook you inherited from old Moriarty fellow.

 

If you want a handy shortcut for revealing Direct Dependents, click the keyboard as follows:

 

                                         Ctrl + [ 

These two fundamental investigative tools can Wow the people around you.  You may even be tempted to say, “Elementary my dear Watson”…

Keeping Your Reports Up-to-Date

Keeping your Excel reports current up-to-the-minute or even up-to-date can be challenging.  There are several ways of approaching this, of course, and we will explore a couple of the key best practices to do this.

First of all, if you can store (not just analyze) your data in Excel, you can use some extremely powerful functions to mine your in-app database.  If you have your database and reports contained within one Excel workbook, you can use Boolean functions (i.e. SUMPRODUCT is the most straightforward…) to extract the information you want from your data.  Information is, of course, what any good analyst wants, as all the data in the world serves no useful purpose unless information is extracted from it…

By anticipating the addition of future data in the named ranges in your Excel database, the reports will automatically update as soon as any new date is added.  Simply name the ranges to include the currently empty records area that will be developed as future data is entered.

But, let’s say you do not have your data stored in your Excel workbook (e.g. SQL, Access, SAP, etc.).  Let’s also say that every time you download a data update from the database source you then have to manipulate it in order to have it produce the information you desire in Excel.  What can you do?

Having to do repetitive manipulation of data every time you need to update your reports can obviously deter you from keeping your data.  One excellent solution (which can make you look like a Rock Star in the process) is to Record a Macro of your repetitive steps and link it to a simple button in your report workbook.  Then whenever you import fresh data, you press the button (in cavalier style, of course) and Badda Bing, your report is Up-to-Date!

Keeping your Excel reports current up-to-the-minute or even up-to-date can be a Snap with a couple of compelling tricks!

PDF to Excel

As most any Excel user knows, it is a Snap to convert Excel to PDF.  All you have to do is use the Save as type dialogue box, and save your file as PDF (*pdf).  Easy!
 
But what about when you want to do the reverse?  That is, save a PDF as an Excel file.  Well, that is not quite so easy…

The Good News is that there a several niche software solutions available that handle saving PDF files as Excel worksheets very well.  They all handle some basic functions that the Excel user may want, including:

·        Retaining row and column structure

·        Convert PDF tables from scanned and image PDF into Excel

·        Perform batch conversions of multiple PDFs

·        Create and save templates for repeat conversions

Most also offer some advanced custom excel conversion features that some users may find advantageous.  These features vary from software to software, so if you have special needs in your conversions, it certainly is wise to compare before you buy.  As you can see by the following list of major PDF to Excel software, prices are similar:

·        Baltsoft PDF Converter - $99.00

·        Cogniview PDF2XL - $97.00

·        Investintech’s Able2Exract PDF Converter 8 - $99.95

·        Nitro - $139.95

                                                         

Some also offer Free Online Converters that can be useful for more limited applications.  If your needs are limited, I would definitely suggest checking out a free solution first.  If your needs are frequent and more complex, however, this type of software can Save you Time and Money!

PDF to Excel.  It may be just what you need!

.

Intersections

The Intersect Operator is yet another somewhat obscure, but highly useful Excel tools.  Many of the ways to glean information from Excel tables involve cumbersome formulas that can take an Excel guru a considerable amount of time out of his or her busy day.  The Intersect Operator, is not only Powerful and Versatile, it is also quite Simple to use!

The function of this handy tool is to use the vertical and horizontal ranges in a cross-tab Table or Database, and find the value at the Intersection (ergo, the Intersect Operator…).  The syntax (Special Note: Be sure to use No brackets or commas…) is simply:

= (RangeName1 RangeName2)

For the Ranges, you can use the generic names such as =(C2:C42 A10:K10).  Whereas this gets the job done and may be advantageous in limited circumstances, using Named Ranges is much more effective.

As a reminder, you can very quickly name all of the ranges in your database by selecting all cells (use Ctrl + A), and the click Ctrl + Shift + F3. This will bring up the Create Names dialogue box as shown below.  Just click OK, and Bamm!  Named Ranges!

Once you have your ranges Named, you can then (as with the example below where we are finding the 3^rd Quarter Sales for Los Angeles) find a value with the modest function (in Cell E5 in our example):

=(Los_Angeles Quarter3)  Note: Be sure to include the space between the column title and the row title.

 

There are, of course, other ways of obtaining this information in Excel, but the Intersect Operator is certainly a worthy addition to any Excel user’s tool belt.

New Excel Features on iPad

Back in April we talked about the advent of Excel, (along with Word and PowerPoint), on iPad.  For those of us who have become heavily dependent on iPads, this was great news!

As with any software product there is, of course, room for improvement.  Many of us submitted suggestions for Additional Features and Tools for this convenient addition to our Excel capabilities, and as of a few days ago, Microsoft has come out with the new Excel 1.1 for iPad.

So, what is new in Excel for iPads?  Here are some of what I consider to be the Best of the Best:

·        Flick to Select: This is remarkably easy and very handy.  Simply flick a cell’s selection handle in any direction to quickly select all the data in a row or column.  Very cool!

·        Use an External Keyboard:  Although I am not a big fan of using an external keyboard with an iPad, (it is a tablet, after all…), it is sometimes useful to do so.  Now you use the same keys to input data and move around a worksheet just as you would on your PC or Mac.  Good stuff!

·        Send Files as PDFs: You can now send Excel files as PDFs. 

·        More Printing Options: More control over the layout when printing your workbooks is a nice addition.

·        Work with PivotTables: You can now interact with PivotTables that have been created on a PC or Mac.  Great new feature for all of us geeks!

There are some additional new features, but the above are, in my opinion, the most utile.  Excel on iPad is getting More Powerful and Easier to use!  If you have an iPad, give it a try!

AND, OR, WHAT?

Logic Functions can often be an Excel user’s best friend (maybe not their BFF, but a really good friend nonetheless…).  The Major Logic Functions are: AND, IF, IFERROR, and OR.

A brief study of the AND and OR functions is a worthy exercise for any Excel user who never (or seldom) uses these valuable logic functions.

As with most Logic Functions, the AND and OR functions return a TRUE or FALSE result.  When used with other logic functions such as IF or IFERROR, they can be tremendous tools for extracting information from your data. 

 

Inclusive and Exclusive:

• ·         As shown in the syntaxes below, the OR function is very Inclusive. If Any of the Conditions are True, then the OR function will return a True result.
• ·         The AND function, on the other hand is highly Exclusive. If Any of the conditions are False, then the AND function will return a False result (All of the conditions must be True to return a True result).

Syntaxes:

• ·         OR function syntax: OR(Condition1, Condition2, Condition3, etc)
• ·         AND function syntax: AND(Condition1, Condition2, Condition3, etc)

For example, OR(A1>20%,A2>20%,A3>20%) returns TRUE if Any of the cells referenced are over 20%.

 

In the case of the following, AND(A1>20%,A2>20%,A3>20%) returns TRUE only if All of the cells referenced are over 20%.

 

It only takes a small bit of imagination to see how these cool functions can be applied to sales, marketing, customer service, or myriad other data-hungry applications.  AND/OR – Why not!