Calculations Without the Formulas!

Some people use Excel because they LIKE numbers and math. Some use Excel because they DON’T! Nick counts on Excel to do the mathematical heavy lifting. He needs to calculate rows of data, but the thought of creating formulas and then checking the answers seems daunting. If you feel the same way, you might like the automatic calculations that Excel can do without you having to create one formula!

Imagine simply clicking on cells with numbers and the sum instantly appears! It is faster than looking for your calculator! Once it is turned on, it is always available to view on the status bar every time you select multiple cells that contain data. This is great for a quick peek to see how different salespeople, offices or products are doing.

So, how do you turn it on? Right-click on the status bar (that’s the little toolbar below the worksheet tabs that says Ready). Then, click to check the calculations you want to appear. It stays on until you turn it off in the same location.

This will simplify your year-to-date totals! Let’s say you keep track of the number of views on your website’s pages. You normally create a formula to add the new month’s totals to last month’s totals to create the year-to-date. You have to create a new formula for each new month throughout the year.

Instead of creating all those formulas, simply copy your new monthly total, then paste it over your year-to-date data. EXCEPT instead of using Ctrl + V to paste, use Ctrl + Alt + V. That opens Paste Special.  Under the Operation section, select Add, then OK and the totals appear! Then each month, copy your current numbers and use Paste Special, Add to the year-to-date. No formulas, and no problems!

Consolidate is more complex, but it can save you a boatload of time when you need to gather numbers from several different sources on multiple worksheets and then total them all in one sheet. For example, each of your worksheets contain the sales figures for each of your company’s regions. Each line item is a different product which not all the regions sell. What a cumbersome process you would have to follow! First, to sort all the products on each sheet and then to create a formula to collect all the totals ensuring the data matches the products.

Consolidate is a HUGE easy button! It will look for all the same products (even if they are NOT in sort order) and sum them on a new sheet (even if some of the worksheets don’t list the product).

What’s the catch? There is a tiny bit of setup involved: make sure that all the worksheets have the same columns appearing in the same order with the same column names. (Tip: if you receive the worksheets regularly and they are not set up the same, use a macro to restructure them.)

Then, create a new worksheet for the totals and put your cursor in cell A1. Under the Data tab, click Consolidate. In the Reference section, click the collapse button (it looks like a tiny worksheet).  Go to the first worksheet and select the data. Back in the Consolidate dialog box, click Add. Check the Use Labels in Top Row and Left Column options. Repeat for each of the worksheets. Then click OK. Once you have selected where the data is, Excel does all the math for you. What a time saver!

It is smart to let Excel do your routine calculations so you can spend your time and brain power on more important things like finding that calculator!