Microsoft Excel

FREQUENTLY ASKED QUESTIONS:

  1. What is that formula again?
  2. How do a do a PMT formula ?
  3. What is Auto Fit and how do I use it?
  4. Fill Handles; What are they and how do I use them?
  5. Macro's
  6. How do I open up a toolbar?
  7. How do I customize a toolbar?
  8. All about Charts
  9. Rotation of text in your chart
  10. Adding colors to your charts
  11. How do I add text boxes?
  12. How do I copy information from the above cell down? 

 

Formula's

Formula's  in Excel are relatively simple, as long as you remember a few rules.

  bullet ALWAYS Begin the expression with the = sign

        Make sure that you don't hold the shift key down when you hit the = key. 

·         ALWAYS follow the rules of Validation.

        MDAS--- Multiplication, Division, Addition, Subtraction

Formulas:

=Today() Gives you today's date  

=Now() Gives you today's date and the current time

=Sum() Finds the sum of a specific range

=Max() Finds the Maximum number of a specific range

=Min() Finds the Minimum number of a specific range

=Avg() Finds the Average of a specific range

=Count() Counts the number of cells in a range (even blank ones) 

=CountA() Counts only the cells that are not empty 

=IF, Than() Ok on this one you specify a true/false statement

                  i.e. =IF(A1>=$57.00,A1*10%,A1)

This statement says if the cell contents of A1>=$57.00 then multiply that cell contents by 10%, if not, show the amount in A1.

How do I set up a PMT formula ?

Calculates the payment for a loan based on constant payments and a constant interest rate.

Syntax

PMT(rate,nper,pv,fv,type)

For a more complete description of the arguments in PMT, see PV.

Rate   is the interest rate for the loan.

Nper   is the total number of payments for the loan.

Pv   is the present value, or the total amount that a series of future payments is worth now; also known as the principal.

Fv   is the 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   is the number 0 (zero) or 1 and indicates when payments are due.

All About AutoFit

Autofit is generally used after you have completed your spreadsheet.  It looks at all your columns and automatically resizes that column to fit the largest entry.  WAY COOL!

There are several ways to resize your column.  

  1. The hardest way is to Click on Format on the menu bar point to column, width, type in the width.
  2. In the column indicator (IN THE GRAY). Take your mouse point get on the line that separates the two columns   Click and drag out.
  3. OR USE AUTOFIT:

To Autofit just a column, Double click in the top Right hand corner of the column indicator.  It will adjust the entire column.

To Autofit the entire Sheet, select the "Select all button" (The gray rectangle in the upper-left corner of a worksheet where the row and column headings meet.) Point to format on the menu bar, column, autofit selection.

                   

 

Fill Handles; what are they and how do I use them?

Your fill handle is the little black box in the lower right hand corner of your cell.

Examples of Fill handles:

What is a Macro and how do I use it... ?

A macro is a short cut way of doing repetitive keystrokes.

When doing a macro, make sure before you start typing that you start RECORDING!

How do you record a macro.  Simple:

  bulletPoint to tools on the menu bar  
  bulletPoint to macro  
  bulletRecord new macro  
  bulletName your macro & decide if you want a shortcut key assigned to it.  
  bulletStart typing.... Formatting... calculating  
  bulletWhen finished, hit the little blue box in the "stop recording" toolbar  

To Run your macro:

  bulletPoint to tools on the menu bar  
  bulletPoint to macro  
  bulletMacros  
  bulletFind your macro and press "Run"  

Adding a macro to the menu list:

bulletOpen the Customizing window
bulletUnder the Categories, select Macros
bulletSelect either "Custom Menu Item" or " Custom button" 
bulletDrag it into the toolbar or up into the Menu bar itself  
bulletRight click on the word "Custom Menu Item" or " Custom button" 
  bulletRename it   
  bulletThen YOU MUST ASSIGN A MACRO TO IT> That is the last option under the right click.  
  bulletYou can also change the picture by right clicking.. Change button image.  

How to open/close a toolbar ?

Point to View on the menu bar, toolbars, select the toolbar you want. OR right click on the menu bar it will show you all your toolbars. If it has a check, then it is open.  Remember also that the Drawing tool bar has an icon on the Standard toolbar!

 

Customizing a toolbar...

To customize a toolbar. 

  bulletPoint to view on the menu bar or right click anywhere in the toolbars  
  bulletPoint to toolbars then down to customize .
  bulletOpen the command tab.  
  bulletDrag the icon you want up in the toolbar  
  bulletTo delete an icon off the toolbar.  Simply drag it down into the command tab.  

All about Charts....

Creating a Chart...

  bulletSelect the cells that contain the data that you want to appear in the chart. If you want the column and row labels to appear in the chart, include the cells that contain them in the selection.  
  bulletClick Chart Wizard  
  bulletFollow the instructions in the Chart Wizard.  

Fonts and rotating text

You can format text in a chart as you would any other text. Click the text or item, and then change the font, size, and color by clicking the buttons on the Formatting toolbar.     

 

You can rotate, or "angle," text in a chart title or along an axis. You cannot rotate legend text.

  bulletClick the axis or the title you want to format.  
  bulletIf you clicked an axis, click Selected Axis on the Format menu. If you clicked an axis title, click Selected Axis Title on the Format menu. If you clicked a chart title, click Selected Chart Title on the Format menu.  
  bulletClick the Alignment tab.   
  bulletIf you don't see the Alignment tab, click Cancel, click outside of the text you want to format, and then repeat steps 1-3.  
  bulletTo rotate text, under Orientation, click a degree point, or drag the indicator to the position you want.  

Tip   To quickly rotate selected text 45 degrees up or down, click Angle Text Upward or Angle Text Downward on the Chart toolbar.

Changing/Adding Color to your chart

  1. Double-click the chart item you want to change or select "chart objects" from the Chart toolbar. (You can also Right click the item then select Format Data Series.)
  2. Choose a color or...

3.      To specify a fill effect, click Fill Effects, and then select the options you want on the Gradient, Texture, or Pattern tabs.

 

NOTE:  Notice that I only formatted one as texture..... You can do all the bars in a series OR just one  BE CAREFUL!  Make sure you select the entire series!

How did I add text boxes and arrows?

  bulletOpen your drawing toolbar.. Need to know how?  
  bulletTo add a text box  

How do you copy the contents of the cell above into the active cell?

Simple!

 

Main Menu