Archive for 'Excel'

Don’t “eyeball” documents or webpages for words you’re looking for.  Save yourself time and do a keyword search.

Find Keywords in a Microsoft Office Document (Word, Excel, PowerPoint)

  1. Home Ribbon > Find & Select (right side of Home tab) > Find.  OR  CTRL + F is the keyboard way to start a Find.
  2. Type the word you’re searching for.
  3. Click on [Find All] for a list of each instance of the word or phrase in your list.
  4. Press [Esc] or click [Close] to clear the dialog box.

Searching a document will check all pages of the document.

 On a website, use the keyboard:   CTRL + F 

Even on a banking website, you can use CTRL +F to search for a check number.

Do you like learning the shortcuts?  Our Microsoft Word Shortcuts “Cheat Sheet” is now available. Click here to learn more.

A very common scenario in Excel is creating spreadsheets that report numbers for specific time periods. For example, weekly, monthly, quarterly, or annual reports.  This means you will continually be re-creating the same spreadsheet going forward in time – other than the variance in reported data.

You don’t want to start from scratch. Make quick work of your next report – duplicate the sheet, then edit the details. 

Some of you might be asking “Does she mean copy-and-paste?”  NO!  Though it can be accomplished by copying the info from the previous spreadsheet to a new blank sheet, it will take more work than the duplication method because there are several things that do not come over with the copy-and-paste, including:

  • Widened columns.
  • Changed row heights.
  • Print settings, including headers and footers.

To duplicate a sheet:

  1. Drag the sheet tab to the right while holding the CTRL key down.
  2. When you see a little black triangle appearing on the right side of the sheet tab, release your mouse before releasing the CTRL key.  The new worksheet will be named the same with a (2) after it. Example: A duplicated March sheet will be March (2). 
  3. Now edit the details of the duplicate sheet.  Change the sheet name, edit time period labels, input your new data.

Quick and easy you are done with your report and leaving your office early! (Until the boss decides they need to give you more work 🙂

Tags: , ,

When your Excel file exceeds the screen view, and you scroll down or to the right in order to view your data, the row and/or column labels also scroll away from view.  Data becomes meaningless when you can’t see what it belongs to. 

Freeze Panes allows you to “freeze” the row/column labels (for example, “Jan, Feb, Mar, Total”) in view so they continue to show on screen even as you scroll down through document.  Freeze Panes does not affect printing (we’ll cover print-related issues later).

Where you click before you “freeze” is important. Excel will freeze above and to the left of where your cell selection is.  For example, if all you need to freeze is your top row of labels on row 1, click on cell A2 when you Freeze Panes.  If you also need the A column to stay in view when you scroll to the right in a wide spreadsheet, click on cell B2.

            To turn on Freeze Panes:   View  >  Freeze Panes. Choose rows or columns or both.

            To turn off Freeze Panes:  View  >  Unfreeze Panes







If you would like to figure out what a loan payment will be, use Excel’s PMT function.


Rate = Interest rate for the loan. Because the interest rate is per annum, you must divide it by 12.
Nper=Number of payments, expressed in years x 12.
Pv=Present Value, otherwise known as the principal or loan amount
Fv=future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
Type = (Optional) When payments are due. (Zero or omitted=end of period, 1 is at beginning of period).

The following is an example of a mortgage loan payment calculation:

The function typed in to cell B7 in this illustration is =PMT(B5/12,B6*12,B4).  B5 is the interest rate, which must be divided by 12 because it is an annual rate. B6 is the loan term, which must be multiplied by 12 for the 12 payments per year. B4 is the loan amount. In this example, there is no need to include Future Value or Type. 

Note:  There is also a PPMT function.  PPMT returns the principal portion of a specific loan payment; where PMT returns the full, fixed payment for a loan.

Tags: ,

Everyone makes data entry errors. You need to correct errors quickly so you can move on with completing your work. In Excel, here are some ways to do that.

If the error just occurred:

1. [ESC] key on the keyboard. If you are still typing and have not hit [Enter] yet, press [ESC]. This cancels what you’ve begun to type in to a cell, but have not confirmed yet. (If you have already hit [Enter], see instructions below).
2. Click the Red X on the formula bar. This is the same thing as pressing [ESC]. The red X will not be available if you have already entered the data. This is only if you are still typing in the cell.
3. UNDO: If your error is recent, you can Undo. To undo:
Keyboard method – CTRL Z; (This one works in almost all programs)
Toolbar method – Undo Button (Left-facing blue arrow)
Menu method = EDIT > UNDO

If the error has already been entered, you have 2 options:

1. Click on the cell and simply re-type the correct entry. You do not have to press delete first, though that would work as well. The correction can be re-typed in the cell or on the formula bar.
2. Delete: Highlight the cells to delete data from, press [DELETE] on your keyboard.

Remember – UNDO is your best friend!

Tags: , ,

Have you been using Microsoft Word or Excel without formal training – and thinking of taking an intermediate level class?

 The beginning level training covers key foundational concepts you may have missed without training.  Here is a checklist to help you decide if you’re ready for intermediate.  But first, let’s start with your basic Windows knowledge before we take a look at your Word and Excel skills.  Are you able to…

  1. Navigate Windows through icons, folders,  and menus.
  2. Open programs – including Word and Excel.
  3. Use window controls (close, minimize, restore), toolbars, menus, and scroll bars.
  4. Use window views.
  5. Identify which window or program you are in.
  6. Create, save, and name files.
  7. Find and open files you created.
  8. Send a file as an email attachment.

 These are basics a beginner should know using any Microsoft Office program:

  1. How to set margins.
  2. Select text and navigate documents.
  3. Copy and paste.
  4. Spell check.
  5. Change the look of text (change font, make it bold, underlined, or blue).
  6. How to use help.
  7. How to create page breaks.
  8. File > Save, Open, or Close.
  9. Undo.
  10. Print

 Microsoft Word

Specific concepts and features you should know in Word. You can… 

  1. Jump directly from top to bottom of your document without scrolling.
  2. Select words, sentences, paragraphs, lines by methods other than click and drag.
  3. Use the Show/Hide button on the toolbar and know what it is for.
  4. Name two examples of text formats.
  5. Name two examples of paragraph formats. (Formats that apply to paragraphs).
  6. Describe how Word knows what a paragraph is.
  7. Create three types of indents using the ruler.
  8. Describe what AutoCorrect is and what it does.
  9. Double space your text.
  10. Switch between Normal View, Print Layout View, and Print Preview, and know the difference between them.
  11. Turn bullets and numbering on/off.


You may be ready for Intermediate Excel if you know the following:

  1.  How to widen columns, change row heights.
  2. Design a basic spreadsheet.
  3. The formula bar and its function.
  4. Create basic formulas, to add, average, subtract, divide.
  5. How to automatically fill months of the year across columns.
  6. Move cell contents by drag and drop.
  7. The affect on formulas when copied to other cells.
  8. How to add/remove decimal places.
  9. Format a number as a percent.
  10. Center a spreadsheet on the printed page, and other print settings such as landscape/portrait orientation.
  11. Excel’s pointer modes and what tasks they are for.
  12. AutoSum.
  13. AutoCalculate for a quick total, average, or count without a formula.
  14. How to write a formula with multiple operators (i.e. adding and multiplying in the same formula).
  15. How to add, delete, rename sheets.

If you find yourself unsure about 1/3 of these features, consider taking the beginning class. You will be amazed at how much you learn.

See you in class!

Tags: , , ,

Need a quick total or count without having to write a formula?  Excel’s AutoCalculate will show you a total, a count, an average, or other calculations by simply selecting the cells you are calculating.  Look on the lower right hand portion of the status bar to see the calculated result.

Sum is the default calculation.  To change the type of calculation, right-click on the status bar and choose from the pop-up menu which calculation you want to perform.


Tags: ,

Charts are a great way to visually represent and compare data. In
Excel, the key ingredients for a meaningful chart are:

1. Accurate data selected correctly for preparing the chart.
2. The right type of chart for the type of data to be displayed.

QuickTip for an instant column chart

1. Select the comparison data (and labels for the data) to be
   charted.  Example: Sales figures for Jan through June
2. Press [F11] at the top of your keyboard.

If you selected the correct data, you should now have a column chart!

The number one thing you can do to increase your personal
productivity is to get training – and then practice what you learn!

Tags: ,
Back to top