Give a Cell or Range of Cells a Name


You first select the cell or range of cells you want to name and then click on the cell reference in the name box (located at the left end of the formula bar) type the name for the cell or range and then press enter.

You must keep the following in mind; use the underscore or hyphen in place of all spaces and use abbreviated, meaningful names.

Example.

cell-Name cell-NameText

Blog Post by Thomas

Create and use custom lists


If you have lists you use on a regular basis you can create custom lists. You can now use these instead of using copy and paste to create the lists.

  • Open the Worksheet containing the list you want to use.
  • Highlight the whole list.
  • In Excel 2003 go to Tools/Options/Custom Lists and in 2007; click on the Office Button/Excel Options/Edit Custom Lists.
  • On the Custom List dialog box you will notice that the cells you have highlighted are displayed in the “Import list from cells” text box
  • Click on the Import button

customList

  • Click on OK in the dialog box.
  • Click on the OK button in the Options Window.
  • Your list is now ready for use
  • To use the list.
  • Type one of the names in your list and press Enter. Select that cell and then use the AutoFill button to drag the selection as far as you have to complete the list.
  • You can create as many list as you require.

Blog Post by Thomas

Pivot in layman’s terms


PivotTable reports and PivotChart reports

A PivotTable is basically a three dimentional interactive view of two dimentional data.  It allows you to quickly summarize large amounts of data.  You can use a PivotTable to summarize, analyze, explore, and present your data quickly and easily. It also allows you to easily see comparisons, patterns, and trends.

Both a PivotTable report and a PivotChart report enable you to make informed decisions about critical data in your enterprise.

Blog Post by Thomas

Formula Function in Microsoft Word Tables


Use the formula function in tables to do basic calculations directly in MS Word tables.

1. Open MS Word
2. Click on the Insert Tab
3. Click on Table
4. Select 2 columns and 5 rows
5. Enter the information as indicated below
6. Place your cursor in the cell to the right of the total
7. On the Table Tools Contextual tab select the Layout tab
8. In the data group click on the Formula icon
9.  Notice that =SUM(ABOVE) is displayed in the formula text box
10. Click in the Number format box and select 0.00 on the drop down arrow
11. Click on OK
12. Your answer will be displayed in the Cell

Item 1 251
Item 2 501
Item 3 325
Item 4 87
Total 1764.00

Formula

If any of the numbers change, right click on the answer and select Update field from the available options Your new answer will now be shown.

Blog Post by Thomas

Auto-Text in MS Office


Auto-Text can help you perform tasks quicker

Use the Auto-Text feature to help you enter long, often used words.  This will allow you to use abbreviations instead of having to type out the full words.

1. Click on the Office Button.
2. Click on the Auto Text button
3. Select the AutoCorrect tab in the Autocorrect dialog box that will be displayed
4. Type the abbreviation you want to use in the text box on the left and the full word/sentence on the right.
5. Click on Add
6. Repeat steps 4 and 5 for any other entries you want to use.
7. Click on OK when finished
8. Now when you need a long word or sentence, just type the abbreviation you created and press the spacebar or enter and the abbreviation will be replaced by the long word or sentence.

Blog Post by Thomas

Bookmarks explained


bookmarks

Bookmarks in MS Word or any electronic document works the same as a marker placed in a paper based document to show where you are in the document. Once you have placed bookmarks in your document, you can use the Go To function to jump to any of the bookmarks.

Blog Post by Thomas

Delete Blank Rows in Excel


Blank rows or Blank cells is a problem we all inherit one time or another. This is very common when you try to import data from somewhere else (like a text file or a CSV file). Today we will learn a very simple trick to delete blank rows from excel spreadsheets.

  • Select your data
  • Press F5
    This opens “Go to” dialog in Excel. Now hit on that “select” button.
  • From “select special” screen, select “Blanks” (shown aside)
    Now, all the blank cells will be selected.
  • Just press CTRL and Minus sign (-)
  • Select “shift cells up” or “entire row” as needed.

That is all. Now you have successfully removed blank rows.

Bonus tip:

If you are looking for keyboard short-cut for this, here it is. Press them in the same order once you select the cells.

Blog Post by Johan