Spreadiquette? Exceliquette? Stop messing up my spreadsheet!
I like Excel and other spreadsheet programs. They provide incredible power. And if someone doesn't know what they are doing they get messed up really badly really quickly.
Typically the problem occurs when one person (usually with more knowledge of spreadsheets) authors a spreadsheet and then gives it to someone else (typically someone with less knowledge of spreadsheets, especially wrt formulae) to do data entry or to maintain the data in the spreadsheet.
Following a few rules of "etiquette" can keep everyone's life a lot simpler a neater. (Yes, I have been accused of OCD before. No, I don't think this is unreasonable - you try cleaning up a spreadsheet that someone has been hacking away at with a machete!)
Get it? SPREADsheets + ETIQUETTE = SPREADIQUETTE Did you see what I did there? Wasn't that cute? OK, enough of that - let's get to the rules...
(1) Be careful with copy & paste and even more careful with cut & paste. You think you are just moving a piece of data from one cell to another, but you don't realize that Excel just changed all formulae in the whole spreadsheet that refer to the origin cell. Copy/paste is *much* safer than Cut/paste - therefore what you should do instead of cut/paste is to first copy/paste your data and then go back and delete the data from where it was. Yes, it's 2 steps instead of 1 and therefore takes a few seconds longer, but it will save you (and the person who has to clean up after you) untold headaches if you learn this one simple rule.
(2) A space is not the same as an empty cell. Yes, it *looks* like you deleted the cell by pressing space and enter, and, yes, I can see that it feels really fast and efficient to you. But to a computer leaving a space in a cell is just as non-empty as if you left an X there or as if you left a copy of the entire text of War and Peace in there. If you want to delete the data in a cell, use the backspace or the delete key - don't use the "space-enter" shortcut. It really messes up formulae like COUNTA() and ISEMPTY() and etc.
(3) Pay attention and maintain consistency with where data goes and where formulae are. Particularly if you see a row or a column or a section of the spreadsheet that has a bunch of formulae, don't go and replace one of the formula with your own data that you type in (and don't delete the formula and don't change the formula either unless you're following the rules below). Yes, your "quick editing" made it look like how you wanted, but you are almost undoubtedly dooming that spreadsheet to a purgatory of future inaccuracies by your careless editing.
Replacing a cell of entered data (a simple text or number) with a formula is usually OK. Replacing a formula with something you type in (OR DELETING THE FORMULA OR MODIFYING THE FORMULA!) is rarely going to end well unless you follow these steps...
RULE: If you need a cell changed and you see that it contains a formula (especially if the cells above/below/beside it also contain similar formula!) then follow these steps:
(1) If you have the spreadsheet expertise to understand the formula, go and fix the data that the formula is using - the formula will then automatically update the cell you are trying to fix and everything is good. Data is made to be entered and corrected; formula normally shouldn't be changed.
(2) If the formula is simply wrong, try to understand what the writer of the formula was trying to do and fix it well and then copy it (or, better, use "fill") to all cells that had the same formula. Be particularly careful of absolute/relative references.
(3) If you are unable to do (1) or (2) with confidence and ability (i.e., if you don't understand formulae adequately) then GO AND ASK FOR HELP. Don't blithely make changes to the spreadsheet in a way that is going to cause headaches later on...
(1) If there are parts of your spreadsheet that you don't want a data-entry person to mess up, then protect those parts and then protect the sheet. You choose whether you're going to put a password on or not, but at least make sure they have to make a deliberate decision to change the stuff you think shouldn't be changed.
(2) Often people don't protect cells and sheets because the data entry personnel need the capability of adding rows (or, less commonly, columns). Excel provides excellent capabilities for this - learn to copy your formulae into the relevant cells using "fill" (dragging that little box in the lower right corner) rather than copy/pasting the formula or (horrors!) typing it in multiple times. And fill it down to one row *below* where they should be entering. That way when the data entry person inserts a new row all the formulae will be automatically present and everybody is happy and the data entry person is still restricted to just making changes in the place they should be working.
Typically the problem occurs when one person (usually with more knowledge of spreadsheets) authors a spreadsheet and then gives it to someone else (typically someone with less knowledge of spreadsheets, especially wrt formulae) to do data entry or to maintain the data in the spreadsheet.
Following a few rules of "etiquette" can keep everyone's life a lot simpler a neater. (Yes, I have been accused of OCD before. No, I don't think this is unreasonable - you try cleaning up a spreadsheet that someone has been hacking away at with a machete!)
Get it? SPREADsheets + ETIQUETTE = SPREADIQUETTE Did you see what I did there? Wasn't that cute? OK, enough of that - let's get to the rules...
Spreadiquette for Data Entry
If you are given access to someone's spreadsheet to enter data, here are a couple of "etiquette" rules related to spreadsheets:(1) Be careful with copy & paste and even more careful with cut & paste. You think you are just moving a piece of data from one cell to another, but you don't realize that Excel just changed all formulae in the whole spreadsheet that refer to the origin cell. Copy/paste is *much* safer than Cut/paste - therefore what you should do instead of cut/paste is to first copy/paste your data and then go back and delete the data from where it was. Yes, it's 2 steps instead of 1 and therefore takes a few seconds longer, but it will save you (and the person who has to clean up after you) untold headaches if you learn this one simple rule.
(2) A space is not the same as an empty cell. Yes, it *looks* like you deleted the cell by pressing space and enter, and, yes, I can see that it feels really fast and efficient to you. But to a computer leaving a space in a cell is just as non-empty as if you left an X there or as if you left a copy of the entire text of War and Peace in there. If you want to delete the data in a cell, use the backspace or the delete key - don't use the "space-enter" shortcut. It really messes up formulae like COUNTA() and ISEMPTY() and etc.
(3) Pay attention and maintain consistency with where data goes and where formulae are. Particularly if you see a row or a column or a section of the spreadsheet that has a bunch of formulae, don't go and replace one of the formula with your own data that you type in (and don't delete the formula and don't change the formula either unless you're following the rules below). Yes, your "quick editing" made it look like how you wanted, but you are almost undoubtedly dooming that spreadsheet to a purgatory of future inaccuracies by your careless editing.
Replacing a cell of entered data (a simple text or number) with a formula is usually OK. Replacing a formula with something you type in (OR DELETING THE FORMULA OR MODIFYING THE FORMULA!) is rarely going to end well unless you follow these steps...
RULE: If you need a cell changed and you see that it contains a formula (especially if the cells above/below/beside it also contain similar formula!) then follow these steps:
(1) If you have the spreadsheet expertise to understand the formula, go and fix the data that the formula is using - the formula will then automatically update the cell you are trying to fix and everything is good. Data is made to be entered and corrected; formula normally shouldn't be changed.
(2) If the formula is simply wrong, try to understand what the writer of the formula was trying to do and fix it well and then copy it (or, better, use "fill") to all cells that had the same formula. Be particularly careful of absolute/relative references.
(3) If you are unable to do (1) or (2) with confidence and ability (i.e., if you don't understand formulae adequately) then GO AND ASK FOR HELP. Don't blithely make changes to the spreadsheet in a way that is going to cause headaches later on...
Spreadiquette for Authors
OK, above was some spreadiquette for data-enterers - now some spreadiquette for the original authors of spreadsheets:(1) If there are parts of your spreadsheet that you don't want a data-entry person to mess up, then protect those parts and then protect the sheet. You choose whether you're going to put a password on or not, but at least make sure they have to make a deliberate decision to change the stuff you think shouldn't be changed.
(2) Often people don't protect cells and sheets because the data entry personnel need the capability of adding rows (or, less commonly, columns). Excel provides excellent capabilities for this - learn to copy your formulae into the relevant cells using "fill" (dragging that little box in the lower right corner) rather than copy/pasting the formula or (horrors!) typing it in multiple times. And fill it down to one row *below* where they should be entering. That way when the data entry person inserts a new row all the formulae will be automatically present and everybody is happy and the data entry person is still restricted to just making changes in the place they should be working.
posted by Peter Bowers at 3:00 AM
0 Comments:
Post a Comment
<< Home