Excel Software Tips

Software Tips Home

 

Date

Tip
3/30/12 Excel Shortcuts:

1. To format any selected object: ctrl + 1
2. To insert the current date: ctrl + ;
3. To insert the current time: ctrl + shift ;
4. To insert or edit a cell comment: shift + F2
5. To auto-sum selected cells: alt + =
6. To insert a new sheet: shift + F11
7. To go to the next worksheet: ctrl + pg down
8. To go to the previous worksheet: ctrl + pg up
9. To see formulas (and formatting): ctrl + ~ (repeat this command to return to original view)
1/27/12 Fill Blank Cells With The Data From Above

When you bring data into Excel from another program, often some of the cells are left blank, indicating a repeat of the value above. This may make the information easier to read but it also means that you cannot use many of Excel's best features, such as filtering, subtotaling and Pivot Tables, to manipulate the data.

If you have a small amount of data, you can simply fill down or Copy and Paste the values for each blank section. However, when you are dealing with hundreds or even thousands of records, this method would not be practical (unless you have nothing better to do and you really enjoy doing boring, monotonous work)

I'd like to show you a 'fairly simple' process to help you complete this same task in less than a minute, regardless of how many records you have.

For this example, I the data for which we want to fill the blank cells is in Column A...

1) Select all of the data, both blank and non-blank cells, in a single range (e.g. A2:A38);

2) Press the F5 key on your keyboard and click the Special... button in the Goto dialog;

3) Select the Blanks option and click OK. Only the blank cells are now selected;

4) Type = and press the Up arrow key once;

5) Hold down the CTRL key and press Enter;. The puts a formula referencing one cell above in every cell that was previously blank;

6) Re-select the entire data range again (same as step 1);

7) Right-click, Copy;

8) Right-click, Paste Special...,Values, OK.
11/11/11

Ensure Values Entered In A Range Are Correct Length 

If you want to ensure that the correct number of digits/characters are entered for data such as part #s, serial #s, invoice #s, etc... you can do it using Data Validation.

1) Select the range of cells;

2) From the Data tab, click Data Validation in the Data Tools group and then Data Validation from the options. (Excel 2003 users, from the Data menu, select Validation);

3) Click the Settings tab and in the 'Allow' dropdown, select 'Text Length';

4) In the Data dropdown, select 'Equal to';

5) In the Length field enter 7 (the required length of the input data);

6) Optionally, you can customize the error message that appears when someone inputs an invalid value by clicking the Error Alert tab, entering a title for the error dialog and a message to the user.

7) Click OK.

It is important to note that, although Data Validation is very useful, there are some serious limitations which could possible cause problems.

·If you copy a cell that does not use Data Validation and paste it over a cell that does use Data Validation, the validations rules are overwritten and any value can be entered in the cell.
·Data Validation doesn't apply to the results of formulas.

·Unless the Stop option is selected in the Style dropdown on the Error Alert tab, any value can be entered.
8/19/11 Filtering
  1. In an open Excel file, select Data from the menu bar.
  2. Click on Filter
  3. Click a dropdown arrow in the column to be filtered.
  4. Put a checkmark by the data you would like to see.
  5. The list will change to display only the selected information.
  6. Turn off the filter by selecting the Filter again.
5/27/11

AutoSum

Everyone uses Excel's AutoSum button for quickly summing values. Did you know there is also a virtually unknown keyboard shortcut that performs the same function. This is great for times when you'd rather use your keyboard.
 
Simply press the Alt key plus the equal sign (Alt + =)

Once you get used to using this shortcut, you can avoid reaching for the mouse when you already have your hands on the keyboard.

4/25/11

How to Paste a Hyperlink


1) Open the workbook where you want to paste the hyperlink

2) Open the workbook that you want the hyperlink to jump to. This can be the same workbook or another workbook. This cannot be a new, unsaved workbook

3) Navigate to the cell you want the hyperlink to jump to

4) Copy that cell

5) Switch to the workbook where you want to paste the hyperlink

6) In Excel 2007-2010, on the Home tab, click the small arrow below the Paste button and click Paste as Hyperlink. In Excel 2000-2003, from the Edit menu select Paste As Hyperlink. Note that if the workbook you are copying from is a new unsaved workbook, the Paste as Hyperlink option will be greyed out;

The contents of the cell you are copying will determine what will be displayed in the hyperlinked cell.

If the copied cell contains text, that same text will be displayed in the cell containing the hyperlink.

However, if the copied cell contains a formula, a numeric value, or a blank cell, the full path (file name, worksheet name, cell reference) of the cell will be displayed in the cell containing the hyperlink. If the copied cell is from within the same workbook, only the sheet name and cell reference will be displayed.

Once you have pasted a hyperlink, you can change the text displayed in that cell without changing the hyperlink.

For example, you can copy a blank cell (A1) from another workbook and paste it as a hyperlink. The cell containing the hyperlink will now be showing the full path of the copied cell. You can then type some other text in that cell to replace the long displayed text. 

Now you can click on the hyperlink and instantly the source file will be opened and you will be taken to the linked cell.

4/1/11

Shortcuts keys

While in edit mode...

  • To jump to the beginning or end: Press Home or End;
  • To jump one word right or left: Press CTRL + left/right arrow keys;
  • To select text to copy: Drag with the mouse (or use the Shift + arrow keys).
  • To copy: Select text and press CTRL+C (or right-click, Copy);
  • To cut: Select text and press CTRL+V (or right-click and Paste);
  • To paste: Position insertion bar and press CTRL+V (or right-click, Paste);