Tips on Microsoft Excel

How to protect few selected cells in an excel document?

Select the entire worksheet by clicking the Select All button (the gray rectangle directly above the row number for row 1 and to the left of column letter A).
Click Cells on the Format menu, click the Protection tab, and then clear the Locked check box. This unlocks all the cells on the worksheet (If the Cells command is not available, parts of the worksheet may already be locked—to unlock go to the Tools menu, point to Protection, and then click Unprotect Sheet.
Select just the cells you want to lock and repeat step 2, but this time select the Locked check box.
On the Tools menu, point to Protection, click Protect Sheet, and then click OK. In the Protect Sheet dialog box, you have the option to specify a password and select the elements that you want users to be able to change. You will need to save your changes in the excel document for the Protection to save.

Reverse your direction when searching in a work sheet in Excel

Did you know that you can reverse your direction when searching in a work sheet in Excel? When you are using the Find or Replace feature in Excel, it is easy to accidently click the Find Next button one too many times. Fortunately, you don’t have to cycle through all of the matches in the worksheet to get back to the ones you missed the first time. Instead, just hold down the (Shift) key when you click the Find Next button. Excel searches in the opposite direction through the worksheet as it hunts for matches to your search string.

Number as text in an excel worksheet

Here is a tip on how to enter a number as text in an excel worksheet. Simply precede your data with an apostrophe character (‘). Doing so forces Excel to treat the entry as text data. For example, enter ‘8-2099. When you complete the entry, Excel doesn’t display the apostrophe in the cell, although you can still see it in the Formula bar.

How to change the color of the new 2007 Excel Application

Tired of the blue? In the new Excel 2007 it gives you a lot of new visual options when it comes to building a spreadsheet. You can apply themes to your data and use new shape effects and conditional formatting looks. But what about your Excel application itself? Believe it or not, you don’t need to stick with Microsoft’s traditional blue color scheme. To change your Excel application’s color scheme: 1. Launch Excel and click the Office button. 2. Click the Excel Options button to open the Excel Options dialog box. 3. If necessary, choose Popular in the left pane. 4. Choose either Blue, Black, or Silver from the Color Scheme drop-down list. 5. Click OK to apply the new color scheme.

How to add an image to a comment box in Excel. Open an excel document.

On the box that you want to add a picture comment RIGHT click on the edge of the box not inside the text box of the comment. Then select the Colors and Lines tab in the Format Comment dialog box. Click the Color drop-down list and select Fill Effects. In the Fill Effects dialog box, click the Picture tab and then click the Select Picture Button at bottom. Browse for a picture to insert and then make sure you check the box “LOCK PICTURE ASPECT RATIO”. Select OK. You should now see the picture of the item when you scroll your mouse over it. If you need to resize the picture then right click on the cell to launch the shortcut menu and click the Edit Comment menu item. You can then resize the display of the image.

Ever have problems with Pivot Table in Excel that keeps pulling in information

(data) that had been deleted or changed. These are often called ‘ghost items’ and can

really drive a person crazy! This can be fixed with a setting within the Pivot Table.

Click in the Pivot Table to open the Pivot Table option menu.

Click on options
Click on Options (under Pivot Table Name)
Click on the data table
Change the number of items to retain per field to NONE:
Refresh the data

Insert a new worksheet

To create a new worksheet, press [Shift] + [F11].

Quickly move to a worksheet

Right-click on any of the triangle navigation buttons (located immediately to the left of the worksheet tabs).
Select the worksheet you want to move to on the pop-up menu of worksheet names.
In Excel 2010 and earlier versions, select More Sheets if the worksheet you want is not shown.

Move between worksheets in a workbook

Next worksheet: [Ctrl] + [Page Down]
Previous worksheet: [Ctrl] + [Page Up]

Insert a new worksheet

To create a new worksheet, press [Shift] + [F11].

Quickly move to a worksheet

Right-click on any of the triangle navigation buttons (located immediately to the left of the worksheet tabs).
Select the worksheet you want to move to on the pop-up menu of worksheet names.
In Excel 2010 and earlier versions, select More Sheets if the worksheet you want is not shown.

Move between worksheets in a workbook

Next worksheet: [Ctrl] + [Page Down]
Previous worksheet: [Ctrl] + [Page Up]

To download data into Excel from other programs, first check for blank cells, and fill them in as needed. Some cells may be blank on purpose and should remain that way. But other blank cells can be troublesome, especially if sorting and filtering the data. For instance, a column in a report may only list the key category once for a group of records whereas in Excel, the category may need to be repeated on each row.

To download data or reports to sort, save time by filling the blanks right away. Typing or copying the entries can be very time-consuming, especially with large downloads. See the steps below for a quicker way.

Select blank cells

First, select the range that contains blanks you need to fill. Choose Go To Special from the Find dropdown list in the Editing group on the Home tab, or press [Ctrl]+G for Go To.
Select Blanks. Quick Tip: Notice the additional options under Go To Special, which are helpful with other Excel actions.
Click OK, and Excel will select all of the blank cells in the selected range.

Copy values from above

In the first selected blank cell, enter an equal sign, and point to the cell above. Do not press [Enter].
Press [Ctrl]+[Enter], and Excel will copy the respective formula to all blank cells in the selected range.

This keyboard shortcut can be used as a quick way of filling numerous cells or copying a formula into a range when it is entered, rather than copying it separately afterward. At this point, the range contains literal values (the original values) and formulas that repeat those literal values. To preserve the displayed data, replace the formulas with their results (the repeated literal values).

Paste values in Excel

Select the range, and copy using any technique you prefer, for example, the Excel keyboard shortcut Ctrl]+C for copy.
Right-click on the selection, and choose Paste Values from the shortcut menu or Paste Values from the Paste drop-down list in the clipboard group on the home tab.
Paste Value replaces the formulas with literal values.

Before the process, be careful if the range already contains formulas to not overwrite them, only overwrite the formulas added to fill the blank cells.

This technique may not work with every sheet that contains blank cells, but it is a fast and easy way to repeat existing data and to quickly change downloaded data to easily complete work in Excel.

VLOOKUP – What it is and when to use it

Use VLOOKUP with lists or tables arranged in vertical columns.
The lookup_value must be in the first, or leftmost column.
Wherever it starts, the lookup_value argument must exist in the first column of the list or table.
If your lookup table starts at column R and ends at column T, column R is considered the leftmost column.
To find the column that contains the search result (the col_index_num argument), count, starting from the left, or first column. For example, if the search result is in column C, the col_index_num argument is number 3.
If users leave the range_lookup argument off (which is either True or False), the formula defaults to True.
Use absolute cell references, or a named range, to copy the formula down a column.

What to do if the lookup_value is not in the left column

If the lookup_value is not in the left column, and it is not practical to move the column, use another solution, such as INDEX and MATCH.

VLOOKUP Arguments

lookup_value : This is the lookup value. Excel will try to find a match to this value in the leftmost column of the lookup table.
table_array: This is the lookup table. To copy the VLOOKUP formula, use absolute references to “lock” the range, or use a named range.
col_index_num : Count over from the first column to figure out what this number should be, starting with 1. For example, to lookup table starts in column A, and the search result is in column C, the col_index_num argument would be 3.
range_lookup: Should the lookup value be TRUE, or FALSE?
False =exact match (enter FALSE, or 0)
True =approximate match (enter TRUE, or 1). If TRUE, sort the leftmost column in ascending (or A through Z) order to ensure correct formula results.

Excel can help users recall the arguments

Click the Formulas tab on the ribbon, and then click Insert Function.
Type VLOOKUP in the Search for a function box.
Click VLOOKUP in the Select a function.
The Function Arguments dialog box opens, with an explanation for each argument.

Avoid VLOOKUP Errors

If the lookup_value is not in the first column, it will not work.
Make sure there are no unnecessary leading or trailing spaces or extra spaces between words that can cause errors.
Avoid including carriage returns, linefeeds, non-breaking spaces, or other special characters in the first column.
Before using VLOOKUP, check the list or table to make sure the formatting is consistent, and that there are no obvious errors.

Display formulas

To display worksheet formulas for auditing and printing, press [Ctrl] + ~
Use this shortcut again to revert back to worksheet results. This option, Show Formulas , is also found under the Formulas tab.

Fast moves between worksheets in a workbook

Try these keyboard shortcuts to toggle or cycle between worksheets:

Next worksheet: [Ctrl] + [Page Down]
Previous worksheet: [Ctrl] + [Page Up]

For another way to easily navigate to a worksheet in an Excel workbook, try the following:

Right-click on any of the triangle navigation buttons (located immediately to the left of the worksheet tabs) to display a pop-up menu of worksheet names.
Choose the worksheet you want. The option for More Sheets… displays as needed for a complete list of worksheets in the workbook.

Fast data entry

Save time with these quick and easy ways to enter data into an Excel worksheet:

Enter current date: [Ctrl] + ;
Repeat data from the cell immediately above: [Ctrl] + ‘
Create a SUM function for cells next to your current location: [Ctrl] + =

Wrapping text

When entering titles or long paragraphs of text, the [Enter] key will move to a new cell.
Press [Alt] + [Enter] to create a line break or new line in the same cell to decide where the text breaks for column headings, text descriptions, and Excel notes and comments.
Anticipate lengthy entries to wrap to multiple lines by applying the Wrap Text alignment feature to entire columns or rows by picking Wrap Text command from the Alignment group in the Home tab.

Go to shortcut

Quickly move to a specific cell or range by pressing [F5] or [Ctrl] + G (Go To).

Editing cells

Excel has lots of choices for editing text, values, or a formula in a worksheet cell including:

Type a new entry in the cell (no need to delete previous contents).
Double-click on a cell to open “in-place” or direct editing.
Press [F2] to open the current cell for editing.
Click once into the Formula Bar.
Press [Delete] to clear contents (cell is empty but retains formatting).
When a cell is opened for editing, click with the mouse or use arrow keys, [Backspace] , [Delete] , [Home], and [End] to navigate within the cell.

Link an entire Excel spreadsheet to PowerPoint:

In PowerPoint, on the Insert tab, click Object.
In the Insert Object dialog box, select Create from file .
Click Browse , and in the Browse box, find the Excel workbook with the data to insert and link to.
Before closing the Insert Object box, select Link , and click OK .

Link a section of data in Excel to PowerPoint:

In Excel, open the saved workbook with the data to insert and link to.
Drag over the area of data to link to in PowerPoint, and on the Home tab, click Copy.
In PowerPoint, click the slide where you want to paste the copied worksheet data.
On the Home tab, click the arrow below Paste , and select Paste Special.
In the Paste Special box, click Paste link , and then, under As , select Microsoft Excel Worksheet Object .

How to repeat titles on multi-pages spreadsheets in excel:

We’ve all had to flip back to the first page of a multi-page printout when we forget the all-too-important column headers. If only you could “freeze” your columns and rows on a printout, just like you can onscreen. If you use Excel 2007 or 2010, you can do just that and the column headers, repeat on each page of the printout. To designate repeating rows and/or columns: 1. Click on the Page Layout tab. 2. In the Page Setup group, click the Print Titles button to open the Page Setup dialog box’s Sheet tab. 3. Enter the row number(s) or column letter(s) as a range in the Rows to Repeat at Top or Columns To Repeat At Left text boxes. For example, because we want to display the top three rows, we would enter 1:3 in the Rows To Repeat At Top text box. 4. Click OK to accept these repeating rows and/or columns. If you look at the spreadsheets in print preview, you will now see you’re repeating rows and/or columns display on every page as you cycle through them.

Admin