Lexington Public Library | Reading is Just the Beginning ...



Advanced Features of ExcelFormula TypesCategoryExamplesLogicalThe most common logical function is the IF function, which we used in the Student Grades exercise. But there are several others. Logical functions are most often used to return a particular value (TRUE or FALSE) to make a decision Date and TimeDate and Time functions perform calculations based on Date and Time formatted numbers. To represent the date format, Excel automatically calculates a whole number that begins on 1/1/1900 (1), and runs sequentially through 12/31/9999 (2,958,465!) Excel thinks of time as a fraction of a day, so every hour, minute, and second has a decimal representation. For example, 12:00:00 PM is represented as exactly .5 of a day. Using the decimal portion of a number allows Excel to combine date and time into a single number—for example 1/1/1900 at 12 PM is the number 1.5 to Excel. Commonly used date and time functions include TODAY, WEEKDAY, and DATEDIFTextText functions manipulate Text. They can be used to remove parts of the text, add to the text, combine text, change case, etc. Examples include UPPER and CONCATENATE.FinancialFinancial functions include functions to perform tasks like calculate compounded interest, amount paid to principal, etc. No examples of this function are used in class, due to the narrow professional application of the functions.Math and TrigPerform mathematical operations, and includes many of the most common operations performed, including ROUNDUP, MAX, AVERAGE, and SUM along with trigonometric functions.Engineering and StatisticalThese functions are included under More Functions, and engineers and statisticians should be aware of their presence, but we don’t use them in our class.Lookup and ReferenceFunctions which refer to other cells and files to obtain information. This includes the locations of cells, so that a lookup function could get the value of a cell to be used in another formula—a very advanced use of Excel! VLOOKUP is an example.4998085-240665Constructing Formulas...13All formulas must begin with an equal sign (=) to indicate that a calculation is about to follow.To specify the number in a specific cell, use the cell’s name when creating a formula, for example "=A1+B1"To specify a range of cells, type the name of the first cell, followed by a colon, and then the name of the last cell. For example, type “B1:B5” to specify all the numbers in the cells B1, B2, B3, B4, and B5.To indicate a specific type of calculation, you can use one of several functions, including SUM, AVERAGE, etc. (A complete list of available functions can be found in the Insert Function dialog box.)Excel orders calculations by priority:( )^*/+-If, for example, the calculation is 2+2*5, Excel would multiply 2*5 first and then add 2. The total would be 12. If we place parentheses around (2+2), that calculation will be performed first before being multiplied by 5. The total would be 20.Using External Reference Formulas: If you would like to have information from one sheet included in calculations on another sheet, you can use an external reference formula. An external reference formula is a formula containing references to cells not located in the current sheet. When the information referenced is changed, the result of the formula is also changed. Note that the sheet name is followed by the exclamation point and then followed by the cell reference: "=SUM(sheetname!cellname)"Using an IF Statement: Excel’s IF function can make decisions based on whether a test condition is true or false. An IF statement can be written as follows: "=IF (what you want tested, what to do if the result if true, what to do if the result if false)". Commas separate each argument. Do not use spaces after the commas within the parenthesis. Example: To perform a calculation of overtime, you could type =IF(B10>40,B10-40,0). You would be telling the program to test if the number in cell B10 is greater than the number 40. If that is the case, subtract the number 40 from the number in cell B10. If the number in cell B10 in smaller than 40, the result is 0.Exercise #1: Customer OrdersGetting StartedOpen the document “Customer Orders” from the Excel Exercises folderChoose “Format as Table” from Home Tab > Styles group on the ribbon. Formatting your data as a table is a good way to tell Excel that all of your data, including the headings, belongs together.Now enter the following in the first open row under the table (notice that the table automatically expands to include the new data!)Use AutoFill (make sure your cursor is the ‘skinny plus sign’ by hovering over the bottom right corner of A77 and then drag and drop to A78) to copy the order number from A77 to A78. Now click on the little button that pops up beside A78 and choose “Fill series”. This button is the Auto-Fill options button, and it will often provide a variety of helpful choices when you are pasting data, including keeping or discarding formatting and incrementing numbers (note that you don’t want to use the formula =A77+1 to increment, as this can be changed when you sort your data!)ADD COLUMN FOR “Days to Process”Insert a column before the “Customer” column (Column E) either by right-clicking on the column header and choosing “Insert…” or by selecting column E in its entirety and then clicking Insert on the Home tab > Cells group.Type “Days to Process” in cell E1. When you have a table in Excel, it’s best to have headings so the data is identified.Page 2 of this packet describes how Excel handles Date and Time data and formulas—for now, we will create a formula that figures out how long it takes for us to ship orders from the order date.Create formula to calculate Days to process order: Enter the following formula in Cell E2: =DATEDIF(use the mouse to select C2,use the mouse to select D2, “d”)Note that Excel will automatically realize that we want this formula throughout the entire table. DATEDIF is a special function that is not in the Help feature and cannot be found in the “Insert Function” menu. The syntax is =DATEDIF (Start Date, End Date, Interval). Start Date and End Date are usually cell references (as in our case), and the interval is “d” for number of days, “m” for number of months, and “y” for number of years. But there’s a problem – all of our DATEDIF results are dates, and they should just be numbers (representing a number of days). We can change this by going to the Home Tab and choosing the number format with the drop down menu (that currently says “Date”) in the number group. Notice that when we choose the number it has two decimal places by default—thinking of how Excel handles dates and times, can you see how this might be useful? In our case, just change the decimal places to zero by clicking the Decrease Decimal button twice (remember—this button is on the Number group of the Home tab).MORE DATE AND TIME FUNCTIONSInsert a new column F Put label on column “Day Ordered”As before, choose the first cell (F2) and enter this formula: =WEEKDAY(use the mouse to select C2) The WEEKDAY function will return a single digit number (1-7) telling us what day it is. Make sure column F is selected and go to the format dropdown in the Number section of the Home ribbon. Select the more number formats at the bottom. In the dialog box that opens, select custom. Type dddd into the box under Type: and press OK. This will show the actual name of the weekday. TEXT FUNCTIONSMove to sheet 2. Change the sheet name to “Telephone List”.Type “Date Printed” in cell A1.Type the formula =TODAY() in B1. This formula will return the current date – very useful for many reasons, but especially useful for calculating elapsed.OK, back to making our phone list! Type headers in A2 and B2: Customer and Phone Number (A2) and State (B2).In cell A3, type this formula: =CONCATENATE(click on Sheet1 cell G2 with your mouse , “ enter 5 spaces “ , click on Sheet1 cell L2 with your mouse ). Double check before you hit ENTER – the finished formula should look like this: =CONCATENATE(Sheet1!G2," ",Sheet1!L2).Now use the Autofill feature to copy your formula down the column. If you go far down the spreadsheet, your data will fill in as it is updated, so drag down to cell 300 or so. Since the cells being combined are currently blank, the column will not look like anything is there, but there will be 5 spaces! The concatenate function is useful for combining data from different cells into a single cell, and is commonly used for items like addresses.In Column B on the “Telephone List” sheet, let’s fix a common data entry error: In Cell B3, type the formula =UPPER(Sheet1!J2). This formula will convert all text to upper case (useful for example, for addresses, which the USPS prefers be in upper case).FILTERING AND SORTING DATA IN A TABLEBefore we do Pivot tables, note the little arrows next to the headers on our data table—note that you can filter this data table by clicking on a down arrow next to the header you are interested in. You can also sort the data in the same way. Pivot tables will allow you to do this as well, but a little more in depth. They’ll also allow you to perform calculations and really make your data work for you!PIVOT TABLESPivot Tables can’t have blank columns in them or blank rows, but individual cells can be blank.To create a pivot table, click on the insert tab and choose the Pivot Table button.A box will pop up and you must choose a table or a range for the Pivot Table to pull data from. If you have already made a table, Excel will usually just recognize it and fill in your range for you. Excel will also suggest a range that matches the area it thinks is a table if you have not actually formatted your data as a table.You must choose whether you want the data on a new worksheet or on your current sheet. It’s usually best to use a new worksheet.It’s also usually best to use only one Pivot Table per sheet.To create a report, simply choose the categories you need from the Pivot Table Field List on the right—for examplePlace a check mark on Price, then place a check mark on StateThat easily, we have created a report that gives us the sum of merchandise sold by state.Now look closely at the “Row Labels” section –we can filter out the states we aren’t interested in at the moment by clicking here and getting a drop down menu to choose which states we want to look at and leave the others out.And we don’t have to do a SUM—go ahead and double click on the “Sum of Price” header and choose AVERAGE insteadNow change it back to SUM.Let’s add a little more detail—Add the cities in by dragging the City field to the Row Labels section. Be sure and drop it BELOW the State label (it should fit below State in the hierarchy!)And let’s add even more—drag the Customer field down as well, dropping it below the City fieldPIVOT CHARTSPivot Charts work a lot like Pivot tables, except you can create a chart with them, instead of tabular data.Be sure and choose a new worksheet again when you create a Pivot Chart. One thing to remember about Pivot Tables and Charts is that they can’t overlap each other, so you should try and keep one per sheet.You can select the fields you want displayed in the chart in the same manner (by dragging them to the Axis, Legend, or Sum Areas). You can also use the Chart tools to choose the chart type and other modifications.For example, let’s create a pie chart to show our sales by state:Click anywhere in the table on Sheet 1. Click on the Insert tab, then click on the words “Pivot Chart” in the Charts section of the Insert tab and select “Pivot Chart”.Excel automatically chooses the whole table, but you could select a different range in this popup window. Likewise, the default option is a new worksheet, but you could put it in the same sheet—a new worksheet works best for each Pivot Chart or Pivot Table.Close the Pivot Chart Filter Pane window that pops up when your chart is created—the actions you can perform with this can also be performed using the Pivot Chart field list to the rightIn the Pivot Chart field list, drag the “State” field to the “Axis” portion of the chart. This sets the categories for your chart.Now drag the “Price” field to the “Values” portion. It will create a “Sum of” field by defaultBut wait, the chart is wrong! We wanted a pie chart and this is a column chart. To change the chart, we need to select it, click on the Pivot Chart tools Design tab. In the Type section select “Change Chart Type”. In the box that pops up select “Pie” in the menu on the left, then select the first pie chart option and click “Ok”. While still in the Design tab for your Pivot Chart, click Quick Layout in the Chart Layouts section and select Layout 1Finally, click on the “Total” title box and replace “Total” with “Sales by State”-19056985The remaining items are not critical to understanding Additional Features of Excel. Both are types of data entry controls, and can be used to make your data entry a little more accurate, but neither Data Validation or the Vlookup function are necessary to using Excel effectively. This course is designed to emphasize Linked Cells, PivotTables, Pivot Charts, and advanced formulas, so as long as you understand these, you’ve got the main ideas down. If the pace of the class allows, your teacher may cover these in class.DATA VALIDATIONReturn to Sheet 1.In addition to correcting common data entry errors, it is also possible to set columns in a table so that erroneous data cannot be entered.Click on column D and then click on Data Validation.282702011430Set the popup to the following:Notice the start date is the day of the order, and the end date is the current date – no product could be shipped before it was ordered, and no item could be entered as shipped in the future.You can customize the error message and put in a message that comes up before people enter data, but most of the time the default message is clear enough. If you did wish to customize the messages you would use the additional tabs.Look at Cell D18 – notice the tiny green triangle in the upper left corner? That means there is a Data Validation error in this cell that was there before we turned on Data Validation. 10/1/2013 is before 10/6/2013, and there’s no way we could have shipped an item before it was ordered. Try entering a date before 10/6/2013 (the date of the order) or after today’s date and see what happens. In the popup window that comes up click “Retry” and enter the corrected date of 10/13/2013. Notice you can’t even re-enter the incorrect data now!VLOOKUP FUNCTIONLet’s experiment with another version of data control, the VLOOKUP function.Choose column H and insert a column on Sheet1 called ‘Region’.Regions are already set up on Sheet 3 (already named Region), so we won’t need to enter them. In a ‘real’ situation all 50 states would be entered, but we’re only using states we’ve had orders from.When using the VLOOKUP function, your lookup array (remember—an array is just a set of associated values, like KY and Southeast, NY and Northeast, etc. We used an array in our Grades exercise.) must already be sorted in ascending order.To enter the function, click in cell H2 and type =VLOOKUP( click on cell K2 on Sheet1, Regions!$A$1:$B$19,2)The dollar signs are necessary for the array on Region—remember the difference between absolute and relative references, taught in exercise #1. Also, the 2 in the formula refers to the column that is being used to lookup the value. Essentially Excel is looking for the state and then putting the region into the cell that matches the state on the same row.Self-Evaluation: Additional Features of ExcelPlease keep this form for your own reference.Evaluate your understanding of the following topics. Be sure to ask your instructor to clarify any concepts you find icvery clearcleara little confusedvery confusedRenaming worksheets and adding colorCreating formulasUsing office assistant to search for functionsCell ranges (cell:cell)Inserting cellsLinking cellsSample functions (SUM, AVERAGE, ROUNDUP, IF)Advanced formula types (general concepts)Date and Time formulas (TODAY, WEEKDAY, DATEDIF)Text formulas (CONCATENATE, UPPER)Creating a TableSorting and Filtering a TableExamining Data and Creating Reports with Pivot Tables and Charts**Optional: using Data Validation and Lookup functionsIMPORTANT:Before moving onto the next class, you should answer “very clear” or “clear” to most of the above.If you need further help with these topics, consider taking the class again after practicing your skills independently. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download