Excel Tips and Tricks for Data Acquisition and Control
Our free Monitor newsletter (ISSN 1472-0221) features a series of Excel Corners, giving hints and tips to scientists and engineers on using Excel. To subscribe to Monitor fill in your e-mail below.
For information on charting with Excel, see our Excel Charting Tips.
Contents: E-mail and Web Data | Regularly Logging Data | Opening and Closing Files | Starting Other Applications | Working with Latitude and Longitude | Counters | Virtual Instruments | Filtering, Averaging, Summing, Condition Matching | Formatting Data | Selecting Cells | Entering Data | Speeding Things Up | Everything Else
E-mail and Web Data
- For how to send an e-mail when a value crosses an alarm level, see Monitor issues 117 and 118.
- For how to get live data from a web page into Excel, see our Excel-to-web page.
- For how to overlay Excel data on Google Earth.
Regularly Logging Data
- Reading Data from All the Instruments' Channels
- For how to find the first empty row and jump down to it, see Monitor issue 177.
- How to log to a worksheet's top row, and move the other data down..
- For how to log only when new data arrives, see Monitor issue 56 and 57.
- For how to add a time stamp to logged data, see Monitor issue 74.
- For timestamping in tenths of seconds, see Monitor issue 77.
- For displaying timestamps in seconds, see Monitor issue 167.
- For logging to a new file every day, see Monitor issue 179.
- For logging to a new file every hour, see Monitor issue 192.
- For how to have Excel automatically start and stop logging, see Monitor issue 73.
- For setting the interval between data readings, see Monitor issue 152.
- Logging data, timestamping and showing interval sub-totals, see Monitor issue 151
- For how to save data only from, say, the last hour or week, see Monitor issue 50.
Opening and Closing Files
- For how to open several files of logged data at once, see Monitor issue 51.
- For how to open very large files in Excel, see Monitor issue 155.
- For how to close all open worksheets, see Monitor issue 166.
Starting Other Applications
Working with Latitude and Longitude
- For how to convert latitude and longitude to decimal degrees, see Monitor issue 61.
- For how to overlay Excel data on Google Earth.
Counters
Virtual Instruments
Filtering, Averaging, Summing, Condition Matching
- For how to see quick calculation results, see Monitor issue 105.
- For how to filter and sum data, see Monitor issue 36.
- For how to calculate running averages for live data, see Monitor issue 38.
- For how to calculate average values per hour, see Monitor issue 80
- How to average items in depending upon the contents of cells further along the row, see Monitor issue 210
- For how to exclude extreme data values from a mean, see Monitor issue 176
- For how to count the number of readings matching a given condition (using CountIf), see Monitor issue 115.
- For how to find all values matching one or more conditions (using an Array Formula), see Monitor issue 116.
- For how to work with arrays of data, see Monitor issue 116.
- For how to use SumProduct instead of arrays, see Monitor issue 229.
- For how to quickly arrange data with a pivot table, see Monitor issue 123.
Formatting Data
- For how to highlight out-of-range data, see Monitor issue 78.
- For how to automatically format a cell depending on its value, see Monitor issue 83.
- For how to automatically add borders to data, see Monitor issue 86.
- For how to highlight alternate rows, see Monitor issue 228.
Selecting Cells
- For how to quickly select a table of data, see Monitor issue 79.
- For how to work with alternate rows, see Monitor issue 125.
- For how to copy just visible cells, see Monitor issue 213.
Entering Data
- For how to enter a line break in a cell, see Monitor issue 52.
- For how to edit the same cell in several sheets at once, see Monitor issue 132.
- For how to create custom number formats in Excel, see Monitor issue 121.
- For how to quickly switch between absolute and relative references, see Monitor issue 131.
- For how to show live data in an Excel form, see Monitor issue 66.
- For how to reduce errors in worksheet references, see Monitor issue 133.
- Indirect references - stopping references changing after inserting columns, see Monitor issue 218
Speeding Things Up
- For tips on naming ranges, see Monitor issue 53 and 55.
- For how to automatically generate reports, see Monitor issue 72.
- For handy keyboard shortcuts, see Monitor issues 143 and 197.
- 8 Tips to Speed up Your Spreadsheet, see Monitor issue 127.
- For quick help on formulae, see Monitor issue 134.
- For finding readings with VLOOKUP, see Monitor issue 139.
- For speedy formula updates, see Monitor issue 160.
- Instantaneously copying cells, see Monitor issue 216.
- For finding all cells containing a formula, see Monitor issue 225.
Everything Else
- Sending data to an analogue or digital output
- For how to recover data from damaged spreadsheets, see Monitor issue 101.
- For how to run a macro from several worksheets, see Monitor issue 99
- Why Declare Variables?, see Monitor issue 188
- For sending keystrokes to Excel, see Monitor issue 191
- Deleting Links, see Monitor issue 206
- If, Then, Else, see Monitor issue 203
- For other Excel resources see our Links page.
