Use able Short Tips on MS Excel

Password protection for Excel Workbooks
To protect your workbooks, open the workbook in question and click on Save As on the File menu. On the Tools menu at the top right of the box that appears, click on General Options. Type the password to open text box. Click OK. Your workbook is now password protected.

Using conditional formatting
Your Excel spreadsheet should let you glean information from it at a glance. This can be done using formatting. Conditional formatting allows you to apply a particular format to a cell, depending on whether it fulfils a certain pre-assigned condition.

To apply conditional formatting to a range of cells, first select the range. Go to the Format menu and select Conditional Formatting. A dialog box opens up asking you to specify the condition under which you want the format applied. From the first drop down, select Cell Value Is. Then select the appropriate evaluator from the second drop down. For example, this may be “greater than”. Now specify the value that the items in the selected range of cells should exceed. For example, “2300”.

Once you have specified the condition, click the Format button in the Conditional Formatting dialog box. Specify the formatting you want applied. For example, you might want to make all text red, or fill those cells with a particular colour. When you are done, click OK. Excel will highlight all the cells whose value is above 2300 with the colour of your choice.

Inserting multiple rows into an Excel worksheet
You probably know how to insert a single row into a worksheet—right-click anywhere in the row below where you want the new row, and choose Rows from the Insert option. Now, if you want multiple rows, after inserting one row, press F4. Excel will insert a new row as many times as you press F4. You can also do this: Select as many existing rows as you want new ones, and then choose Rows from the Insert menu. You will get as many new rows.

Close all open workbooks
To close your active workbook in Excel, you can use Ctrl + F4. In case you want to close all open workbooks at the same time, hold down the Shift key as you display the File menu. This will give you a Close All option. Choose this option to close all your workbooks, after saving changes in each. But remember that this option is only displayed if you go to the File menu while pressing the Shift key, and not otherwise.

Working with multiple Excel worksheets
If you’re working on several worksheets in a workbook, having to use a mouse to switch between them can be a huge waste of time. To go to the next active worksheet, use Ctrl + Page Down. To go back to the previous one, use Ctrl + Page Up. To insert a new worksheet into the workbook, use Shift + F11.

Allow simultaneous editing of Excel 2007 workbooks by multiple users
You can enable different users to simultaneously edit an Excel 2007 workbook by setting up the workbook as “shared”. You then need to save the workbook on an allocated network share, from where users can access and modify the data.

Create the workbook and add the data. Then, on the Review tab, select the Changes group, and click Share Workbook. You can select your options here. In the Changes group, you can also select Protect and Share Workbook, so that other users cannot turn off Change Tracking.

Saving the shared workbook will then prompt you about changes saved by other users. If you keep the shared workbook open, you can choose to be informed at specified time intervals about changes other users have made.

If changes that you are making to a cell conflict with changes made by other users to the same cell, you are prompted to choose the changes that you want to keep.

Distribute workbooks for editing; merge or consolidate changes
If you want to allow users to make changes to the data in a workbook, but want to control what changes are accepted in the final copy, you can distribute copies of the workbook to each user and then merge or consolidate the changes in each copy.

After creating the workbook, set it up as a shared workbook from the Changes group in the Review tab. Then, distribute copies of the workbook. After users have made the changes in their copies and sent them back to you, save all the copies to a common location and click Compare and Merge Workbooks.

You can also send copies of worksheets for separate data inputs and then collate these into a master worksheet. For this, each worksheet should have similar or identical layouts. Once you have received completed worksheets from each user, click Consolidate in the Data Tools group on the Data tab. You can choose to consolidate data by Position, Category, or 3D Formulas.