Advanced Microsoft Excel Qg

ADVANCED MICROSOFT EXCEL QUICKGUIDE

http://www.itd.depaul.edu/website/students/training.asp Email us at: [email protected] DePaul University

Table of contents

Working with Multiple Worksheets

Working with Multiple Worksheets

Importing Data

Importing Data Sorting Data Filtering Data Conditional Formatting Using Functions

1- Click Data, Import External Data, Import Data 2- Locate your file, click Open FYI: When importing a *.txt file Check the Delimited radio box option, Next, Next, Finish. Importing MS Access will only ask you to decide where you want the data’s final destination to go (Existing worksheet or new worksheet)

Using cell names Working with Charts Creating Charts Modifying Charts Workbook Protection Help

3- Click OK 4- OR if you are importing a simple text file you can simply to go File, Open and the Data Import Wizard will automatically be opened for you when you open your file.

Sorting Data 1- Highlight desired column(s), click Data, Sort FYI: if you select one column out of many, the system will ask you to select one of the following options:

4- Then press the Format button to set the format and click OK

Paste Special

2- Select your sort by choice. You usually want to ‘expand the selection’ so that all data to the left and right of your selection is also sorted and kept with the highlighted column. 3- Click OK

Filtering Data 1- Click, Data, Filter, Auto-Filter

Conditional Formatting 1- Highlight required range of cells 2- Click Format, Conditional Formatting 3- Make your selections

1- If you want to transpose a column of data into a row, or a row of data into a column you can use the Paste Special function. Select the data you want to transpose. Right click and choose Copy. 2- Put your cursor where you are going to paste the data. Right click and choose Paste Special. 3- Choose the Transpose checkbox at the bottom of the Paste Special screen. 4- Click Ok

Using Functions 1- Click Insert, Function, you may also click on the toolbar icon 2- Select your category 3- Click OK

Financial

PMT

Date & Time

NOW

Logical

IF, FALSE, TRUE

SUMIF: adds only those cells that meet a given criteria AVERAGE: returns the average of its arguments COUNT: counts the total number of arguments in a list COUNTIF: counts the number of cells within a range that meets a given criteria PMT: calculates the payment for a loan based in constant payments and a constant interest rate NOW: calculates the current date and time IF: performs one calculation is a certain condition is true and a different condition if that condition is false FALSE: returns the logical value false TRUE: returns the logical value true 4- You can use the Fill Handle to copy a formula into many columns. For example, if you have created the formula =SUM(A1:A5) for column A, you can click on the cell containing the formula. Move your mouse to the bottom right corner until it becomes a plus sign. This is the fill handle. You can click and drag to the right for example and create the following: =SUM(B1:B5) for column B.

Most common functions Category

Function

Math & Trig

SUMIF

Statistical

AVERAGE, COUNT, COUNTIF

Working with Charts Creating Charts 1- Highlight the range of data cells that are required in the chart 2- Click, Insert, Chart 3- Make your required selections using the chart wizard

Name or Values box. You can then click on the Name in your spreadsheet or highlight the values in the spreadsheet. When you are done just click the arrow again to bring back the Source Data screen!) 6- Click OK

4- Click Next, Next, Next 5- Make your selection of where you want to place the chart 6- Click Finish

Changing Chart/Graph Type 1- Click within the graph 2- Right click and select Chart Type 3- Select your choice, click OK

Modifying Charts Adding and/or Removing Items 12345-

Click inside the graphic Right click, select Source Data Click on Series tab Add or Remove items Type the Name or change the Values as required. (Hint: To do this it is easiest to click on the small arrow that appears on the right hand side of the

Formatting the Chart/Graph 1- Double click inside the chart or click within the chart, right click and select Format Chart Area 2- Make your choices 3- Click OK

Adding a Trend line (Regression Analysis) 1234-

Right click inside the chart area element(s) Select Add Trendline Select your Type Click OK

Workbook Protection 5678-

Click Tools, Protection Select your choice: Protect Sheet or Protect Workbook Enter password, click OK Re-enter password, click OK

Help 1234-

Click Help, Show the Office Assistant Double-click on the smiley face Type in your question Click Search