Supercharge Your Data Crunching

The current CEM data acquisition workbook updates input values every second while in acquisition mode. In the process of doing this, it clears the clipboard each second, which makes copy/paste a difficult maneuver. Also, all the pull-down and shortcut menus disappear as soon as the run gets started, which makes boxing your averaged data a frustrating task and may lead to a lot of swearing at Volker.

Here are a few tricks that Mark Ambler has worked out that may help you get some work done while the workbook is busy doing its thing. A word of warning – the movie clips below are huge files…you probably want to download them first before attempting to run them.

Copy and Paste

Sometimes you run a zero gas and the numbers you want to average apply to all the constituents. You can still CTRL + C then CTRL + V if you get it done in one second, but a better way is to click and drag the corner of the box you want to copy (called ‘filling’). That way you can copy everything at the same time and you don’t have to complete the task in one second.

VIDEO [Copy-Paste] – This video shows how to take the averages for the zero and copy them to the other cells

Inserting Averages

Look for the row that the first data point for each average will be and write this down. Then, go up to the average boxes and manually change the average equations. If you press CTRL + ` (the quote below tilda, ~, usually to the left of the “1” key), Excel will change to an ‘equation view mode’. This way you can check to make sure you entered the right averages.

VIDEO [Averages] – This video shows how to enter a few averages using CTRL+` to view the formulas.

Creating Boxes

After you have the averages completed you can create boxes around the data you selected by selecting all the averages, pressing CTRL + [ to select all the averaged data, then CTRL + Shift + & to box all the data.

VIDEO [Boxes] – This video shows how to make boxes around the averages that have already been entered.

And there you have it. The data is all crunched quickly and during the run so that you don’t have to delay the start of the next run.

Here are some other shortcut keys that are handy to keep up your sleeve:

ALT + TAB to switch to another program/file open on the computer

Windows Button + M to view the desktop

CTRL + 9 to hide selected rows

CTRL + 0 to hide selected columns

CTRL + ; to insert the date

F2 to edit the formula (stops the data logger though)

CTRL + arrow to go to the last data in a row/column before a space

CTRL + SHIFT + arrow to select all the data in a row/column before the space
VIDEO [CTRL-SHIFT-arrow] – This video shows how this command can quickly select an entire set of data

CTRL + page up/down to move through tabs

CTRL + SHIFT + page up/down to select multiple tabs
VIDEO [CTRL-SHIFT-page] – This video shows how you can use this command to quickly select multiple tabs and fix a formula in all the tabs.