Zeroing an input range in multiple cells

Range names make it easy

In some cases you may have to make manual inputs across multiple cells that are spread across a sheet. Before making the entries you need to clear the existing entries. Creating a range name can make that process much quicker.

In the image below let’s say I need to clear all the yellow cells that are for input.

If this is a regular process the easiest way is to create a range name which can be re-used to select the range

If the input cells have a consistent colour and no other cells use that colour then technique 1 below works for ad-hoc selections.

To create the range name you first need to select all the cells in the range.

There is are two ways to do that.

  1. This technique is best of you have lots of yellow cells to select. If there are not many cells/ranges involved use the manual method at 2. below.Click a single cell in the sheet. Press Ctr + F to open the Find Dialog. Click the Options button if the options aren’t visible. Click the Format drop down and click Choose Format from cell and click on one of the yellow cells.

    Click the Find All Button then click in the bottom section of the Find dialog and press Ctrl + A to select all the cells.

    This has selected the range – see the Creating the name section below.

  2. The manual method to select the range also involves the Ctrl key. Select one of the yellow ranges as per normal using the mouse. Then hold the Ctrl key down and use the mouse to select all the other yellow ranges.

Creating the name

With the range selected click in the Name Box (top left above the grid see pic below)

Type a name in.  I used Input_range and press Enter. Note you can’t have spaces in range names.

Click another cell and then click the Name box drop down and re-select the range name.

To clear the entries press the Delete key.

To make an entry in all the selected cells (say zero) type 0 and then press Ctrl + Enter – see image below.

Ctrl + Enter makes the same entry in all the cells in a selected range.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.