Microsoft Excel: 5 Things You Should Know How to Do

Learning these simple tasks can save you time and increase your productivity.

1) How to Change the Number of Default Sheets

When you open a new workbook, it contains three spreadsheets by default. But did you know you can change this number? Here’s how:

  1. Go to Tools–>Options and click the General tab.
  2. Under Settings/Sheets in new workbook, click the arrows on the right of the list box to either increase or decrease the default number of sheets.
  3. Click OK.

2) How to Name a Range

A range like A12312:P17175 can be pretty awkward to use in a formula. You can simplify things by giving the range a name instead:

  1. Select your range.
  2. Go to Insert–>Name–>Define…
  3. In the Define Name dialog box, type the name of your range in the Names in workbook section. Also, make sure your selected range is properly reflected in the Refers to section.
  4. Click OK.

Just use the name in place of the range. For example, you can add the values in a range named Numberslike this:

=SUM(Numbers)

3) How to Create a Constant

A constant is a value, often a measurement, that doesn’t change. A user typically places the constant in a cell, hide the row containing the cell, and then create formulas referencing the cell’s location. But this method is dangerous. What if someone accidentally deletes the hidden row? Any formula using the constant will get the #REF error. A safer way is to give the constant a name:

  1. Go to Insert–>Name–>Define…
  2. In the Define Name dialog box, enter the name of the constant in the Names in Workbook section.
  3. In the Refers to section, type an equal sign and then the value of the constant.
  4. Click OK.

Here’s an example of a formula that multiplies a constant named PI (with a value of 3.14) by five, and divides the result by three:

= (5 * PI)/3

4) How to Expand the Recently Used File List

As you probably know, you can find a short list of workbooks you’ve recently opened at the bottom of the File menu. But if you work with lots of Excel files, it may be convenient to expand the listing:

  1. Go to Tools–>Options and click the Generaltab.
  2. Under Settings, go to the Recently used file list section. Use the arrows on the right to increase (or decrease) the number of files you want to see.
  3. Click OK.

5) How to Save a Workspace

Users often work with several files at a time, arranged in various ways (i.e., tiled, horizontal, vertical, cascade). And many of them go through the tedious routine of closing the files at the end of the day, opening them up the next morning, and re-arranging them again. A more efficient method is to save the configuration of the spreadsheets as a workspace.Give this a try:

  1. Save each of your files in the arrangement of your choice.
  2. Go to File–>Save Workspace…
  3. In the Save Workspace dialog box, enter the workspace name in the File name section.
  4. Accept Workspaces as the default in the Save as type section.
  5. Click Save.

To open your workspace, go to File–>Open. Double-click the name of your workspace and your workbooks will immediately open in their original configuration . If you want to get rid of a workspace, just delete it as you would any file. Only the arrangement will be erased, not the workbooks themselves.

These Excel tasks aren’t the hardest ones to master, but they’re certainly some of the most useful.

Leave a Comment