EXCEL 2007 in a Nutshell

EXCEL 2007 in a NutshellPage 1 of 7

  1. Entering Formulas
  • Formulas in Excel are calculations performed on cells in a worksheet
  • Formulas must begin with an EQUAL SIGN (=)
  • Common formula operations include:

+ (add) - (subtract) * (multiply) / (divide)^ (exponent)

  • You can use numbers in a formula

=2+3=457/12=3^2

  • You can use cell addresses in a formula

=A2*2=20-B7 =C3*D2

  • Formulas follow the rules of order of operations or BEDMAS.
  1. Using Ranges

A Range is a block of cells in a worksheet. Ranges are used to:

a)quickly apply formatting throughout cells in the range

b)perform calculations on cells in the range

c)perform an action using cells in the range, such as creating a chart

A range can be either:

  • Contiguous where the cells are adjacent to each other
  • Example: A2:B5
  • To select, click and drag the mouse pointer from the first to the last cell in the range
  • Noncontiguous where the cells are not necessarily adjacent to each other
  • Example: A2, B3, D1:D3
  • To select, hold the Ctrl key down and click on each cell address and/or select a contiguous range as indicated above
  1. Adding & Deleting Rows and Columns

Select the entire ROW or COLUMN by placing the mouse pointer in the row or column heading (gray area with the row number or column letter)

Use the Mouse: Right-click and select Delete or Insert

OR

On the Home Ribbon : InsertColumnsto insert a column

InsertRowsto insert a row

INSERTING COLUMNS: new columns are inserted to the left of the selected column

INSERTING ROWS: new rows are inserted above the selected row

  1. Using Functions

Functions are pre-defined formulas in Excel

Common Functions include:

FUNCTION / DESCRIPTION / EXAMPLE
=SUM( ) / Adds a range of cells together / =SUM(B2:B6)
=AVERAGE( ) / Calculates the average of a range / =AVERAGE(B2:F2)
=COUNT( ) / Counts the # of values in a range / =COUNT(A2:A8)
=MAX( ) / Identifies the highest value in a range / =MAX(C3:C12)
=MIN( ) / Identifies the lowest value in a range / =MIN(C3:C12)

Other Statistical Functions you will use include:

FUNCTION / DESCRIPTION / EXAMPLE
= MEDIAN() / Finds the middle value of a range of cells / = MEDIAN(A2:100)
= MODE() / Finds the most frequently occurring value in a range / = MODE(B3:B45)
= QUARTILE() / Finds a specified quartile in a range / = QUARTILE(A2:A100,1)
= QUARTILE(A2:A100,2)
= QUARTILE(A2:A100,3)
= STDEV() / Calculates the standard deviation of a range of cells / = STDEV(A2:A100)
=CORREL() / Calculates the correlation coefficient of two ranges / =CORREL(A2:A100,B2:B100)

Functions can operate on both a contiguous and non-contiguous ranges.

Using a contiguous range: =SUM(C2:E2)

Using a non-contiguous range: =SUM(C2, E2, F2)

Using contiguous & non-contiguous range: =SUM(C2, E2:G2, J2)

Entering Functions

  • You can enter functions manually by typing and/or using the click & drag method
  • You can enter functions using the Insert function button at the beginning of the formula bar
  • Find the function you need using the search or category list in the Insert Function dialog
  • Specify arguments for the function by typing
    them or using the click & drag method
  1. Charts and Graphs

A chart or graph presents data in a graphical format so relationships or patterns between data items are easier to understand and analyze.

Although there are many different types of charts in Excel, the three most common charts used in business are:

  • Scatter Plots/Line Graphs

Typically used to connect points of data and show changes over time (trends)

  • Bar or Column Charts

Typically used for side by side comparisons of data

  • Pie Charts

Typically used to show the contribution of each data item to the total

TO CREATE A CHART

1)Select (highlight) the data in the table that you want to plot in a chart

  • Select the ranges of data you want to include.
  • Hold the Ctrl key to select non-contiguous ranges (cells that are not connected side-by-side).
  • The selection should include the labels for the data (if possible)

2)From Insert Ribbon: Click on Chart Typeof your choice(from Charts Section)

3)Once your chart has been created, the Chart Tools Ribbon becomesavailable, enabling the custom design of your chart:

4)Format & specify Chart Options in the following sections:

  • Chart Tools Ribbon: Layout Labels & Axes Sections
  • Chart Title: describes the content of the chart
  • Axis Titles:
    - X-axis: horizontal axis that typically describes the “categories”
    - Y-axis: vertical axis that typically represents the “values”
  • Legend: is a key that describes information about the data
  • Data Labels: elect to show or hide the series name, category name, and values for each data item
  • Data Table: elect to show or hide the excel table below the chart
  • Axes Tab: elect to show or hide the axes values/labels
  • Gridlines Tab: elect to show or hide vertical and horizontal gridlines in the chart area

5)Specify Chart Location:

  • Chart Tools Ribbon: Design Tab Location Section Move Chart

A dialogue box will appear providing you with the following options:

  • Chooseas New Sheet to place the chart in a new worksheet tab within the workbook. You must specify a name for this new sheet
  • Choose as Object In to place the chart as an embedded object in an existing worksheet

If you prefer, you can achieve the same as above by
Right Clicking anywhere in your chart and from the
menu, click Move Chart

FORMATTING AN EXISTING CHART

Once a chart is created, you can change different features of
the chart to enhance the way it looks through the Design and
Format Tabs available via the Chart Tools Ribbon…or by right-
clicking on different parts of the chart.Opt to change colours,
set backgrounds, specify a measurement scale, and so on.

Hours / Frequency
0-5 / 3
6-10 / 4
11-15 / 5
16-20 / 1

CREATING A HISTOGRAM

  1. Create a mini-table with interval ranges and frequencies
  2. Select the entire range of the table and create a column chart
  3. To remove the spaces between the columns in the graph:

a)Right clicking on one of the columns

b)Choose Format Data Series

c)Change the Gap Width to zero (0)

  1. Use Auto Calculate

Use the Auto Calculate button in the Standard toolbar to quickly enter functions.

  1. Position the active cell at the location where you want to enter the function
  2. Click the Auto Calculate button for SUM or click the down arrow for other common Auto Calculate functions such as AVERAGE, MIN, MAX, and COUNT
  3. Hit Enter to accept the default range specified, or use the click & drag method to select a new range and hit Enter. (Remember to hold the Ctrl key down if you need to select non-contiguous cells).
  1. Sorting Data

The method you use to sort data in an Excel spreadsheet depends on if you need to sort the contents of one column only, or of multiple columns

Sorting contents of one column

  1. Click in a cell in the column you would like to sort
  2. Use the Sort Ascending or Sort Descending toolbar icons

Sorting Contents of Multiple Columns (Up to Three Columns)

  1. Highlight the entire range of data you wish to sort
  2. From the Menu: DataSort
  3. Choose up to 3 columns to sort on by selecting a column in Sort by, Then by, and Then by.
  4. For each column, choose an Ascending or Descending sort.
  1. Absolute & Relative Cell References

When copying formulas from one location to another:

Relative Cell References (B4) automatically change depending on where the formula is being copied to

Absolute cell references ($B$4) stay the same, no matter where they are copied to

Mixed cell references ($B4 or B$4) automatically change the relative portion of the reference but retain the absolute portion of the reference

Example:

  • If cell D1 = A1+B1 (relative references) is copied to cell E2,
    the formula in E2 becomes B2+C2
  • If cell D1 = $A$1+$B$1 (absolute references) is copied to cell E2,
    the formula in E2 stays $A$1+$B$1
  1. Calculating Percents

To calculate a percent value, you need to divide the “part” by the “whole”.

For example:

Take the number or amount of an individual item and divide it by the total number or amount of all items

Use an absolute cell reference when creating formulas to calculate percent values. It will make your job easier when using AutoFill or copy & paste to complete the rest of the calculations

Finally, use the percent toolbar button to format the values as a percent

EXCEL 2007 in a NutshellPage 1 of 7