Www.lexpublib.org



Advanced Features of ExcelFormula TypesLOGICAL Logical functions are most often used to return a particular value (true or false) to make a decision. Examples include: IF, AND, and OR. DATE AND TIMEDate and Time functions perform calculations based on Date and Time formatted numbers. Excel represents date information using a range of whole numbers that begins on 1/1/1900 (1), and ends on 12/31/9999 (2,958,465!) July 20, 1969, the date of the moon landing, corresponds to the number 25, 404. By matching up dates with whole numbers, Excel can easily make calculation. 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.Examples 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. They have a somewhat narrow professional application.Examples include: ACCRINT, ISPMY, and TBILLYIELD.MATH AND TRIGONOMETRYIncludes many of the most commonly used mathematical functions, including those related to the area, volume and angles of shapes and spaces.Examples include: ROUNDUP, MAX, AVERAGE, and SUM.LOOKUP AND REFERENCEFunctions which access other worksheets and files to obtain information. They often included the full (long form) locations of cells, so that a lookup function could get the value of a cell to be used in another formula regardless of how ‘far away’ it is — a very advanced use of Excel! Examples include: VLOOKUP, RTD, and MATCH. MORE FUNCTIONSContains even more specialized functions like those used in Statistics and Engineering. Constructing Formulas...13All formulas must begin with an equal sign (=) to indicate that a calculation is about to follow. Excel orders calculations by priority: ( )^*/+-If 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.To specify the number in a specific cell, use the cell’s name when creating a formula, for example "=A1+B1"To specify a cell not on the same worksheet, use the long form of the cell name. Formulas that refer to other sheets and workbooks are often referred to as External Reference Formulas. An ! is used to mark the boundary between the sheet name and cell name. Square brackets [ ] are used to denote the specific file or workbook a sheet and cell reside in. For a different worksheet in the same workbook: =Sheet2!A1+B1 For a completely different file: [Example.xlsx]Sheet1!A1+B1To specify a range of cells, type the name of the first cell, followed by a colon, and then the name of the last cell. Example: “B1:B5” specifies 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.)When using a Function, commas separate each argument or distinct piece of the Function. Do not use spaces after the commas. Example: SUM(A3, D5, H32)Exercise #1: Customer OrdersGETTING STARTEDOpen the document Customer Orders from the Excel Exercises folderChoose Format as Table from the Styles section on the Home ribbon. Formatting your data as a table is a good way to tell Excel that all of your data, including the headings, belongs together.Scroll down to cell A77, use AutoFill to copy the order number down several rows.Click the AutoFill Options button that appears beside the new numbers and choose Fill series from the dropdown menu.The order numbers should now form a clear progression, increasing by 1 in each cell down the column.The Auto-Fill options button will often provide a variety of helpful choices when you are pasting or expanding data, including keeping or discarding formatting and incrementing numbers. This allows you to avoid the use of formulas that might change when you sort your data.CALCULATE HOW LONG IT TAKES TO PROCESS ORDERSSelect column E, Customer, and insert a new column in front of it.Name the new column by typing Days to Process in cell E1. When you have a table in Excel, it’s best to have headings so the data is identified.Click into Cell E2 and enter the formula: =DATEDIF(use the mouse to select C2,use the mouse to select D2, “d”) 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, 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. Make sure column E is still selected and go to the Number section of the Home ribbon. Use the dropdown at the top of the section to reselect General. MORE DATE AND TIME FUNCTIONSSelect column F and insert a new column.Name the new column Day Ordered. Click into 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 still selected and go to the dropdown in the Number section of the Home ribbon.Select 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 and change the sheet name to Telephone List.Type Date Printed in cell A1.Type the formula =TODAY() in cell B1. This formula will return the current date – very useful for many reasons, but especially useful for calculating elapsed time.Type headers: Customer and Phone Number in cell A2 and State in cell B2.In cell A3, type this formula: =CONCATENATE(click on Sheet1 cell G2 with your mouse , “ space 5 times “ , 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 Autofill 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. Click into cell B3 and 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. You can Filter, select data that fits certain requirements, 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 TABLESClick on a cell containing data within your table. Make sure the table contains no blank rows or columns. Pivot Tables can’t have blank columns in them or blank rows, but individual cells can be blank.Go to the Tables section of the Insert ribbon and choose the Pivot Table button.A box will pop up, the name of your table should already be selected. 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. Pivot tables cannot overlap, so be very careful if you try to put more than one on a single worksheet. You will need to designate a specific cell location on the sheet to ensure they have enough space between them. In the panel on the right, drag Price down to the area called Values.Drag State to the Rows area and watch how the values adjust with the additional data. We have created a report that gives us the sum of merchandise sales by state.Look closely at the Row Labels button at the top of the pivot table display, we can filter or hide some of the states by clicking here and unchecking them on the dropdown menu.Remove the checkmarks from NY, MN, and OH. These are our three lowest preforming sales areas. Look back to the panel on the right, Sum of Price in the Values area has its own dropdown menu. Click the dropdown arrow and select Value Field Settings. In the box that opens, change the setting from Sum to Average. Let’s add a second pivot table to run a quick comparison. Go back to Sheet 1 and click inside your table. Then go back to the Insert ribbon and click Pivot Table. In the box that opens, select Existing Worksheet and type Sheet3!H3 into the location box. Click OK.In the new pivot table, drag Price to the Values area and State to the Rows area.Use the Sum of Price dropdown to change the Value Field Settings from Sum to Average and see what difference the three filtered states make. Multiple groups of data can be placed in the panel areas. Drag Order Number into the Values area. Try to place it above Price. Change the Value Field Settings to Count. Now drag City into the Rows area below State. PIVOT CHARTS3973830444500Pivot charts have many of the same rules as pivot tables. You still need to start with your data in a table. You can’t have any blank rows or columns in the data. And two pivot charts can’t overlap. Go back to Sheet 1 and click inside your table. Then go to the Insert ribbon and click on Pivot Chart. If you use the dropdown arrow you will get the choice of making a pivot chart separately or along with a pivot table. Make sure New Worksheet is selected and click Ok. Pivot charts have their own tool ribbons that will appear to the right of the standard ribbons. Go to the Pivot Chart Design ribbon and click Change Chart Type. Select Pie Chart and click OK. In the panel on the right, drag Price to Values and State to Axis. The amount of states makes the chart a little crowded. Use the handle, the little white circle, in the lower right corner of the chart to drag it larger. The chart is still rather crowded, so try adding a filter. Locate State along the right side of the chart and click the dropdown arrow. Click on Value Filters and select Greater Than. In the box that appears, type 1000. 0-5207000The remaining items are not critical to understanding Advanced Features of Excel. Both are types of data control, and can be used to make your data entry a little more accurate, but neither Data Validation nor the Vlookup function are necessary to using Excel effectively. If the pace of the class allows, your teacher may cover these in class.DATA VALIDATIONData Validation makes it possible to prevent erroneous data entry and identify certain types of errors already present in the data. Go to Sheet 1 and select column D by clicking the letter D at the top of the column.Select Data Validation from the Data Tools section of the Data ribbon.39052509525000Set the window that opens to match the picture.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 before that date as come.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. VLOOKUP FUNCTIONVLOOKUP allows you to use the data in your table to reference and retrieve some piece of associated information. We have the state sales were made in already listed in our table, but we also want to know the region these sales occurred in.On Sheet 1, select column H and insert a new column. Call the new column Region. Regions are already set up on Sheet 3 (already named Regions), 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 must already be sorted in ascending order. (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.) Click on 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 Regions to remain unchanged when the formula fills down—remember the difference between absolute and relative references, taught in Intermediate Features of Excel. 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