Using Spreadsheets

Here are tips on using one of the Office applications to input data into your spreadsheets quickly and reliably using a sequential set of steps.

Topics this page:

Site Map  
About this site  

Data Entry

Preparations Setup Starting During Ending Follow-up After

Preparations:

• Are Excel add-in installed on your computer? The VBA and Analytical functions are the most requested.
• If you are working on a big file, do you have WinZip or Stuffit programs installed so you could backup to multiple diskettes in a compressed format?
• Make sure you end up with a paper trail. What if your computer crashes right after you entered a hugh stack of work? It's been known to happen.
• Purchase extra blank backup media.
• Put manual data on one sheet, then create another sheet for calculations. This leaves your data entry sheet uncluttered.
• Set your Options to not move down a cell when you press Enter.
• Consider creating a macro to perform repetitive tasks. It's easier than it looks.

  To avoid that warning when running your Excel 2000 macros, digitally sign the macros you create. First, install the Digital Signature for VBA Projects option using the Custom Install option. Then use Find/Search to locate file selfcert.exe, and run it to set up a digital certificate.

  Apply your certificate by opening a file containing a macro and select Tools-> Macro -> Visual Basic Editor. In the Project Explorer, select the module containing the project you want to "sign" and select Tools/Digital Signatures. Then select your certificate and click OK.

  When you open that file again, when the Security Warning dialog appears, check the "Always trust macros from this source" box and click Enable Macros.

Setup each time:

• Do you have appropriate amount of light shining on your work area? Ideally, you should not see any reflection on your computer screen.
• If you see glare on your paper, move the paper or move the computer. Glare leads to mistakes and headaches. It's not worth it.
• Check to make sure the date and time on the computer is accurate. You may need to use the time stamp to see which version of a file to keep or throw away. An accurate time stamp will keep you from throwing away the wrong file.
• Have ready a pencil to mark off each item you entered. This way, if you stop in the middle, you'll know where to resume work again.
• It's good if you have a log of changes to your file.
• Create diskette labels before your start working. Put the name of your file and the date.

Starting:

• Take the few seconds to make an extra backup to a diskette before you begin. Try it and see how much less stressful it is.
• You may find it easier to double-click the file to open it from the Windows 3.1 File Manager or Windows 95 Explorer. This method allows the files to be sorted by the date last modified. This is one way to make it harder to pick obsolete files.
• Make sure that the system's menu bar at the bottom of the screen doesn't obscure the Excel tabs. Maximize your screen, then either bring the bottom of your viewing area up or set the system's bar up so "Always on Top" is unselected and "Auto hide" is selected. Go to the Start menu, select Settings, Task Bar, then click away.
• Pull down the Window menu and check to see whether you could close files you don't need open. Not only do extra open files compete for resources and thus make your computer slower, they also might cause your computer to crash.
• Open data files before opening files which reference that data file.
• Don't move the location of columns unless you open the spreadsheets which refer to those cells.
• If you're entering data from a report which have a total of individual items, also enter that total so that you have a way of verifying your entries.

During:

• Make it a habit to click on the Save button often. WARNING: You will be working off where you saved the file. If you want to save to a diskette, save to a hard disk and switch to the File Manager or Explorer to copy the file to the diskette.
• Press the Tab key to move to the next field.
• Check each item for accuracy before moving to the next entry.
• If you're entering corrections from a marked-up print-out, use a highlighter to mark the changes you completed. This allows you to quickly identify whether a change was input or not.

Ending:

• Print out a data input verification report. Most people catch more mistakes on paper. Plus you will have physical written proof that data went in the computer. This could kill a lot of trees, though.
• Press Ctrl-arrow keys to check for missing fields.
• Close files before exiting the program. Don't just turn off the power.

Follow-up
After:

• You may not catch all errors until you analyze and your data on each variable entered. So update and review your summary reports after you enter a batch of data.

VB Macro to Export Text File with Comma AND Quote Delimiter

Creating Charts in Excel

Switching Chart Type erases Chart Options. However, Series data is carried forward to the chart selected.

Stock Charts

When hightlighting a range before selecting the chart button, remember that if text is found in the leftmost column, it is used as the X axis labels for all data series.
So if your X axis labels contain numbers, place a single quote in front of each value so that they will be treated as text.

Highlight the number of data columns (series) expected by each type of Stock chart:

Stock chart type	Series
High-Low-Close	1 - 2 - 3
Open-High-Low-Close	1 - 2 - 3 - 4
Volume-High-Low-Close	1 - 2 - 3 - 4
Volume-Open-High-Low-Close	1 - 2 - 3 - 4 - 5

2 Axis Charts

Within the "Chart Type" menu "Custom Types" tab choose "Lines on 2 Axes".

When using the "Line - Column on 2 Axes": the Primary axis is always a bar chart and the Secondary axis is always a line chart. Also:

• Y Primary axis title text always appears at the left of the chart.
• Y Secondary axis title text always appears at the right of the chart.
• X Primary axis title text always appears at the bottom of the chart.
• X Secondary axis title text always appears at the top of the chart.

If only one series is designated as the Primary axis, it cannot be changed into a Secondary axis. So first select a series designated for the Secondary axis and change its axis to Primary.

Radar Charts

Radar charts are great to compare multiple series on multiple dimensions (categories), like a 3 dimensional view.

The problem with Excel's Radar Chart is that you are forced to have a single "Y axis" scale for every category (dimension).

So if each category has a different value range (which is pretty typical), you can't use Excel.

3D Charts

Radar charts are great to compare multiple series on multiple dimensions (categories), like a 3 dimensional view.

The problem with Excel's Radar Chart is that you are forced to have a single "Y axis" scale for every category (dimension).

So if each category has a different value range (which is pretty typical), you can't use Excel.

...

MS-Excel Functions

In Excel, the serial number that represents one hour is .04166667 Divide again by 60 for the minutes.

For the number of hours and minutes in an elapsed date in cell A1

=INT(A1/.04166667)&":"&MINUTE(MOD(A1,1))

To add a month to a date in cell A1 (such as 1/31/03 to 2/28/03)

=DATE(YEAR(A1),MONTH(A1)+1,MIN(DAY(A1),DAY(DATE(YEAR(A1),MONTH(A1)+2,0))))

To find the last name in "Smith, Joe" in cell A1:

=LEFT(A1,FIND(",",A1,1)-1)

To find the first name in "Smith, Joe" in cell A1:

=MID(A1,FIND(",",A1)+2,LEN(A1)-FIND(",",A1)+2)

To tell Excel that you are making an array, hold Ctrl+Shift while pressing Enter. Brackets {} will be displayed around it.

Date Programming

Group microsoft.public.excel.programming

List of Excel Developers at J-Walk:

Alan R. Barasch's Excel Goodies

Chip Pearson, a Microsoft MVP - Excel

Debra Dalgleish, Microsoft MVP - Excel

David McRitchie, Microsoft MVP - Excel

Erlandsen Data Consulting

John Walkenbach's Excel Tips

Tommy Flynn's Excel Page

OzGrid.com

Excel templates for financial analysis and business decision-making