EXCEL course intermediate with examples


Télécharger EXCEL course intermediate with examples

Formation Excel en ligne par vidéo

205 leçons vidéos + 20 Livres PDF + 20 TP + Sous supervision + Certificat de réussite à la fin du cours



★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


 H & H Associates

 

Excel Intermediate &

Advanced

     

 

Contents

 Contents                                                                       2

 Excel Intermediate                                                                                                                                    1

 Further techniques to format spreadsheets . 1

 Use more functions  ..                          3

 The Date Function                                                                           6

 The Days360 Function in Excel 2007 .                                                                                   10

 Use the functions wizard .                                                                                                                      13

 Parts of a Function 13

 Inserting Functions . 14

 Inserting Functions: Dialog Box Option  .. 14

 Inserting Functions: Ribbon Option . 17

 About the Function Arguments Dialog Box .. 18

 Inserting Functions with the Point and Click Method . 19

 Adding Cells Together  ..  19

 Adding a Range of Cells with the SUM Function 19

 Use Absolute cell references in formulas .. 20

 Use Excel as a database .                             23

 Creating Tables and Lists                                                               23

 Tables of data ..                                                                                                                        23

 To follow this tutorial: . 23

 Tip - To enter the Student ID's quickly:  .. 23

 Entering Data Correctly     24

 Entering Data Correctly                                          24

 Rows are Records .                                                                                       25

 Rows are Records .                                                                                                                                    25

 Columns are Fields 26

.

 Columns are Fields 26

                      Creating the List  ..                             ..                                                 27

 Create the list                                                                                               27

 Using the Database Tools ..                                                                                                                              28

 Using the Database Tools .. 28

 Expanding the Database . 29

 Expanding the database  .. 29  Expanding the database  ..    29  ormatting the Database                                         30  Formatting the database: .                                                                            30

 Use filters and subtotals in a data base .                                                                                     31

 Create a Pie Chart / Pie Graph in Excel 2007 .                                                                                31

 Entering the Chart Data                                                                                                                      32

 Enter the Chart Data . 32

 Select the Pie Chart Data 32

 Two Options for Selecting the Chart Data  . 32

 Selecting a Pie Chart Type  . 33  Selecting a Pie Chart Type .    33  he Basic Pie Chart .                                                   34  Formatting options for the pie chart .                                                                    34

 Formatting the Pie Chart - 1                                                                                                   35

 Formatting the Pie Chart - 1 35

 Formatting the Pie Chart - 2 36

 Formatting the Pie Chart - 2 36

 Formatting the Pie Chart - 3 37

 Formatting the Pie Chart - 3                            37

 Create drawing objects .                                                                   38

 How to Draw a Shape on an Excel 2007 Spreadsheet                                        39

 Add a Text Box to an Excel 2007 Spreadsheet ..                              43

 Use hyperlinks .                                                                                     47

.

 Excel Advanced 51

                      Use named ranges in formulas...                                                         . 51

 Define a Name .                                          51

 Apply a Name ..                                                                                                 52

 Using Named Ranges in Formulas .                                                                                                            54

 Use lookup functions . 56

 Syntax . 60

 Remarks  .                                61

 Example 1 .                                                                                              61

 Example 2 . 64

 Example 3 . 65

 Link multiple workbooks  .. 68

 Import data                                                            68

 CSV Files Explained .                                                                                                      68

 Working With CSV  .                        69

 Additional Resources                                                                 70

 Use pivot tables and charts .                                                                                                 70

 What is an Excel Pivot Table? ..                                                                                                                         70

 Understanding Pivot Table Structures 71

 How to Create an Excel Pivot Table 71

 Additional Groupings and Options .. 73

 Troubleshooting Pivot Tables 74

 Use templates and styles 74

 Using the installed templates  . 74

 Downloading templates                        76

 Analyse data .                                                                                   78

 Change Excel 2007 spreadsheets to tables for easier data analysis .                                             78

 Create and use macros                                                                                 79

 Excel Macro Recorder Tutorial ..                                                                                                         79

 Adding the Developer Tab .. 80

 Adding the Developer Tab...... .. 80

 Adding a Worksheet Title / The Excel Macro Recorder .. 81

 Adding a Worksheet Title     81

 Excel Macro Recorder ..                                           81

 The Macro Recorder Options .                                                                    82

 The Macro Recorder dialog box  82

 Adding the Macro Steps ..                     83

 Recording the Macro .                                                               83

 Running the Macro .                                                                                                             84

 Running a Macro in Excel 84

 Macro Errors / Editing a Macro .. 85

 Macro Errors . 85

 Editing/Step Into a Macro     85

 Protect a spreadsheet ..                                            86

 Protection Options .                                                                                           86

 Locking & Unlocking Cells ..                                                                                                                           86

 Locking Cells 86

 Unlocking Cells 87

 Workbook Level Protection .         87

 Protecting the Workbook .                                           87

 Unprotecting the Workbook                                                                       88

 Worksheet Level Protection                                                                                                    88

 Protecting the Worksheet 88

 Unprotecting the Worksheet . 89

 File Level Protection .. 89

 Protecting the File  .. 89

 Unprotecting the File                                        90

 Consolidate a work book .                                                                          90

 What do you want to do? .                                                                                                            90

 Consolidate by position 91

 Consolidate by category......  ..      92

 Consolidate by formula .                                             93

 Use a PivotTable report to consolidate data .                                             94

 Appendix – Definitions & Resources  .. 94  Resources  .                                              94


Excel Intermediate

Further techniques to format spreadsheets

Renaming Worksheets

Double click on the tab you wish to rename

Enter the new name

Or right-click on a worksheet tab and use a pop-up menu

Deleting and Inserting Worksheets

You can insert and delete worksheets:

By clicking on the relevant worksheet tab with the right-hand mouse button and selecting Insert or Delete from the pop-up menu

OR

By selecting Worksheet from the Insert menu, or Delete Sheet from the Edit menu

 Finding Files

 

Allows you to easily locate files stored on disk

Displaying Other Toolbars

From the View menu, select Toolbars and pick from the sub-menu displayed Or right click on any toolbar to see a pop-up list

Customizing the Look of the Toolbars & Menus

Right click on any toolbar

Select Customize

Select the Options tab

 Freeze Panes

To enable you to see which row or column you are in when you are working on a large worksheet, you may wish to lock the row or column headings

To do this, use Freeze Panes

Grouping and Ungrouping Worksheets

Allows you to edit and format several worksheets at the same time

Changes made to the active sheet will be reflected in all sheets in the group

Use more functions

Excel's COUNT is one of a group of Count Functions that can be used when you need to total the number of cellsin a selected range.

The COUNT function's job is to only add up the cells in a selected range that contain numbers. It ignores empty cells or those contain text. If a number is later added to an empty cell the function will be automatically updated to include this new dat

Simple Expense Tracking With New Excel 2007 Functions -SUMIFS

Today’s author, Monica Poinescu, a Software Developer in Test on the Excel team, gives an overview of some new functions in Excel 2007 and along the way shows us how to do some simple expense tracking.

Considering the current economic environment, one may desire to better keep track of personal expenses. The new functions introduced in Office Excel 2007 simplify this effort. Here is how: given a list of expenses sorted by dates and categories (food, travel, clothing, etc.), how do I find how much was spent for each category, per month? And what’s the average amount spent? The functions SUMIFS, AVERAGEIFS and COUNTIFS perform selective calculations: they take into account only those values which fulfill several criteria. The values could be in a range spanning several rows and columns.

Let’s assume I entered the expenses in a table:

 

The solution below uses the new Excel 2007 feature structured references; see 'Tables Part 3: Using Formulaswith Tables'for more details on how to simplify your formulas based on tables.

I’m also using the function SUMIFS which extends the functionality of SUMIF: given a range to be summed, it will add only those values that fulfill all the given criteria (one could use between 1 and 127 criteria applied to corresponding ranges).

The syntax is SUMIFS( the_range_to_sum, range_to_apply_criteria1, criteria1, [range_to_apply_criteria2, criteria2], … ) where the first parameter tells Excel what range we want to sum and the following pairs of arguments tell Excel on which range to apply the respective condition.

In visual terms, I think of each pair (range_to_apply_criteria1, criteria1) as a punch cardwith some (0 or more) perforations, in the places where the criteria is TRUE. Stack all these cards on top of each other, the_range_to_sum being on the bottom, and SUMIFS adds only the values that remain visible. 

Then start building your reports table by entering in F1:I3 the categories and the months:

 

and in G2 enter:

=SUMIFS( Table1[Amount], Table1[Category], G$1, Table1[Date], $F2 )

which will evaluate the ‘home’ expenses for January (235.35).

It should look like this:

 

Similarly, in H2 enter:

=SUMIFS( Table1[Amount], Table1[Category], H$1, Table1[Date], $F2 ) this will evaluate the ‘food’ expenses for January .

And in I2 enter:

=SUMIFS( Table1[Amount], Table1[Category], I$1, Table1[Date], $F2 ) to get your travel related amount.

You can copy/paste these formulas down as you add more months. This will automatically take into account new entries in your expense list. Also, in case you need to figure the average expense per category, within each month, you can use:

=AVERAGEIFS( Table1[Amount], Table1[Category], "home", Table1[Date], "January" ) - this returns 117.675

The Date Function in Excel 2007

Date and Time functions are quite common when you're creating spreadsheets, especially if you need to create a spreadsheet for work. What was the date of the order? How long did it take to process the order? How many days overdue, if any, is the payment? In the next part of this Excel 2007 lesson, you'll see how to theTime function. First up, though is how to handle dates.

The Date Function

As an example of how to use date functions in Excel 2007, we'll contruct as simple spreadsheets for an order form. We'll enter the date an order was taken, the date the order was sent, and how long it took to be processed. So, to make a start, create the spreadsheet below :

 

Click inside cell A2, and we'll enter a date. To enter a date, Click on the Formulas menu at the top of Excel. Then locate the Function Library panel. From the Function Library panel, click on Date & Time:

 

As you can see, there's quite a lot of Date and Time functions! Click on Date from the menu, and you'll get the following dialogue box:

 

You're now being asked enter a full date.

•    In the Year box, enter 2006

•    In the Month box, enter the number 4

•    In the Day box, enter the number 15

•    Click the OK button

•    Excel 2007 will enter the Date in your selected cell, A2 for us

 

Notice the DATE Function in the Formula bar:

=DATE(2006, 4, 15)

Between the round brackets of DATE, the Year comes first, then the Month, then the Day.

If you want to format your date as say Monday 15th of April, then you need to click on Home from the menu bar at the top of Excel. Locate the Number panel, and you'll see Date already displayed:

 

Click the down arrow to see more options:

 

Click the Long Date item. Or click on More at the bottom to see some more Date formats to choose from. Your spreadsheet will then look like this:

 

In cell B2, under your Date Order Sent heading, enter another Date Function. This time, have the date read May 3, 2006:

 

In cell C2, under Time Taken, we'll work out how many days the order took to be sent out.

The Days360 Function in Excel 2007

When you want to work out how many days there are between two dates, the function to use is Days360( ). We want to work out how many days there are between the 15th of April 2006 and the 3rd of May 2006. So click inside cell C2 and do the following:

Click on the Formulas menu at the top of Excel. Then locate the Function Library panel. From the Function Library panel, click on Date & Time. From the menu, click on Days360(). You should see the Function Arguments dialogue box appear again. This time, it will look like this:

 

The Days360 function needs a start date and an end date. You can enter your cell references here. So in the

Start_date box, enter A2. In the End_Date box, enter B2. If you enter the word True in the Method box, Excel 2007 will calculate using the European date system. Click OK, to return to your spreadsheet and you might see this:

 

(If your C2 cell has a lot of #### symbols in it, it means that your column is not wide enough. Widen the C column and they'll go away!)

The answer we got was January 18th 1900! The reason for such a bizarre answer is that we've formatted the C2 cell as a date. But the answer to the Days360 function is not a date - it's a number. If you have the same strange answer, then format your C2 as a number. Your spreadsheet will then look like ours below:

 

So the difference between the two dates is 18 days.

Entering dates can be fairly straightforward, like cells A2 and B2. But performing calculations with dates can be slightly more complex. To get you some more practise, here's an exercise.

Exercise

Use a Days360 function to work out how many days are left before your next birthday.

Instead of typing out the current date in say cell A2, you can use this inbuilt function:

=Now( )

The Now function doesn't need anything between the round brackets. Once you have today's date, you can enter your birthday in say cell B2.

Use Of The Count Functions in Excel 2007

COUNT is one of the first functions which the average a target=’_blank’Excel/a user will encounter. It can be accessed from the drop-down menu next to the AutoSum tool. However, many users remain unaware that this modest function has a fine pedigree and is fairly powerful. In this article, we shall examine some of the hidden power of the COUNT function.brbrThe COUNT function is found in the statistical category. There are five variations; COUNT, COUNTA, COUNTBLANK, COUNTIF and COUNTIFS. We will examine COUNTIF and COUNTIFS in another article. In this article, we will look at COUNT, COUNTA and COUNTBLANK.brbrThe COUNT function returns the number of cells within a given range or series of ranges that contain numbers. COUNTA simply counts the cells which aren’t blank. COUNTBLANK does the reverse: it counts the cells in a range which are black.brbrFor the purposes of illustration, let’s say that we have a spreadsheet containing the scores achieved by students in a variety of subjects. For each student, we can input one of three entries under each subject: firstly, a number representing the score achieved; secondly, the letter X indicating a subject the student was scheduled to take but missed; and, thirdly, the cell can be left blank, indicating a subject that they were not scheduled to take.brbrIf we want to count the number of exams each student took, we would use COUNT function. If we want to count the number of exams they were scheduled to take we would use COUNTA. Finally, to count the number of exams they were not scheduled to take, we would use COUNTBLANK.brbrTo create the formula for calculating the number of exams taken, we would click in the appropriate call and click the Insert Function button on the left of the formula bar. Next, we highlight the statistical category and scroll down to COUNT. As always Excel reminds us of what this function will do: it counts the number of cells in a range that contain numbers.brbrWhen we click OK, the Function Wizard appears and we are prompted to specify the parameters required by this function. In almost all circumstances, these parameters will consist of cell references. We can simply drag across the cells that we want to count and Excel will create the reference for us. We can then click OK and Excel Will return the correct value.brbrNext, we would repeat the same procedure to create a formula containing the COUNTA function and another for containing COUNTBLANK. Finally, we would copy the formula down by highlighting the cells containing the original formulas and dragging the AutoFill handle down.brbrThe author is a trainer and developer with , an independent computer training company offering a target=’_blank’Excel training courses/a in London and throughout the UK.brbrP.S. After a hard working day you deserve a nice rest. If you like video games – get PS3 at the best price. Visit the a target=’_blank’cheap PlayStation 3/a for frugal tips.

Use the functions wizard

Calculating with Functions

Basic worksheets in Excel often require you to use formulas and functions, which are calculations based on designated values, cell references, and commands. Functions are pre-written commands provided by Excel, while formulas are written entirely by the user. While both methods are useful, functions often save time and energy when working with complex but common tasks (such as finding the sum or average of a group of numbers) by allowing you to customize a pre-created calculation instead of typing it yourself.

HINT: For more information on formulas, refer to Performing Calculations with Formulas (| Mac).

•    Parts of a Function

•    Inserting Functions

•    Inserting Functions with the Point and Click Method

 

Parts of a Function

Functions have two basic parts which you should be aware of:

•    An equation, which is provided by Excel when you select the desired function

•    Values or cell references to be used in the equation, which you will provide

Functions that are inserted using the Insert Function (Windows) / Formula Builder () dialog or the Point and Clickmethod provide empty equations, you must provide the values which will be used in the calculation. Depending on the calculation, you may choose to use several types of operands.

NOTE: While typing cell references, keep in mind that the calculations will be done using the values present in the particular cells entered, not with the cell references themselves.

Operand

Example

Value

Cell

Reference

A1

Calculates the function using the value(s) present in a specified cell. References can be absolute.

Cell Range

A1:A3

Calculates the function using the values present in all cells specified. References can be absolute.

 

Named Cell Range

Quiz Scores

Calculates the function using a specific group of cells that you have previously named. If the function is copiedor moved, it will still use the values present in the specified range.

 

Value

5

Calculates the function using a specific value provided by you.

         

 Inserting Functions

There are multiple ways to create a function. You can insert functions manually (by typing them), or you can use the Insert Function dialog box in Windows and the Formula Builder dialog box in Macintosh. The dialog box option eliminates the possibility of a typing error, so it is the recommended method.

Inserting Functions: Dialog Box Option

Thedialog box option makes it easy to determine what functions are available, which function you should be using, and what you need to include in the function. It displays a listing of all functions or categories of functions available with Excel. As you select a function, a sample of the function appears at the bottom of the dialog box. As you make your selection, thedialog box will request certain types of information; you will simply need to select the cells where that information is located.

Windows:

1.  Select the cell where the function should be added

2.  From the Ribbon, select the Formulas command tab

3.  In the Function Library group, click FUNCTION WIZARD The Insert Function dialog box appears.

 

4.  From the Or select a category pull-down list, select the appropriate function category

OR

Select All

5.  From the Select a function scroll box, select the desired function

HINT: A description of the selected function appears beneath the Select a function scroll box.

6.  Click OK

The Function Arguments dialog box appears.

NOTES:

The appearance and options available in the Function Arguments dialog box will differ depending on which function has been chosen.

A function's arguments are the value(s) that the function is being performed upon.

 

7.  In the text boxes, type the datato be used in the function

OR To select cell ranges

a.  Click COLLAPSE DIALOG

b.  Click and drag the mouse to select the desired cells

c.  Click RESTORE DIALOG

8.  Click OK

The results of the function appear in the selected cell.

Macintosh:

1.  Select the cell where the function should be added

2.  From the Insert menu, select Function

The Formula Builder dialog box appears.

 

3.  In the formula scroll-box, double-click the desired function

HINT: In the Description section, a description of the selected function appears.

NOTES:

The Arguments section of the Formula Builder expands.

The appearance and options available in the Arguments section will differ depending on which function has been chosen.

A function's arguments are the value(s) that the function is being performed upon.

 

4.  Complete the text boxes.

HINT: Type the datato be used in the function or click and drag the mouse to select the desired cells

5.  Press [return]

Inserting Functions: Ribbon Option

Windows only:

Excel provides a multitude of functions for your use. While this ensures that functions exist for most of your needs, it can also make it very difficult to find a particular function. To make functions easier to find, they are divided into categories (e.g., math and trig functions, date and time functions, logic functions, etc.). If you are looking for a function that belongs in a particular category, you can access the Function Arguments dialog box from that category.

1.  Select the cell where the function should be added

2.  From the Ribbon, select the Formulas command tab

3.  In the Function Library group, click the correct category » select the desired function The Function Arguments dialog box appears.

NOTES:

The appearance and options available in the Function Arguments dialog box will differ depending on which function has been chosen.

A function's arguments are the value(s) that the function is being performed upon.

 

4.  In the text boxes, type the datato be used in the function

OR To select cell ranges

a.  Click COLLAPSE DIALOG

b.  Click and drag the mouse to select the desired cells

c.  Click RESTORE DIALOG

5.  Click OK

The results of the function appear in the selected cell.

About the Function Arguments Dialog Box

The Function Arguments dialog box helps you to create functions. As you type information about the function, the Function Arguments dialog box displays the name of the function, the function arguments (i.e., the valuesthat the function is being performed upon), a description of the function and its logic, and the result of the function.

Once you have entered a function, you can further edit it using the Function Arguments dialog box.

To access the Function Arguments dialog box:

Windows only:

1.  Select a cell containing a function

2.  On the Formula bar, click FUNCTION WIZARD The Function Arguments dialog box appears.

 Inserting Functions with the Point and Click Method

Functions based on cell references can be created by clicking the cells rather than typing the cell entries. This "point and click" method can help reduce the chance of error in the functions and may be easier for some users.

The key to the point and click method is to click the cells to be included and type the operators where appropriate.

NOTE: All functions that can be accessed from the Insert Function (Windows) / Formula Builder

() dialog box can be typed with a text-based command. If you choose to type your function into a cell, however, be sure that you know precisely how to enter information for the function, especially if you are working with a complex function.

The following examples provide step-by-step instructions for a simple addition of two cells and for adding a range of cells.

Adding Cells Together

1.  Select the cell where the results should be displayed

2.  To start the function, press [=]

3.  Click the first cell to be added

4.  Press [+]

5.  Click the next cell to be added

6.  Repeat steps 4–5 as necessary

7.  Windows: Press [Enter]

Macintosh: Press [return]

The sum appears in the selected cell.

Adding a Range of Cells with the SUM Function

1.  Select the cell where the results should be displayed

2.  To start the function, press [=]

3.  Type SUM(

4.  Click and drag the mouse over the range of cells to be added

OR

a.  Click the first cell in the range to be added

b.  Press [:]

c.  Click the last cell in the range to be added

5.  Type )

6.  Windows: Press [Enter]

Macintosh: Press [return]

The sum appears in the selected cell.

Use Absolute cell references in formulas

Absolute Cell References

An important difference in Excel spreadsheets is between absolute cell references and relative cell references. To see what this is all about, we'll create a simple spreadsheet. This will illustrate relative cell references, which is what we've been using so far.

So open up Excel 2007 and enter the same values as in the image below:

 

In cell B2, you need the following formula:

= A1 + A2

What do you think would happen if we copied an pasted the formula from B2 to cell B3? Let's see:

•    Click inside cell B2 to highlight it

•    Click on cell B2 with your right mouse button, and select Copy from the menu that appears

•    Now click into cell B3

•    Again, right click the cell to get the menu. But this time click Paste

•    Your spreadsheet should now look like ours:

 

Cell now says 25! We were trying to work out what 20 + 25 was, and have the wrong answer. So why did Excel put 25 into cell B3 and not 45?

With cell B3 still highlighted, look at the formula bar at the top of Excel. You should see this formula:

= A2 + A3

Click into B2, however, and the formula is this:

= A1 + A2

The problem is due to cell referencing. When you clicked Copy from the menu, Excel didn't only copy the formula. It took at look at where the cells were in the formula, relative to the B2 cell, and copied this as well. From B2, the first cell reference (A1) is up one row, and left 1 column (the red arrow below):

 

The second cell reference (A2) is one column to the left of cell B2:

 

When you clicked into cell B3 and selected Paste from the menu, Excel was not only pasting the formula, it was pasting this "up 1, left 1". Take a look at the two images below. We're now starting at cell B3. Have a look at where the two red arrows are pointing now:

The first cell reference:

 

The second cell reference:

 

So the first red arrow is pointing to cell A2, and the second red arrow is point to cell A3. This is what was copied. Excel then took the formula to mean this:

= A2 + A3

But it should have been this:

= A1 + A2

If you want the correct answer in cell B3, you have stop Excel from using this Relative Cell Referencing that it's currently doing. What you need is Absolute Cell Referencing.

Absolute cell referencing involves nothing more than placing a dollar symbol ( $ ) before each letter and number.

Click inside of cell B2 on your spreadsheet, and change the formula to this:

= $A$1 + $A$2

Now copy and paste it over to cell B3 again. You should have the correct answer, this time:

 

Excel will use Absolute Formula in its own calculation, so it's worth getting used to them. But to recap:

•    If you need to copy and paste formulas, use Absolute cell references

•    Absolute referencing means typing a dollar symbol before the numbers and letters of each cell reference (You can mix absolute and relative cell references, though).

Use Excel as a database

Creating Tables and Lists

 

Excel 2003 Database Tutorial

© Ted French

Tables of data

The basic format for storing data in an Excel database is a table. In a table, data is entered in rows. Each row is known as a record.

Once a table has been created, Excel's data tools can be used to search, sort, and filter records in the database to find specific information.

Although there are a number of ways you can use these data tools in Excel, the easiest way of doing so is to create what's known as a list from the datain a table.

To follow this tutorial:

• enter the data as it is shown in the image above.

Tip - To enter the Student ID's quickly:

Note: For help on these steps, refer to the image above.

1.  Type the first two ID's - ST348-245 and ST348-246 into cells A5 and A6 respectively.

2.  Drag selectthe two ID's to highlight them.

3.  Click on the and drag it down to cell A13.

4.  The rest of the Student ID's should be entered into cells A6 to A13 correctl

Entering Data Correctly

 

Enter the data correctly for a list

© Ted French

Entering Data Correctly

Note: For help on these steps, refer to the image above.

The first step in creating an Excel database is to enter the data. When doing so, it is important to ensure that it is entered correctly.

Data errors, caused by incorrect data entry, are the source of many problems related to data management. If the data is entered correctly in the beginning, the program is more likely to give you back the results you want.

Rows are Records

 

A data record in an Excel database

© Ted French

Rows are Records

Note: For help on these steps, refer to the image above.

As mentioned, rows of data, in a database are known as records. When entering records keep these guidelines in mind:

•    Leave no blank rows in the table being created, not even between the headings and the first row of data.

•    A record can contain data about only one specific item.

•    A record must also contain ALL the data in the database about that item. There can't be information about an item in more than one row.

Columns are Fields

 

Field names in an Excel table

© Ted French

Columns are Fields

Note: For help on these steps, refer to the image above.

While rows in an Excel database are referred to as records, the columns are known as fields. Each column needs a heading to identify the data it contains. These headings are called field names.

•    Field names are used to ensure that the data for each record is entered in the same sequence.



•    Make sure that all the data in a column is entered using the same format. If you start entering numbers as digits (such as 10 or 20) keep it up. Don't change part way through and begin entering numbers as words (such as ten or twenty). Be consistent.

•    Leave no blank columns in the table.

Creating the List

 

Using the Create List dialog box in Excel

© Ted French

Create the list

Note: For help on these steps, refer to the image above.

Once the data has been entered into the table, it can be converted to a list. To do so:

1.  Drag selectcells A3 to E13 in the spreadsheet.

2.  Click on Data > List > Create List from the menu to open the Create List dialog box.

3.  While the dialog box is open, cellsA3 to E13 on the worksheet should be surrounded by the marching ants.

4.  If the marching ants surround the correct rangeof cells, click Ok in the Create List dialog box.

5.  If the marching ants do not surround the correct range of cells, drag select the correct range in the spreadsheet and then click Ok in the Create List dialog box.

6.  The table should be surrounded by a dark border and have drop down arrows added beside each field name.

Using the Database Tools

 

Creating lists in Excel

© Ted French

Using the Database Tools

Note: For help on these steps, refer to the image above.

Once you have created the database, you can use the tools located under the drop down arrows beside each field name to sort or filter your data.

Sorting Data

1.  Click on the drop down arrow next to the Last Name field name.

2.  Click on the Sort Ascending option to sort the database alphabetically from A to Z.

3.  Once sorted, Graham J. should be the first record in the table and Wilson . R should be the last.

Filtering Data

1.  Click on the drop down arrow next to the Program field name.

2.  Click on the Business option to filter out any students not in the business program.

3.  Click OK.

4.  Only two students - G. Thompson and F. Smith should be visible since they are the only two enrolled in the business program.

5.  To show all records, click on the drop down arrow next to the Program field name.

6.  Click on the All option.

Expanding the Database

 

Excel 2003 Database Tutorial

© Ted French

Expanding the database

Note: For help on these steps, refer to the image above.

Expanding the database

Note: For help on these steps, refer to the image above.

To add additional records to your database:

•    A blank row containing an asterisk (cells A14 to E14) is added to the bottom of the database when it is created.

•    Additional data can be added to the database using this blank row.

•    Add the following data:

Cell - Data

A14 - ST348-255 B14 - Christopher C14 - A.

D14 - 22

E14 - Science

ormatting the Database

 

Excel 2003 Database Tutorial

© Ted French Formatting the database:

Note: For help on these steps, refer to the image above. This step involves using icons located on the Formatting toolbar, which is normally located at the top of the Excel 2003 screen. If it is not present, this article, Finding ExcelToolbarscan help you locate it.

1.  Drag selectcells A1 to E1 in the spreadsheet.

2.  Click on the Merge and Center icon on the Formatting Toolbar to center the title.

3.  With cells A1 to E1 still selected, click on the Fill Color icon on the Formatting Toolbar (looks like a paint can) to open the background color drop down list.

4.  Choose Sea Green from the list to change the background color of cells A1 - E1 to dark green.

5.  Click on the Font Color icon on the Formatting Toolbar (it is a large letter " A ") to open the font color drop down list.

6.  Choose White from the list to change the color of the text in cells A1 - E1 to white.

7.  Drag select A2 - E2 on the spreadsheet.

8.  Click on the Fill Color icon on the Formatting Toolbar to open the background color drop down list.

9.  Choose Light Green from the list to change the background color of cells A2 - E2 to light green.

10.  Drag select cells A3 - E14 on the spreadsheet.

11.  Choose Format > AutoFormat from the menus to open the AutoFormat dialog box.

12.  Choose List 2 from the list of options to format cells A3 - E14.

13.  Drag select cells A3 - E14 on the spreadsheet.

14.  Click on the Center option icon on the Formatting Toolbar to center align the text in cells A3 to E14.

15.  At this point, if you have followed all the steps of this tutorial correctly, your spreadsheet should resemble the spreadsheet pictured in Step 1 of this tutorial.

Use filters and subtotals in a data base

See above

Create charts

Create a Pie Chart / Pie Graph in Excel 2007

 

Excel 2007 Pie Chart

© Ted French

Excel 2007 Pie Chart / Pie Graph Tutorial

Related article: Excel 2003 Pie Chart Tutorial.

One change to creating a pie chart or pie graph in Excel 2007 is that the is no longer available.

It has been replaced by chart options listed under the Insert ribbon.

This tutorial walks you through creating and formatting a pie chart using many of the common features available in Excel 2007.

Entering the Chart Data

 

Excel 2007 Pie Chart

© Ted French

Enter the Chart Data

The first step in creating a pie chart is to enter the data into the .

When entering the data, keep these rules in mind:

1.  Don't leave blank rowsor columnswhen entering your data.

2.  Enter your in columns.

o Note: When laying out your spreadsheet, list the names describing the data in one column and, to the right of that, the data itself.

3.  A pie chart is a percentage chart, so only one series of data will be used in the chart. If there is more than one column of data, try to list the data to be used in the chart next to the column containing the list of names in order to simplify creating the chart.

Select the Pie Chart Data

 

Excel 2007 Pie Chart

© Ted French

Two Options for Selecting the Chart Data

Using the mouse

1.   Drag select with the mouse button to highlight the cells containing the data to be included in the pie chart.

Using the keyboard

1.  Click on the top left of the pie charts's data.

2.  Hold down the SHIFT key on the keyboard.

3.  Use the arrow keys on the keyboard to select the data to be included in the pie chart.

Note: Be sure to select any column and row titles that you want included in the chart.

For this tutorial

                           1. Highlight the block of cells from A2 to B5, which includes the row headings

Selecting a Pie Chart Type

 

Excel 2007 Pie Chart

© Ted French

Selecting a Pie Chart Type

Note: For help with these instructions, see the image example above.

1.  Click on the Inserttab.

2.  Click on a chart category to open the drop down list of available chart types.

(Hovering your mouse pointer over a chart type will bring up a description of the chart type).

3.  Click on a chart type to select it.

For this tutorial

1.  Choose Insert > Pie > Pie in 3-d.

2.  A basic pie chart is created and placed on your worksheet. The following pages cover formatting this chart to match the pie chart shown in Step 1of this tutorial.

he Basic Pie Chart

 

Excel 2007 Pie Chart

© Ted French

Formatting options for the pie chart

Use the following links to format the basic pie chart shown above.

•    Choosing a style for the pie chart

•    Exploding a piece of the pie chart

•    Adding a title to the chart

•    Remove the legend

•    Adding data labels to the chart

•    Adding a drop shadow to the title

•    Coloring the chart background

•    Beveling the chart edge

Formatting the Pie Chart - 1

 

Excel 2007 Pie Chart

© Ted French

Formatting the Pie Chart - 1

Note: For help with these instructions, see the image example above.

When you click on a chart, three tabs - the Design, Layout, and Format tabs are added to the ribbonunder the title of Chart Tools.

Choosing a style for the Pie chart

1.  Click on the pie chart.

2.  Click on the Design tab.

3.  Choose Style 6 of the Chart Styles

. Return to the format options list

Exploding a piece of the pie chart

Note: Exploding a piece of pie out from the rest of the chart is done to emphasize that section.

1.  Click once on the pie chart to highlight it.

2.  Click once on the Peanut Butter slice of the pie chart to select just that piece of the chart.

3.  Click and drag the Peanut Butter slice out from the pie chart to explode it.

Formatting the Pie Chart - 2

 

Excel 2007 Pie Chart

© Ted French

Formatting the Pie Chart - 2

Note: For help with these instructions, see the image example above.

Adding a title to the chart

1.  Click on the Layout tab.

2.  Click on Chart Title under the Labels section.

3.  Select the third option - Above Chart.

4.  Type in the title "The Cookie Shop 2003 Revenue from Sales"

. Return to the format options list

Remove the legend

                           1.   Choose Layout > Legend > None.

Return to the format options list

Adding data labels to the chart

1.  Choose Layout > Data Labels > Best Fit to add data labels.

2.  Choose Layout > Data Labels > More Data Label Options to bring up the Format Data Labels dialog box.

3.  Remove the check marks from Value and Show Leader Lines under Label Options in the right hand window.

4.  Check off Category Name and Percentage under Label Options in the right hand window.

Formatting the Pie Chart - 3

 

Excel 2007 Pie Chart

© Ted French

Formatting the Pie Chart - 3

Note: For help with these instructions, see the image example above.

Adding a drop shadow to the title

1.    tthe chart title.

2.    Click on the Formattab.

3.    Choose Text Effects > Shadow > Offset Right. Return to the format options list

Coloring the chart background

1.    Click on the chart background.

2.    Click on the Formattab.

3.    Choose Shape Fill > Gradient > Theme Colors > Purple, Accent 4, Lighter 80%.

4.    Click on the Shape Fill > Gradient > From Center option. Return to the format options list

Beveling the chart edge

1.  Click on the chart background.

2.  Click on the Formattab.

3.  Choose Shape Effects > Bevel > Divot.

Create drawing objects

Insert Drawing Objects into your Excel 2007 Spreadsheets

A drawing can liven up a dull spreadsheet. Some good line art, or even simple shapes, can help illustrate your data. In this lesson, you'll see how to add simple shapes, and textboxes to your spreadsheet. First, look at the spreadsheet below. Unless you know about Cosines, Adjacent angles, and Hypotenuse, the data below will be a bit bewildering:

 

However, add a few shapes, along with some colour, and it becomes clearer what the data is for (the Cosine in the image below has been formatted to 2 decimal places):

 

We'll now show you how to produce a spreadsheet like the one above. Don't worry if you haven't a clue about

Cosines - it's not important for this lesson. (We'll show you the formula, though.)

How to Draw a Shape on an Excel 2007 Spreadsheet

To insert a shape on your spreadsheet, do the following.

•    From the Excel 2007 menu bar, click on Insert

•    Locate the Shapes panel:

 

On the Shapes panel, click the drop down arrow to see all the available shapes:

 

•    Under Basic Shapes, select the Right Triangle

•    Hold down your left mouse button on your spreadsheet, and drag to create your shape. Let go when you have a decent sized triangle. You'll see something like this:

 

The green circle allows you to rotate the shape. The other circles (and squares) are sizing handles. Hold your mouse down over one of these and drag to resize your shape, if it's not the size you want it.

But we'd like the triangle pointing the other way. So hold your mouse down on the green circle, and drag to rotate your triangle:

 

You should see an outline, like the one above. Let go of your left mouse button when it is in position:

 

As you can see, the green circle is now on the left hand side.

If you look on the Excel 2007 Ribbon at the top, you'll notice that it has changed. You'll see all the various options for shapes. Locate Shape Fill on the Shape Styles panel, and click to see the Fill options:

 

Select a colour for your triangle. You'll also want to select a Shape Outline, underneath Shape Fill. Select the same colour as your Fill, and your triangle will look something like this one:

 

Add a Text Box to an Excel 2007 Spreadsheet

To get the letter B in the triangle, we'll add a text box. So, on the Insert Shapes panel again, you'll notice a Text Box option. Click on this to select it:

 

Now move back to your spreadsheet, hold down your left mouse button, and drag out a Text Box. Let go of the left mouse button and you'll have something like this:

 

With the cursor inside of the Text Box, simply type the letter B. Because it's text, you can highlight your letter and format it. In the image below, we've increased the font size:

 

We now need to drag our Text Box onto the shape. Move your mouse over the Text Box until the cursor changes shape to four arrowheads (this can be tricky):

 

Once your cursor changes shape, hold down the left mouse button and drag your Text Box on to the triangle:

 

With the Text Box selected, use the arrow keys on your keyboard to nudge it in to position. Fill the Text Box in the same way as you did for the triangle. It will then look like this:

 

If you need to move your triangle and Text Box, you can select them both at the same time, and drag them as one. Click on your Triangle to select it. Now hold down the CTRL key on your keyboard. With the CTRL key held down, click on your Text Box. Both will now be selected:

 

With both the triangle and the Text Box selected, hold your mouse over the selected shapes. When your cursor changes to the four arrowheads, hold down the left button and drag your shapes to a new position:

 

You can finish off the formatting in the normal way. In the image below, we selected all the cells surrounding the shape, and added a background colour from the Home menu, Font panel.

 

If you look again at the finished version, you'll see the rest of the colours we chose. These are just filled cells from the Home > Font panel:

 

The Text in the cells is just entered in the normal way. The formula for the Cosine in cell G22 of our spreadsheet has this syntax:

=DEGREES(COS(Adjacent_Cell_Reference / Hypotenuse_ Cell_Reference))

An example of how to use is it this:

=DEGREES(COS(F18 / F10))

When the user types in a value for the Hypotenuse or the Adjacent, the Cosine number will change.

But you can add any shapes you want to liven up your spreadsheet. It doesn't have to look plain, white and dull!

And that completes this beginners course on Excel 2007. It may have a little taxing along the way, but if you've finished all of it, you should have quite a few new skills to show off!

Use hyperlinks

How to Insert Hyperlinks in Excel 2007

You can place Hyperlinks in the cells on your spreadsheet. To quickly go to a different worksheet or workbook, you would simply click the link. We'll see how to do that now.

•    Click inside of cell A1 of a new spreadsheet

•    From the Excel 2007 menu bar, click the Insert menu

•    From the Insert menu, locate the Links panel

•    Click on Hyperlink:

 

When you click the Hyperlink item, you'll see the following dialogue box appear:

 

We're going to create a link to another worksheet in this same spreadsheet. So, under Link to on the left, click on "Place in This Document".

When you click Place in This Document, the dialogue box changes to this:

 

We'll try linking to Sheet3 on our spreadsheet. When the link is clicked on Sheet1, we want to jump to a specific cell on Sheet3.

•    Under "Or select a place in this document, click on Sheet3

•    Type some text in the Text to display box at the top. This is the text of your hyperlink, as it will display in the cell

•    Click the Screen Tip button at the top, and type some text for when the mouse is over the link

Your dialogue box will then look something like this one:

 

Click OK when you're done, and you'll see cell A1 on your spreadsheet change:

 

Hold your mouse over the link and you should see your Screen Tip:

 

Try to click on your link, and you might find that nothing happens! To use the hyperlink, you have to click the link and hold your mouse down for a second or so. Let go of the left mouse button and you should jump to Sheet 3.

If you want to open up an existing spreadsheet, instead of jumping to a location in the current one, click the Hyperlink item on the Links panel to bring up the dialogue box again.

 

•    Under Link to on the left, select Existing File or Web Page

•    Navigate to the location of your spreadsheet from the Look in area

•    Select the spreadsheet to open

•    Type some text, and a Screen tip

•    Then click OK

When you click your new link, the spreadsheet file you selected will open.

But we'll leave this brief introduction to the subject of Web Integration in Excel 2007. There's a whole lot more you can do in this area: Upload your spreadsheet data to the web, instead of downloading like we did; save your spreadsheet as a web page; create a spreadsheet that others can interact with, email your spreadsheets, and a whole lot more besides. In fact, a whole book could be written on the subject!

Excel Advanced

Use named ranges in formulas

Named Ranges in Excel 2007

A Named Range is way to describe your formulas. So you don't have to have this in a cell:

= SUM(B2:B4)

You can replace the cell references between the round brackets. You replace them with a descriptive name, all of your own. So you could have this, instead:

= SUM(Monthly_Totals)

Behind the Monthly_Totals, though, Excel is hiding the cell references. We'll see how it works, now.

Open up Excel 2007, and create the spreadsheet below:

 

The formula is in cell B5, and just adds up the monthly totals in the B column.

Define a Name

Setting up a Named Range is a two-step process. You first Define the Name, and then you Apply it. To Define your name, do this (make sure you have the formula in cell B5):

•    Highlight the cells B2 to B4 (NOT B5), then click the Formulas menu

•    Locate the Named Cells panel

•    Click Name a Range

 

From the Name a Range menu, click Name a Range:

 

You'll then get the following dialogue box:

 

Click OK on the New Name dialogue box. Notice that the Name is our heading of Monthly_Totals.

When you click OK, you'll be returned to your spreadsheet. You won't see anything changed. But what you have done is to Define a Name. You can now Apply it.

Apply a Name

To apply your new Name, click into cell B5 where your formula is, and do this:

•    On the Named Cells panel, Click Name a Range

•    From the menu, select Apply Names

•    From the Apply Names dialogue box, select the Name you want and click OK:

 

When you click OK, Excel should remove all those cell references between the round brackets, and replace them with the Name you defined:

 

In the image above, cell B5 now says:

=SUM(Monthly_Totals)

The cell references have been hidden. But Excel still knows about them - it's you that can't see them!

Exercise

Study the spreadsheet below, now that we have added another Named Range to cell C5:

 

Using the same techniques just outlined, create the same Named Range as in our image above. Again, the formula we've used is just a SUM formula:

= SUM(C2:C4)

You need to start with this, before you Define the Name and Apply it.

Using Named Ranges in Formulas

We'll now use two Named Ranges to deduct the tax from our monthly totals.

So, to define two new Names, do the following:

1.  Click inside cell B5 to highlight it

2.  From the Formulas menu bar, locate the Named Cells panel, and click Name a Range > Name a Range

3.  From the New Name dialogue box, click in to the Name textbox at the top and enter Monthly_Result (with the underscore character)

4.  Click OK

5.  Click inside cell C5 and do the same as step 2 above. This time, however, enter Tax_Result as the Name

You should now have two new Names defined. We'll now Apply these new names. First, add a new label to your spreadsheet:

 

Click in to cell B7, next to your new label, and enter the following formula:

= B5 - C5

With the formula in place, we can Apply the two new Names we've just defined:

•    From the Formulas menu bar, locate the Named Cells panel, and click Name a Range > Apply Names

•    The Apply Names dialogue box appears

•    Click Monthly_Result to select it

•    Click on Tax_Result to select it:

 

•    Click the OK button

•    Excel will replace your cell references with the two Names you Defined

•    Your spreadsheet should look like ours:

 

If you look at the formula bar, you'll see the two Named Ranges. The formula is easier to read like this. But it's not terribly easy to set up! They can be quite useful, though.

Use lookup functions

If you use Excel much at your job, sooner or later, you’re bound to need to look up values in a table. One of the most useful functions in Excel, called vlookup, does exactly that. The “V” in vlookup stands for “vertical” and “lookup” is pretty self explanatory. This function allows you to look up values in a table that are listed in column format (how most tables are laid out), given another value (let’s call this the “key”). Excel also has a sister function called hlookup (h = horizontal) that can be used to look up values in rows.

Sadly, as most companies seem to rely on Excel as a poor-man’s database of sorts (a totally unscalable solution and prone to errors with every revision, but don’t get me started), once you know vlookup, it’s likely to become one of your most often used Excel functions.

So, let’s get started with a very simple example of what vlookup is all about. Suppose you had the following table:

 

Given a list of names in another part of the table (in this case, column H), you want to figure out what kind of animal it is:

 

Vlookup’s format looks like the following:

=vlookup(lookup value, table where values reside, column # where values are located, false) Let’s look at each of these parts a bit closer.

The first thing that goes into the vlookup function is the thing you know (or are given) and that will be used to lookup other values. In this case, you have the names of the animals, so these are the things we know. In our example, they reside in column H, from cells H2 through H5. If we wanted to put the type of animal next to the name of the animal in column I (so I2 would correspond to the name of the animal in H2), we would insert the vlookup function there:

 

and put H2 as the first thing in our vlookup function:

 

Next, we need to know the location of the table where our values reside. These happen to be from cells A1 through B5 in this example, which we would highlight with our mouse to insert into the vlookup function. It’s very important that you include all the cells in the table.

Highlight the table with your mouse:

 

At the same time, the vlookup function automatically puts in the cells you’ve highlighted:

 

Next, we need the column number where the values are located. Always start with the first column (column A in this case) as #1 and count out to the right. In this example, the type of animal listed is in column 2, so that’s what we would need to insert in the vlookup function. Note that to use vlookup, your keys always have to be to the left of your values. (We’ll cover more of this in part II of the tutorial at a later date.)

 

Finally, the last attribute that vlookup takes is either “true” or “false”. I happen to always use “false”, and what this does is force vlookup to return the first exact value it finds. If that value isn’t found, then vlookup conks out and returns “#N/A”. Though we won’t use it in this example, if you select “true”, then rather than always looking for the exact value, vlookup will return the exact value if it exists, or the closest one to it that doesn’t exceed the key. (If you use “true”, you will need to sort your data in ascending order before using vlookup.)

 

Still with me? Again, this is what we would actually put in cells I2 if the names of the animals we have are located in cells H2 through H5:

=vlookup(H2, A1:B5, 2, false)

Once we close off the parenthesis and hit “Enter”, vlookup automatically calculates:

 

And so on. We would continue down each cell in column I that we needed. One thing to note is to make sure that the location of your keys and values is always selected correctly. Oftentimes, as you copy-and-paste formulas all around Excel, the location of the data will also move around relative to the cell. The easiest way to prevent this is to “lock” the range of the location; in this case, we would do so by using “$A$1:$B$1? instead of “A1:B5?. This way, as we move down column I, say, to cell I2, A1:B5 doesn’t become A2:B6 but stays with the original range of data. This way, we can just copy what’s in cell I2 down the rest of the cells (from I3 through I5):

 

Finally, here’s our result, after making the “$” changes and copying and pasting the formula down the rest of the column:

 

This has been a really simple example of vlookup, and I’ll cover a bit more in part II with another example, still simple, but with slightly more data.

Although in practice, vlookup is usually used between Excel sheets and workbooks, once you understand this example (which has been done within a single sheet), using vlookup outside the same sheet shouldn’t be much harder. Look for part II soon!

Searches for a value in the first column of a table array and returns a value in the same row from another column in the table array.

The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data that you want to find.

Syntax

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup_value  The value to search in the first column of the table  array   (array: Used to build single formulas that  produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.). Lookup_value can be a value or a reference. If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.

Table_array  Two or more columns of data. Use a reference to a range or a range name. The values in the first column of table_array are the values searched by lookup_value. These values can be text, numbers, or logical values. Uppercase and lowercase text are equivalent.

Col_index_num  The column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is:

•    Less than 1, VLOOKUP returns the #VALUE! error value.

•    Greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

Range_lookup  A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:

•    If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

The values in the first column of table_array must be placed in ascending sort order; otherwise,

VLOOKUP may not give the correct value. You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending. For more information, see Default sort orders.

•    If FALSE, VLOOKUP will only find an exact match. In this case, the values in the first column of table_array do not need to be sorted. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.

Remarks

•    When searching text values in the first column of table_array, ensure that the data in the first column of table_array does not have leading spaces, trailing spaces, inconsistent use of straight ( ' or " ) and curly ( ‘ or “) quotation marks, or nonprinting characters. In these cases, VLOOKUP may give an incorrect or unexpected value. For more information on functions that you can use to clean text data, see Text and Data functions.

•    When searching number or date values, ensure that the data in the first column of table_array is not stored as text values. In this case, VLOOKUP may give an incorrect or unexpected value. For more information, see Convert numbers stored as text to numbers.

•    If range_lookup is FALSE and lookup_value is text, then you can use the wildcard characters, question mark (?) and asterisk (*), in lookup_value. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.

Example 1

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1.  Create a blank workbook or worksheet.

2.  Select the example in the Help topic.

 Note   Do not select the row or column headers.

 

Selecting an example from Help

3.  Press CTRL+C.

4.  In the worksheet, select cell A1, and press CTRL+V.

5.  To switch between viewing the results and viewing the formulas that return the results, press

CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

This example searches the Density column of an atmospheric properties table to find corresponding values in the

Viscosity and Temperature columns. (The values are for air at 0 degrees Celsius at sea level, or 1 atmosphere.)

1

2

3

4

5

6

7

8

9

10

0.457 0.525 0.616 0.675 0.746 0.835

0.946

1.09

1.29

A

Density

B

Viscosity

3.55 3.25 2.93 2.75 2.57 2.38 2.17 1.95

1.71

C

Temperature

500

400

300

250

200

150

100

50

0

   

Formula

Description (result)

 
 

=VLOOKUP(1,A2:C10,2)

Using an approximate match, searches for the value 1 in column A, finds the largest value less than or equal to 1 in column A which is 0.946, and then returns the value from column B in the same row.

(2.17)

 
 

=VLOOKUP(1,A2:C10,3,TRUE)

Using an approximate match, searches for the value 1 in column A, finds the largest value less than or equal to 1 in

 
 
 

column A, which is 0.946, and then returns the value from column C in the same row.

(100)

=VLOOKUP(.7,A2:C10,3,FALSE)

Using an exact match, searches for the value .7 in column A. Because

there is no exact match in column A, an error is returned. (#N/A)

=VLOOKUP(0.1,A2:C10,2,TRUE)

Using an approximate match, searches for the value 0.1 in column A. Because 0.1 is less than the smallest value in column A, an error is returned. (#N/A)

=VLOOKUP(2,A2:C10,2,TRUE)

Using an approximate match, searches for the value 2 in column A, finds the largest value less than or equal to 2 in column A, which is 1.29, and then returns the value from column B in the same row.

(1.71)

Example 2

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1.  Create a blank workbook or worksheet.

2.  Select the example in the Help topic.

 Note   Do not select the row or column headers.

 

Selecting an example from Help

3.  Press CTRL+C.

4.  In the worksheet, select cell A1, and press CTRL+V.

5.  To switch between viewing the results and viewing the formulas that return the results, press

CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

This example searches the Item-ID column of a baby products table and matches values in the Cost and Markup columns to calculate prices and test conditions.

1

2

3

4

5

6

A

Item-ID

ST-340

BI-567

DI-328

WI-989

AS-469

B

Item

Stroller

Bib

Diapers

Wipes

Aspirator

C

Cost

$145.67

$3.56

$21.45

$5.12

$2.56

D

Markup

30%

40%

35%

40%

45%

 

Formula

Description (result)

   
 

= VLOOKUP("DI-

328", A2:D6, 3,

FALSE) * (1 +

VLOOKUP("DI-

328", A2:D6, 4,

FALSE))

Calculates the retail price of diapers by adding the markup percentage to the cost. ($28.96)

   
 

=

(VLOOKUP("WI-

989", A2:D6, 3,

Calculates the sale price of

   

 

FALSE) * (1 +

VLOOKUP("WI-

989", A2:D6, 4,

FALSE))) * (1 -

20%)

wipes by subtracting a specified discount from the retail price.

($5.73)

 

 

=

IF(VLOOKUP(A2,

A2:D6, 3, FALSE)

>= 20, "Markup is

" & 100 *

VLOOKUP(A2,

A2:D6, 4, FALSE) &"%", "Cost is under $20.00")

If the cost of an item is greater than or equal to $20.00, displays the string

"Markup is nn%"; otherwise, displays the string "Cost is under $20.00".

(Markup is

30%)

 

 

=

IF(VLOOKUP(A3,

A2:D6, 3, FALSE)

>= 20, "Markup is:

" & 100 *

VLOOKUP(A3,

A2:D6, 4, FALSE)

&"%", "Cost is $"

& VLOOKUP(A3,

A2:D6, 3,

FALSE))

If the cost of an item is greater than or equal to $20.00, displays the string

Markup is nn%"; otherwise, displays the string "Cost is $n.nn".

(Cost is

$3.56)

 

           

Example 3

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1.  Create a blank workbook or worksheet.

2.  Select the example in the Help topic.

 Note   Do not select the row or column headers.

 

Selecting an example from Help

3.  Press CTRL+C.

4.  In the worksheet, select cell A1, and press CTRL+V.

5.  To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

This example searches the ID column of an employee table and matches values in other columns to calculate ages and test for error conditions.

                                                                                              A                                                          B                             C

1                                                 First

                                                                                                              ID                                                   Last name

2                                                 name

3                                                 1 Davolio Nancy

4

                                           5                    2                                                                             Fuller                         Andrew

6

                                           7                    3                                                                             Leverling                       Janet

4                            Peacock          Margaret

5                            Buchanan         Steven

6                            Suyama           Michael

Description

Formula

(result)

For the fiscal year 2004, finds the age of the employee with ID equal to 5. Uses the

YEARFRAC

function to

=INT(YEARFRAC(DATE(2004,6,30),

subtract the birth

VLOOKUP(5,A2:E7,5, FALSE), 1))

date from the fiscal year end date and displays the result as an integer using the INT function.

(49)

                                                                       =IF(ISNA(VLOOKUP(5,A2:E7,2,FALSE))                   If there is an

                                                                       = TRUE, "Employee not found",                                 employee with

an ID of 5, displays the employee's last name; otherwise, displays the message

                                                                       VLOOKUP(5,A2:E7,2,FALSE))                                  "Employee not

found". (Buchanan)

The ISNA function returns a

TRUE value when the VLOOKUP function returns the #NA error value.

If there is an employee with an ID of 15, displays the employee's last name; otherwise, displays the

                                                                       =IF(ISNA(VLOOKUP(15,A3:E8,2,FALSE))                  message

= TRUE, "Employee not found", "Employee not VLOOKUP(15,A3:E8,2,FALSE)) found".

(Employee not found)

The ISNA function returns a

TRUE value when the VLOOKUP function returns the #NA error value.

For the employee with an ID of 4, concatenates the values of

=VLOOKUP(4,A2:E7,3,FALSE) & " " &

three cells into a

VLOOKUP(4,A2:E7,2,FALSE) & " is a " & complete

VLOOKUP(4,A2:E7,4,FALSE) & "." sentence. (Margaret

Peacock is a

Sales

Representative.)

Link multiple workbooks

 

To use Paste Special to create links quickly:

1. Copy a range of cells containing data, and select a cell in another sheet or workbook.

2. Select Home -> Paste arrow (in Clipboard Group) -> Paste Link.

Or

Press Shift+F10 or right-click, and then select Paste Special from the shortcut menu, and click Paste Link.

Import data

CSV stands for Comma Separated Values, and this simple text file format is excellent for moving data between programs that can't read each other's native format. Here, we'll look at how to import CSV files into a Microsoft Excel 2007 spreadsheet.

CSV Files Explained

In Windows, a file with a .CSV extension denotes a Comma Separated Values file. It’s a very simple text file format where the data is separated with commas, hence the name of the file. CSV files are most commonly used for transferring data between programs, such as exporting your Outlook Contacts into a .CSV file so that it can be imported into Excel or Access. Because .CSV files are just text, they can also be transferred between operating systems.



Working With CSV

The .CSV file extension has been around since the earliest days of computing, and it is still supported

by many programs today. If you’ve ever been given the tedious task of transferring data between two different programs, then comma separated values will save you lots of headache. Microsoft Excel is great for importing this data and automated wizards built within the program help expedite the process.

Suppose you wanted to export an address book from one program into Excel. Let’s assume that program lets you export to CSV, as many do. When finished, your data will look something like this in the .CSV file:

Name,Address,City

Bill,124 Here,Biloxi

Christy,243 There,Biloxi

Mike,256 Alpha,Gulfport

Lucy,674 Beta,Gulfport

David,127 Horse,Biloxi

In the above example, you see the first line contains the label for each column, then underneath are five different data entries. Each piece of information is separated by a comma. Also notice the space between the street number and name.

To import this back into Microsoft Excel 2007, click on Data, then click the From Text button in the Get External Data menu. This will bring up the Text Import Wizard.

In the first step, make sure you have Delimited set as the first option. You can also select which row you would like to start from. In this example, I’m doing the whole thing starting with the first row.

In the second step, make sure you check the box next to Comma. This will then show your data in columns at the bottom. If you click the check next to Space, it will divide the address column into two lines, in this example. Sometimes this can be helpful when you are trying to break down data during the import, as some programs tend to lump data together.

The third step simply lets you choose the format of the data, such as numbers, text, or dates. After you’ve done that, just click Finish and the data will be imported into your spreadsheet. Depending on how big the .CSV file is, the import process could be instantaneous or take several minutes. I’ve seen giant database exports pulled into Excel that took a while to load, but they still worked just fine.

Additional Resources

So there you have it. Comma Separated Value values are very easy to work with, which is why they are still around today. They are mainly used for importing and exporting data between other programs, but they still function as a viable file format even though they are not very secure. With all the complications that get piled into every new version of some software out there, it’s good to know that the basics like .CSV files are still available.

Use pivot tables and charts

ivot tables are an Excel feature that you should learn how to use. Instead of analyzing rows upon rows of records, a pivot table can aggregate your data and show a new perspective with few clicks. You can also move columns to rows or vice versa. The problem is people believe creating a pivot table is too difficult to learn. Grab a seat and we’ll walk you through a short tutorial using Excel 2007. (Includes tutorial in PDF format with larger images and example worksheet.) What is an Excel Pivot Table?

You might think of a pivot table as a user created summary table of your original spreadsheet. You create the table by defining which fields to view and how the data should be displayed. Based on your field selections, Excel aggregates and organizes the data so you see a different view of your data.

As example, I’ve uploaded a sample spreadsheet of 4000 fictitious voters, which includes the following data fields:

•    Voter ID

•    Party Affiliation

•    Their precinct

•    Age group

•    When they last voted

•    Years they’ve been registered

•    Ballot status

 

Click to enlarge

Looking at the first 20 data records, you can see the data is boring. In this format, the key question it answers is how many voters exist in all the precincts.

Using pivot tables, you can organize and group the same data in ways that start to answer questions such as:

•    What is the party breakdown by precinct?

•    Do voters use permanent absentee ballots? • Which precincts have the most Democrats?

•    How many voter pamphlets do I need for Precinct 2416?

•    Do 18-21 year olds vote?

Excel pivot tables allow you to group the spreadsheet or external data source by any of your data fields. The thumbnail below shows a count of voters by party by precinct.

 

Click to enlarge

Using a pivot table, I can continue to slice the data by selecting additional fields from the PivotTable Field List. For example, I can take the same data and segment by voter age group.

 

Click to enlarge

Understanding Pivot Table Structures

In the thumbnail above, I’ve labeled the main areas of the pivot table.

(1)   PivotTable Field List – this section in the top right displays the fields in your spreadsheet. You may check a field or drag it to a quadrant in the lower portion.

(2)   The lower right quadrants - this area defines where and how the data shows on your pivot table. You can have a field show in either a column or row. You may also indicate if the data should be counted, summed, averaged, filtered and so on.

(3)   The red outlined area to the left is the result of your selections from (1) and (2). You’ll see that the only difference I made in the last pivot table was to drag the AGE GROUP field underneath the PRECINCT field in the Row Labels quadrant.

How to Create an Excel Pivot Table

There are several ways to build a pivot table. Excel has logic that knows the field type and will try to place it in the correct row or column if you check the box. For example, data that is numeric such as Precinct counts tends to appear to the right in columns. Data, which is textual, such as Party would appear in rows.

While you can simply check fields to display and let Excel build your pivot table, I prefer to use the “drag and drop” method. This is partly because I like to visualize my data in columns and rows. I think it may also be easier if you have fields, which can appear to be numbers like a precinct value.

1.    Open your original spreadsheet and remove any blank rows or columns.

2.    Make sure each column has a heading, as it will be carried over to the Field List.

3.    Make sure your cells are properly formatted for their data type.

4.    Highlight your data range

5.    Click the Insert tab.

6.    Select the PivotTable button from the Tables group.

7.    Select PivotTable from the list.

 

Click to enlarge

The Create PivotTable dialog appears.

 

Click to enlarge

8.    Double-check your Table/Range: value.

9.    Select the radio button for New Worksheet.

10. Click OK.

A new worksheet opens with a blank pivot table. You’ll see that the fields from our source spreadsheet were carried over to the PivotTable Field List.

 

Click to enlarge

11.   Drag an item such as PRECINCT from the PivotTable Field List down to the Row Labels quadrant. The left side of your Excel spreadsheet should show a row for each precinct value. You should also see a checkmark appear next to PRECINCT.

 

Click to enlarge

12.   The next step is to ask what you would like to know about each precinct. I’ll drag the PARTY field from the PivotTable Field List to the Column Labels quadrant. This will provide an additional column for each party. Note that you won’t see any numerical data.

 

Click to enlarge

13.   To see the count for each party, I need to drag the same field to the Values quadrant. In this case, Excel determines I want a Count of PARTY. I could double-click the entry and choose another Field Setting. Excel has also added Grand Totals.

 

Click to enlarge

Additional Groupings and Options

As you build your pivot table, you’ll probably think of additional ways to group the information. For example, you might want to know the Age Range of voters by Precinct by Party. In this case, I would drag the AGE GROUP column from the PivotTable Field List down below the PRECINCT value in Row Labels.

 

Click to enlarge

Each age group is broken out and indented by precinct. At this stage, you might also be thinking of usability. As with a regular spreadsheet, you may manipulate the fields. For example, you might want to rename “Grand Total” to “Total” or even collapse the age values for one or more precincts. You can also hide or show rows and columns. These features work the same way as a regular spreadsheet.

One area that is different is the pivot table has its own options. You can access these options by right-clicking a cell within and selecting PivotTable Options… For example, you might only want Grand Totals for columns and not rows.

There are also ways to filter the data using the controls next to Row Labels or Column labels on the pivot table. You may also drag fields to the Report Filter quadrant.

Troubleshooting Pivot Tables

You might encounter several “gotchas” with this example file or another spreadsheet. Sometimes when you move around your pivot table the PivotTable Field List disappears. To get it back, click any cell with a value.

You can also move or “pivot” your data by right clicking a data field on the table and selecting the “Move” menu. From here, you can move a column to a row or even change the position. An example of this might be the values for “LAST VOTED” since Excel will sort by the month first. You might prefer to move the data so the election dates are in a chronological order.

I prefer not adding fields to a pivot table. I think it’s easier to add the fields first to your source spreadsheet. The reason is you might get items out of sync if you move data unless you make them a calculated field.

Excel pivot tables may not make the election data exciting, but it can make the analysis easier. Without these tables, you’d probably spend more time filtering, sorting and subtotaling. The other benefit is that it’s easy to start over by deselecting fields or moving them to another location. Feel free to download the tutorial spreadsheet below and play with the data. This may be the only time you’re allowed to manipulate election data.

Use templates and styles

How to Use Templates in Excel 2007

Excel 2007 provides predesigned templates, worksheetsfor common uses. Templates contain standard or boilerplate text, but you can modify the templates to fit your needs. You can use the templates automatically installed with Excel 2007, or you can download free templates from the Microsoft Office Online Web site.

The following templates are automatically installed when you start using Excel 2007:

Billing Statement, Blood Pressure Tracker, Expense Report, Loan Amortization, Personal Monthly Budget, Sales Report, and Time Card. Many additional templates are available for downloading.

Using the installed templates

Follow these steps to use one of the templates that comes installed with Excel 2007:

1.     Click the Office button and then click New.

The New Workbookdialogboxappears.

2.     Click Installed Templates under Templates in the pane on the left.

The middle pane displays thumbnails for each of the installed templates. A preview of the selected thumbnail appears in a preview pane on the right.

 

You can select an installed template to generate a new workbook.

3.     Select the desired template from the middle pane and click Create.

Excel closes the dialogboxand opens the selected template.

 

A new billing statement worksheet generated from the Billing Statement template.

4.     Customize the template as needed.

5.     Save the file with a new name. Downloading templates

If you have Internet access, you can easily check out and download any of the templates offered by Microsoft directly from the New Workbook dialogbox.

Follow these steps to download a template from Microsoft Office Online:

1.   Click the Office button and then click New.

2.   Click the category of the template you want to download under Microsoft Office Online in the Navigation pane on the left.

The middle pane displays thumbnails for each of the items in that category. In some cases, you may need to select a subcategory in the middle pane before you see the thumbnails. A preview of the selected thumbnail appears in a preview pane on the right.

 

Selecting a Budget template to download from Microsoft Office Online.

3.   Select the thumbnail image for the template you want to download and then click the Download button.

The Download button appears near the bottom-right corner of the New Workbook dialogbox.

4.   If you see the Microsoft Office Genuine Advantage dialogbox, click the Continue button.

This step verifies that you have a genuine copy of Microsoft Office. After Excel 2007 finishes downloading the template, the template file opens automatically.

5.   Customize the template as needed.

6.   Save the file with a new name.

After downloading a template from Microsoft Office Online, you can use the template to generate new workbooks by opening it from the My Templates tabof the New dialogbox(open this dialogboxby clicking the My Templates link in the Navigation pane of the New Workbook dialogbox).

Analyse data

•    Date: August 7th, 2007

•    Author: Mary Ann Richardson

•    Category: Excel, Microsoft Office, Tips

•    Tags:Data Analysis, Column, Sales, Microsoft Excel, Spreadsheet, Microsoft Excel 2007, Cell, Mary Ann Richardson

0 comment(s)

•    Email  

•    Save  

•    ShareoDiggoYahoo! BuzzoTwittero Facebook  o Google  

.usoStumbleUponoReddito Newsvine  oTechnoratioLinkedIn

•    Print  

•    Recommend  

•    8

Excel 2007’s Format As Table feature can make columns of data easy to read, while providing a set of tools that you can use to perform basic data analysis with a few mouse clicks. For example, say you have collected unit sales for 100 products sold during the last quarter. Your worksheet consists of three columns: Product Name in column A, Unit Price in column B, and Units Sold in column C. To convert this worksheet to a table, follow these steps:

1.  Click in any cell within the data range.

2.  Click the Home tab.

3.  In the Styles group, click the drop-down arrow of the Format As Table command.

4.  Point the cursor to the Table Style Medium 17 format. A preview of the format will appear.

5.  Click the Table Style Medium 17 format. The format will apply to the table.

6.  Click OK.

As you scroll through the table, note the locked column headers; with the column headers locked, they always appear on the screen. Follow these steps to add a column that will calculate the total sales for each product to the table:

1.  Enter Total Sales in D1.

2.  Enter =B2*C2 in D2.

Excel automatically calculates and copies the formula to the last product in the table.

You can now perform basic analysis on the Total Sales column. For example, to sort the total sales from highest to lowest, click the drop-down arrow in the Total Sales header cell and select Sort Highest To Lowest. You can also filter the table to show only the cells with above average sales or the top 10 or 20 performers.

When you complete your analysis, you can convert the table back to a data range by following these steps:

1.  Click any cell in the table.

2.  Click the Design tab under Table Tools in the title bar.

3.  Click the Convert To Range command in the Tools group.

4.  Click Yes at Do You Want To Convert The Table To A Normal Range?.

Create and use macros

Excel Macro Recorder Tutorial

Note: For help on these steps, refer to the image above.

Related article: Excel 2003 Macro Tutorial

This tutorial covers using the macro recorder to create a simple macro in Excel. The macro recorder works by recording all keystrokes and clicks of the mouse.

The macro created in this tutorial will apply a number of formatting options to a worksheettitles.

In Excel 2007, all macro - related commands are located on the Developer tab of the . Often, this tab needs to be added to the ribbon in order to access the macro commands.

The topics covered by this tutorial include:

•    Adding the Developer Tab

•    Starting the Excel macro recorder

•    Excel macro recorder options

•    Recording the macro

•    Running the macro

•    Macro errors / Editing the macro

Adding the Developer Tab

 

Excel 2007 Macro Recorder Tutorial

� Ted French

Adding the Developer Tab

Note: For help on these steps, refer to the image above.

1.  Click on the to open the drop down menu.

2.  Click on the Excel Options button located at the bottom of the menu to open the Excel Options dialog box.

3.  Click on the Popular option at the top of the left hand window of the open dialog box.

4.  Click on the Show Developer Tab in the ribbon in the right hand window of the open dialog box.

5.  Click OK.

6.  The Developer tab should now be visible in the ribbon.

Adding a Worksheet Title / The Excel Macro Recorder

 

Excel 2007 Macro Recorder Tutorial

� Ted French

Adding a Worksheet Title

Note: For help on these steps, refer to the image above.

Before we start recording our macro, we need to add the worksheettitle we will be formatting.

Since the title of each worksheet is usually unique to that worksheet, we don't want to include the title in the macro.

Therefore we will add it to the worksheet, before starting the macro recorder.

1.    Click on cell A1 in the worksheet.

2.    Type the title: Cookie Shop Expenses for June 2008.

3.    Press the Enter key on the keyboard. Excel Macro Recorder

The easiest way to create a macro in Excel is to use the macro recorder. To do so:

1.  Click on the Developers tab.

2.  Click on Record Macro in the ribbonto open the Record Macro dialog box.

The Macro Recorder Options

 

Excel 2007 Macro Recorder Tutorial

� Ted French

The Macro Recorder dialog box

Note: For help on these steps, refer to the image above.

There are 4 options to complete in this dialog box:

1.  Macro name - give your macro a descriptive name. The name must begin with a letter and spaces are not allowed. Only letters, numbers and the underscore character are permitted.

2.  Shortcut key - (optional) fill in a letter, number, or other character in the available space. This will allow you to run the macro by holding down the CTRL key and pressing the chosen letter on the keyboard.

3.  Store macro in

Options:

o    This workbook

§   The macro is available only in this file.

o    New workbook

§   This option opens a new Excel file. The macro is available only in this new file.

o    Personal macro workbook.

§   This option creates a hidden file which stores your macros and makes them available to you in all Excel files.

4.  Description - (optional) enter a description of the macro.

For this tutorial:

1.  Set the options in the Record Macro dialog box to match those in the image above.

2.  Do Not click OK - yet - see below.

o    Clicking the OK button in the Record Macro dialog box starts recording the macro you have just identified.

o    As previously mentioned, the macro recorder works by recording all keystrokes and clicks of the mouse.

o    Creating the format_titles macro involves clicking on a number of format options on the home tab of the ribbon with the mouse while the macro recorder is running.

3.  Go to the next step before starting the macro recorder.

Adding the Macro Steps

 

Excel 2007 Macro Recorder Tutorial

� Ted French

Recording the Macro

Note: For help on these steps, refer to the image above.

1.  Click the OK button in the Record Macro dialog box to start the macro recorder.

2.  Click on the Home tab of the ribbon.

3.  Drag selectcells A1 to F1 in the worksheetto highlight them.

4.  Click on the Merge and Center icon to center the title between cells A1 and F1.

5.  Click on the Fill Color icon (looks like a paint can) to open the fill color drop down list.

6.  Choose Blue, Accent 1 from the list to turn the background color of the selected cells to blue.

7.  Click on the Font Color icon (it is a large letter "A") to open the font color drop down list.

8.  Choose White from the list to turn the text in the selected cells to white.

9.  Click on the Font Size icon (above the paint can icon) to open the font size drop down list.

10.  Choose 16 from the list to change the size the text in the selected cells to 16 point.

11.  Click on the Developer tab of the ribbon.

12.  Click the Stop Recording button on the ribbon to stop the macro recording.

13.  At this point, your worksheet title should resemble the title in the image above.

Running the Macro

 

Excel 2007 Macro Recorder Tutorial

� Ted French

Running a Macro in Excel

Note: For help on these steps, refer to the image above.

To run a macro you have recorded:

1.  Click on the Sheet2 tab at the bottom of the spreadsheet.

2.  Click on cell A1 in the worksheet.

3.  Type the title: Cookie Shop Expenses for July 2008.

4.  Press the Enter key on the keyboard.

5.  Click on the Developer tab of the ribbon.

6.  Click the Macros button on the ribbon to bring up the View Macro dialog box.

7.  Click on the format_titles macro in the Macro name window.

8.  Click the Run button.

9.  The steps of the macro should run automatically and apply the same formatting steps applied to the title on sheet 1.

10.  At this point, the title on worksheet 2 should resemble the title on worksheet 1.

Macro Errors / Editing a Macro

 

Excel 2007 Macro Recorder Tutorial

� Ted French Macro Errors

Note: For help on these steps, refer to the image above.

If your macro did not perform as expected, the easiest, and best option is to follow the steps of the tutorial again and re - record the macro.

Editing/Step Into a Macro

An Excel macro is written in the Visual Basic for Applications (VBA) programming language.

Clicking on either the Edit or Step Into buttons in the Macro dialog box starts the VBA editor (see the image above).

Using the VBA editor and covering the VBA programming language is beyond the scope of this tutorial.

Protect a spreadsheet

Protection of documents and cells can help prevent inadvertent changes to your worksheet. This can be especially helpful if you have someone who is unfamiliar with worksheets or doing your data entry, or if you spent many hours struggling to get the worksheet right.

Excel offers the option of protecting the entire document, individual objects, the structure of a window, and/or specific cells. With Excel, you can even add a password to the file. If you want to prevent changes to sheets or cells, you need to lock the cells and then protect the sheet.

•      Protection Options

•     

•     

•      Worksheet Level Protection

•       Protection Options

When you are protecting your workbook, you have two primary options:

•      Prevent data entry for select cells

Users can access the worksheet and view the information; however, access for making changes is restricted. For more information, refer to .

•      Restrict or prevent access to the file

Users can be prevented from viewing the worksheet, or users can view the workbook but not make changes to it. For more information, refer to .

 Locking & Unlocking Cells

Excel can protect cells, graphics, charts, and other worksheet objects. This protection will take effect only after you turn on the Worksheet Protectionoption in the Protect Sheet dialog box. If you enable protection, no changes can be made to a cell until you unlock that cell.

Locking Cells

You can easily lock any cell in a worksheet.

NOTE: Make sure to lock the cells before you protect the sheet or document. Once a sheet or a document has been protected, you cannot access menu selections that allow you to make changes to cells.

1.  Select the cell(s) to be locked

2.  From the Ribbon, select the Home command tab

3.  In the Cells group, click FORMAT

4.  In the Protection section, select Lock

NOTE: If the icon is highlighted, the cells are locked.

5.  Protect the worksheet

Unlocking Cells

In order to unlock cells, sheet protection must first be turned off. For more information, refer to Worksheet LevelProtectionor Workbook Level Protection.

1.  Unprotect the worksheet

2.  Select the cells you want to unlock

3.  From the Ribbon, select the Home command tab

4.  In the Cells group, click FORMAT

5.  In the Protection section, deselect Lock

The cells are unlocked.

NOTE: If the icon is not highlighted, the cells are unlocked.

 Workbook Level Protection

You can prevent a workbook from having its structure and windows modified or resized by another user.

Protecting the Workbook

1.  From the Ribbon, select the Review command tab

2.  In the Changes group, click PROTECT WORKBOOK The Protect Workbook dialog box appears.

 

3.  Select the appropriate option(s):

Structure

Prevents the user from changing the order of the sheets within a workbook. This includes adding or deleting worksheets.

Windows

Prevents the user from being able to resize or move the window.

Password

Allows only those who know the password entered in this text box to turn the workbook protection off.

4.  Click OK

The workbook is protected.

Unprotecting the Workbook

1.  From the Ribbon, select the Review command tab

2.  In the Changes group, click PROTECT WORKBOOK

The workbook is unprotected. Users can now modify the structure and windows of the workbook. NOTE: If you included a password when you turned the protection on, you must enter the password to turn the protection off.

 Worksheet Level Protection

You can protect a single worksheet from being modified by another user.

Protecting the Worksheet

1.  From the Ribbon, select the Home command tab

2.  In the Cells group, click FORMAT

3.  In the Protection section, select Protect Sheet

The Protect Sheet dialog box appears.

 

4.  In the Protect Sheet dialog box, select the appropriate options:

Protect worksheet and contents of locked cells Prevents changes to locked cells.

Password to unprotect sheet

Allows only those who know the assigned password to unprotect the worksheet.

Allow all users of this worksheet to

Checked boxes are aspects that any user can access.

5.  Click OK

The worksheet is protected.

Unprotecting the Worksheet

1.  From the Ribbon, select the Home command tab

2.  In the Cells group, click FORMAT

3.  In the Protection section, select Unprotect Sheet

The worksheet is unprotected. Users can now modify the worksheet.

NOTE: If you included a password when you turned the protection on, you must type the password in the Password text box to turn the protection off.

 File Level Protection

You have two options when saving your workbook file with a password. You can save the file so only those who know the password can open the file, or so only those who know the password can modify the file. Protecting the File

1.  From the File menu, select Save As The Save As dialog box appears.

2.  From the Tools pull-down list,select General Options The General Options dialog box appears.

 

3.  To prevent unauthorized access to the file, in the Password to open text box, type a password

To prevent unauthorized changes to the file, in the Password to modify text box, type a password

4.  OPTIONAL: If you would like Excel to recommend that this file be opened as a read-only file each time it is opened, select Read-only recommended

HINT: Read-only files can be modified, but the changes cannot be saved without creating a new file.

5.  Click OK

The Confirm Password dialog box appears.

6.  In the Reenter password to modify text box, type the password(s) in the order of the password(s) that you typed in the Save Options dialog box

EXAMPLE: If you typed apple in the Password to open text box and orange in the Password to modify text box, you must type apple in the first confirmation dialog box and orange in the second confirmation dialog box.

7.  Click OK

8.  Click SAVE

Your file is saved with password protection.

Unprotecting the File

If you no longer need to password-protect the file, you can remove the password.

1.  Open the file

NOTE: In order to open the file, you will need to know the password.

2.  From the File menu, select Save As… The Save As dialog box appears.

3.  In theSave Asdialog box, from the Tools pull-down list, select General Options

4.  In the Password to open text box, select the text » press [Delete]

5.  In the Password to modify text box, select the text » press [Delete]

6.  Click OK

The file is no longer password protected.

7.  To save the file, click SAVEA confirmation dialog box appears.

NOTE: This will overwrite the existing saved file with the new file, which is currently not passwordprotected. Any other changes you have made to this file since opening it will also be saved and will overwrite the old document.

8.  Click YES

Your file is re-saved without password protection.

Consolidate a work book What do you want to do?

If you want to…

Then…

Arrange the data in all worksheets in identical order and location

Organize the data differently in the separate worksheets, but use the

Consolidate by position

same row and column labels so that the master worksheet can match the data

Consolidate by category

Use formulas with cell references or 3 D references (3-D reference: A  reference to a range that spans two or more worksheets in a workbook.) to

other worksheets that you are combining because you do not have a consistent position or category to rely on

Consolidate by formula

Use a PivotTable report instead of a consolidation

Use a PivotTable report to consolidatedata

Consolidate by position

1.  Set up the data to be consolidated on each separate worksheet.

How to set up the data

o    Make sure that each range of data is in   list  (list: A series of rows that contains related  data or a series of rows that you designate to function as a datasheet by using the Create List command.) format: each column has a label in the first row and contains similar facts, and there are no blank rows or columns within the list.

o    Put each range on a separate worksheet. Don't put any of the ranges on the worksheet where you plan to put the consolidation. o Make sure that each range has the same layout.

o    Name each range: Select the entire range, and then on the Formulas tab in the Named Cells group, click the arrow next to Name a Range, and type a name for the range in the Name box.

2.  Click the upper-left cell of the area where you want the consolidated data to appear in the master worksheet.

 Note    Make sure that you leave enough cells to the right and below this cell for the consolidated data. The Consolidate command populates the area as needed.

3.  On the Data tab, in the Data Tools group, click Consolidate.

 

4.  In the Function box, click the  summary function (summary function: A type of calculation that  combines source data in a PivotTable report or a consolidation table, or when you are inserting automatic subtotals in a list or database. Examples of summary functions include Sum, Count, and Average.) that you want Microsoft Office Excel to use to consolidate the data.

5.  If the worksheet is in another workbook, click Browse to locate the file, and then click OK to close the Browse dialog box.

The file path is entered in the Reference box followed by an exclamation point.

6.  Type the name that you gave the range, and then click Add. Repeat this step for each range.

7.  Decide how you want to update the consolidation. Do one of the following:

o    To set up the consolidation so that it updates automatically when the source data changes, select the Create links to source data check box.

 Important   You can only select this check box if the worksheet is in another workbook. Once you select this check box, you won't be able to change which cells and ranges are included in the consolidation.

o    To set up the consolidation so that you can update the consolidation manually by changing the included cells and ranges, clear the Create links to source data check box.

8.  Leave the boxes under Use labels in blank. Excel does not copy the row or column labels in the source ranges to the consolidation. If you want labels for the consolidated data, copy them from one of the source ranges or enter them manually.

Top of Page

Consolidate by category

1.  Set up the data to be consolidated on each separate worksheet.

How to set up the data

o    Make sure that each range of data is in   list  (list: A series of rows that contains related  data or a series of rows that you designate to function as a datasheet by using the Create List command.) format: each column has a label in the first row and contains similar facts, and there are no blank rows or columns within the list.

o    Put each range on a separate worksheet. Don't put any of the ranges on the worksheet where you plan to put the consolidation.

o    Make sure that the labels for columns or rows that you want to combine have identical spelling and capitalization; for example, labels Annual Avg. and Annual Average are different and will not consolidate.

o    Name each range: Select the entire range, and then on the Formulas tab in the Named Cells group, click the arrow next to Name a Range, and type a name for the range in the Name box.

2.  Click the upper-left cell of the area where you want the consolidated data to appear in the master worksheet.

 Note    Make sure that you leave enough cells to the right and below this cell for the consolidated data. The Consolidate command populates the area as needed.

3.  On the Data tab, in the Data Tools group, click Consolidate.

 

4.  In the Function box, click the  summary function (summary function: A type of calculation that  combines source data in a PivotTable report or a consolidation table, or when you are inserting automatic subtotals in a list or database. Examples of summary functions include Sum, Count, and Average.) that you want Excel to use to consolidate the data.

5.  If the worksheet is in another workbook, click Browse to locate the file, and then click OK to close the Browse dialog box.

The file path is entered in the Reference box followed by an exclamation point.

6.  Type the name you gave the range, and then click Add. Repeat this step for each range.

7.  Decide how you want to update the consolidation. Do one of the following:

o    To set up the consolidation so that it updates automatically when the source data changes, select the Create links to source data check box.

 Important   You can only select this check box if the worksheet is in another workbook. Once you select this check box, you won't be able to change which cells and ranges are included in the consolidation.

o    To set up the consolidation so that you can update the consolidation manually by changing the included cells and ranges, clear the Create links to source data check box.

8.  Select the check boxes under Use labels in that indicate where the labels are located in the source ranges: either the Top row, the Left column, or both.

  Notes 

o    Any labels that don't match up with labels in the other source areas result in separate rows or columns in the consolidation.

o    Make sure that any categories that you don't want to consolidate have unique labels that appear in only one source range.

Top of Page

Consolidate by formula

1.  On the master worksheet, copy or enter the column or row labels that you want for the consolidated data.

2.  Click a cell that you want to contain consolidated data.

3.  Type a formula that includes a cell reference to the source cells on each worksheet or a 3-D reference that contains data that you want to consolidate. Regarding cell references, do one of the following:

If the data to consolidate is in different cells on different worksheets 

o    Enter a formula with cell references to the other worksheets, one for each separate worksheet. For example, to consolidate data from worksheets named Sales (in cell B4), HR (in cell F5), and Marketing (in cell B9), in cell A2 of the master worksheet, you would enter the following:

 

Tip  To enter a cell reference, such as Sales!B4, in a formula without typing, type the formula up to the point where you need the reference, click the worksheet tab, and then click the cell.

If the data to consolidate is in the same cells on different worksheets 

o    Enter a formula with a 3-D reference that uses a reference to a range of worksheet names. For example, to consolidate data in cells A2 from Sales through Marketing inclusive, in cell A2 of the master worksheet you would enter the following:

 

 Note   If the workbook is set to automatically calculate formulas, a consolidation by formula always updates automatically when the data in the separate worksheets change.

Top of Page

Use a PivotTable report to consolidate data

You can create a  PivotTable report   (PivotTable report: An interactive, crosstabulated Excel report that summarizes  and analyzes data, such as database records, from various sources including ones external to Excel.) from multiple consolidation ranges. This method is similar to consolidating by category, but it offers more flexibility to reorganize the categories. For more information see Consolidate multiple workshee

Appendix – Definitions & Resources

Resources

ACCA -

ICAEW-

AIA -

Accounting web -



7