web analytics

TOP 10 TIME SAVING TIPS FOR MICROSOFT EXCEL 2007 – PART 2

Microsoft Excel 2007

These Top 10 saving-time tips for Excel (2007) can reduce the time it takes to complete your work to great extent. They will brighten some of the Excel most time-demanding procedures which are very often hard to achieve, especially for the beginners. There are many screenshots from the required procedures that can boost the user’s performance. They can be used for other versions of MS Excel.

This article is a continuation of Part 1.

9. Setting Up a Form

Most of the standard forms, time sheets or expenses forms that are used by the businesses are created in Excel. This tip presents you the possibility for the user to quickly move through the data input cells by hitting the Tab key.

This tip shows you how to lock down every cell in the spreadsheet except the input cells.

19

Highlight every input cell in you spreadsheet (hold down Ctrl key while selecting non-adjacent cells). Next, click on the Format button (in the cells group) and select the Format Cells tab.

20

In Format Cells dialogue box select the Protection tab and uncheck the Locked check box. Click OK.

You should know that the every cell is locked by default. Also, locking or unlocking the cells from the spreadsheet is not usable if the worksheet is not protected.

21

Click on Format button and in the Protection section select Protect Sheet tab.

22

In the Protect Sheet tab, uncheck the Select locked cells check box. Click OK. When the sheet is protected, you’ll find pressing Tab moves the active cell indicator to the next unlocked cell. (Only the unlocked cells can be selected)

10. Adding a Drop-down List

It is very practical if you can limit your answer to some adequate format, such as a day of the week or a month, so the predefined inputs in drop down box are very useful.

There might be user who wants to enter a day of a week in the input cell, such as Sunday, Monday, Tuesday, etc.

Enter the items that you prefer to be in the drop down list in some column from the worksheet, so there is one item per cell. Enter the names of the months outside of the drop down list area, for example in the cell range from G1 to G7.

Select any cell that you wish to contain the drop down list (G1). For selecting more than one cell that should display the list, hold down Ctrl key while clicking the nonadjacent cells one by one. For adjacent cells just select the range with the mouse.

23

On the Data tab, in the Data Tools group, click Data Validation.

24

Choose Settings tab in Data Validation box. Then, in Allow box select List. Check the In-cell drop down check box. If you want to enable the possibility for the cell to be left blank, uncheck the Ignore blank check box.

25

You can make further improvements by setting an input message when the cell is clicked.

Select the Error Alert tab and check the Show error alert after invalid data is entered check box.

Select one of the following options for the Style box:

  • To display an information message that does not prevent entry of invalid data, click Information.
  • To display a warning message that does not prevent entry of invalid data, click Warning.
  • To prevent entry of invalid data, click Stop.
  • Type the title and text for the message (up to 225 characters).

When you finish the required steps, Drop-down arrow is displayed in the drop-down cell when it is active, which means that you can select an input for the cell. By clicking the arrow you can choose from the given items. If you type something that is not consisted in the drop-down list, you will get a message from Excel that your input is invalid.

26

Note If you don’t enter a title or text, the title defaults to “Microsoft Excel” and the message to: “The value you entered is not valid. A user has restricted values that can be entered into this cell.”

If you want to set your own text for the invalid entries message, enter the text in the Error message field which is located in Error Alert tab in the Data Validation dialogue box.

If the item’s list is very short, you may enter the items in the Source field. Every item from the list should be separated with comma.

Leave a Reply