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!

.