Let’s say you have a file that has numerous input cells, in numerous sheets. Each month you must clear the input cells and start the new month. There is a technique that allows you to clear the input cells if you have used a dedicated colour for those cells.
Assuming you have a fill colour that is ONLY used for input cells then this technique is pretty straight forward.
You must do this one sheet at a time. Steps 2 and 3 are not needed for extra sheets, as the dialog remembers the colour.
- Press Ctrl + f to open the Find Dialog
- Click the Options button
- Click the Format drop down and click Choose Format From Cell. Click on one of the input cells.
- Click the Find All button
- Click inside the bottom section of the dialog and press Ctrl + a, this selects all the cells found, see images below
- Click the Close button
- Type o (zero) and hold the Ctrl key down and press Enter
- Done! All the entries now have a zero entered – ready for input
In the situation where the input cells will not change or be added to in a workbook, I have resorted to selecting all the input cells & giving them a range name, say Input_1
I then simply select the relevant range name from the range name box & then either clear the cells or enter the zero as described above.
Thanks Chris – yes, range names rule and are ideal if the input cells/ranges don’t/won’t change.