Five Powerful but Easy Microsoft Excel Tips for the Novice
Here are a few of my favorite, and often used, Excel features. These selected functions can greatly simplify daily Excel tasks. They are non-technical and easy to remember.
1. Naming ranges
Have you ever gotten lost trying to enter a complex formula, or understood whether it has even been written correctly? Which formula below would be easier to understand?:
- =SUM(A5:D5)*SUM(D7:F7)+SUM(Sheet2!C9:C14)
- =SUM(Quantity*Cost)+Overhead
The next time you need to reference a range of cells or an individual cell in a formula, just highlight that range or cell and click on the Name Range dropdown box (directly above Cell A1), and type in a descriptive name. Now, when you write the formula use the range name instead of the actual cell range.
Use this feature especially when others need to access and review your Excel sheet. Naming ranges will speed the review and auditing process.
2. Creating a dropdown list in a cell
In many data entry sheets you need to ensure data is keyed in correctly. For instance, in a Yes/No field you need to prevent a user from entering “Y” or “N”.
There are a few ways to create a dropdown list. The simplest method is as follows:
- From the main menu, select Data then click Data Validation in the Data Tools section
- Select the Data Validation option.
- In the Data Validation dialog box select “List” in the “Allow” dropdown.
- In the “Source” box, type your list, with each entry separated by a comma (and no spaces!). Click OK.
- Your resulting custom dropdown list should look something like this:
3. Auto column resizing
Have you ever opened a spreadsheet that looks like this:
But you need it to look like this:
Rather than resizing each column as most users do, you can complete the task in 2 clicks (OK 1 click and 1 double-click). Click the top left corner of the table (the box to the left of Column A). Now hover the mouse and move over any column header between the adjacent columns until you see this symbol: 
. Double-click and voila! Every column auto resized.
4. AutoSum
The most basic function of Excel is to add columns and rows of numbers. When creating a sheet like the one below, most users will enter the addition formula in one cell and copy that formula to the other cells — once for the rows and once for the column totals.
How about totaling every row and column in 2 mouse clicks? Simply highlight the entire table, including the blank Total row and column, then click the AutoSum Symbol located at the top-right of the Menu bar. The symbol looks like this: 
5. Creating a line break within a cell
Have you ever needed to enter paragraph style text into a cell, with paragraph/line breaks? Simple but not necessarily intuitive. While in edit mode, press Alt + Enter to force a line break within the cell.
There you have it. Five easy but productive Excel tips and tricks any day-to-day user can take full advantage of.
This is a useful post.