Excel Intermediate - University Of Edinburgh
ExcelIntermediateWorkbookEdition 1January 2019Document Reference: 3808
Excel IntermediateContents1. Absolute and relative cell referencingRelative referencing . 2Absolute referencing . 2Mixed referencing . 3Task 1.1 Relative formulas . 3Task 1.2 When relative formulas are inappropriate . 3Task 1.3 Absolute references. 42. SubtotallingTask 2.1Task 2.2Creating subtotals. 4Nested subtotals . 63. Renaming, managing and linking worksheetsRenaming worksheets . 7Task 3.1 Renaming worksheets . 7Managing multiple worksheets . 7Linking to other worksheets . 7Task 3.2 Referring to cells in other worksheets . 7Referring to cells in other workbooks . 8Task 3.3 Referring to cells in other workbooks . 8Grouping Worksheets. 9Task 3.4 Grouping Worksheets . 94. Naming a RangeTask 4.1 Defining Names . 10Using Names in Formulas. 10Task 4.2 Using Names in Formulas . 105. FunctionsThe Sum Function . 11The Average Function . 11The Round Function . 11Task 5.1 Sum, Average and Round Functions . 11Logical Functions . 12IF . 12Task 5.2 Using IF to return information . 12Nested Functions . 13Nested If . 13Task 5.3 Nesting IF Functions. 13Task 5.4 Working out the grade using the round function . 13Task 5.5 Calculating values with the IF Function. 14Lookup Functions . 14The vlookup function. 15Task 5.6 Using vLookup . 15Task 5.7 vLookup for Exact Matches . 16If you require this document in an alternative format, such as largeprint, please email IS.skills@ed.ac.uk.Copyright IS 2019Permission is granted to any individual or institution to use, copy or redistribute this documentwhole or in part, so long as it is not sold for profit and provided that the above copyright noticeand this permission notice appear in all copies.Where any part of this document is included in another document, due acknowledgement isrequired.
6. FiltersPreparing to filter . 16Filtering Text . 17Task 6.1 Filtering Text . 17Filtering options and custom filters . 17Task 6.2 Filtering Text . 18Filtering Numbers . 18Task 6.3 Filtering Numbers . 18Task 6.4 Removing a filter . 18Filtering Dates . 18Task 6.5 Filtering Dates . 197. Pivot TablesTask 7.1 Create a Pivot Table . 20Task 7.2 Display data for specific items . 20Formatting the data . 21Task 7.3 Calculate the average mark for each subject . 21Grouping data . 21Task 7.4 Grouping data in PivotTables . 21PivotTable Slicers . 22Task 7.5 Creating Slicers . 22Course ConclusionDownloading the course files: . 22Lynda.com playlist: . 224
Excel IntermediateMicrosoft Excel IntermediateThis course builds on your existing Excel knowledge. It is at intermediate level and isintended for those with previous experience in using Excel. Intermediate means differentthings to different people, and from time to time people find parts of the course easier ormore difficult that they expected. The main purpose of the course is to enhance yourExcel knowledge, and to ensure you to leave the course with greater knowledge thanyou had when you arrived.Many people don’t finish the book on the day as it contains many exercises, and not allof the exercises are relevant for everyone. You can carry on with the exercises in yourown time, the location of the workfiles can be found towards the end of this workbook.With increased Excel knowledge you will be able to carry out aspects of your work moreefficiently. You may also find use for Excel at home e.g. for working out your budget orstoring information on CDs, household items etcThe main topics covered today areFormula ReferencingSummarising and SubtotallingManaging WorksheetsLogical and Lookup functionsFilters and Pivot Tables.These topics have been chosen because they are the most frequently requested Exceltopics in the University. If there are other areas you think it would be worth creatingexercises in please let the tutor know.Recap on the uses of ExcelExcel is an electronic spreadsheet application that can be used for storing, organizingand manipulating information. Excel is very flexible as it allows you to choose how youwish to structure the information you enter.You will most likely be aware of many of the uses of Excel, these include: budget forecasting e.g. income and expenditure, annual business reportsinvoicingstoring information e.g. student records, patient records, research datademonstration tool e.g. to show how you arrived at the result of your calculationscientific and statistical analysistracking and decision making toolWe hope you enjoy this course and find it useful. If you have any questions on thecourse the tutors will be happy to answer them.1
1. Absolute and relative cell referencingExcel is frequently used to carry out calculations using formulas. These formulas arecreated using cell references e.g. B3 or D5 rather than actual values, e.g. 2.8 or 5.3.Using cell references allows a change of variable in one cell to be reflected in anydependent cells.Relative referencingBy default, cell references in a formula are relative which means that when the formula iscopied elsewhere the references will adjust, relative to their new position.The use of cell references rather than actual values for formulas provides flexibility andenables:1. Copying of formulas to other cells to quickly repeat a calculation.This saves time when working with complex formulas, and if the calculationneeds to be repeated several times.2. The calculation results will be automatically updated when the source cell valueschange.For example the formula in cell C1 is A1 B1When you copy the formula to another area of thespreadsheet the cell references will change to reflect the newlocation. This is called relative referencing because the celladdress changes relative to the location. You can thenduplicate the formula in other cells.If the value in either A1 or B1 changes then this is immediately reflected in cell C1because cell references rather than values are used.Relative references adjust to location.In another example where a range of cells is added up SUM(B3:B7) copied becomes SUM(C3:C7)Absolute referencingAt times you may need the cell reference in a formula to remain fixed in a specificlocation. For example you may wish to store a currency exchange rate in one cell.When a cell reference in a formula is absolute, the reference does not change whenthe formula is copied.When you multiply a particular value by the exchange rate you want the exchange rateto remain constant and ensure your formula always refers to the same cell.To fix cell references andmake them absolute place a sign in front of the row andcolumn co-ordinates e.g. tomultiply the value in B4 by theexchange rate in G4, firstchange G4 to G 4, then copythe formula down the column B4* G 4Absolute references remain constant regardless of location. The sign is usede.g. G 4 to make cells absolute.2
Excel IntermediateMixed referencingA mixed reference is one where either the column letter or the row number is fixed, forexample: A1 (if the formula is copied, the column letter will not change) orA 1 (if the formula is copied, the row number will not change)Task 1.1 Relative formulasMost formulas in Excel will be relative resulting in cell references changing when theformulas are copied to other locations.1.Open the file Absolute12.Click in cell B93.Click the down arrow next to AutoSum in the Editing group on theHome tab, select Sum and press Enter4.Click on B9 again, and drag the small square handle (the fill handle) at the bottomright corner of the cell along to E9. (This copies the formula fromB9 to E9)5.Click in the cell F66.Select the Sum commandand click Enter7.Click on F6 again anddrag the fill handle to F9.(This will copy the formulafrom F6 to F9). Save andclose the workbook.This is copying relative cell references. We will now look at using absolute referencing.Task 1.2When relative formulas are inappropriateSometimes a value in a formula needs to be located in a specific cell, row or column i.e.the formula must include an absolute cell reference. This exercise illustrates whathappens when the formula doesn’t work as required.1.Open the file Absolute22.Click in C11 (we want to calculate theproduct’s surcharge)3.Type B11*B5 and press Enter4.Click in C11 again (we now want to fillthe other cells)5.Move the mouse pointer to the fill handleat the lower right cornerof the cell and dragdown to cell C22The results are not what we require.As we filled the cells in column C the value we multiplied by changed from 12% (thevalue in B5) to zero (the value in B6 which is empty) and so on down column B.We’ll correct this in the next exercise when we make the cell reference to B5 absolute.3
Task 1.3Absolute referencesIn this exercise we need the value we are multiplying by to remain fixed when theformula is copied down a column. i.e. we need to make the cell reference absolute.1.Using the file Absolute2 click in cell C11. Move to the formula bar at the top of thescreen, and then click within the B5 cell reference in theformula in the formula bar.2.Press F4 (the key to change referencesto absolute) several times. Observe thatthe cell reference changes from B 5 toB 5 to B5 and then B5 each time youpress F43.Press F4 until B5 appears as B 5 andpress enter4.Click in C11 and press enter. Now clickin C11 again and move the mousepointer to the fill handle at the bottomright of C11, and drag the handle down to C22 to fill the formulas down the column.Save and close the workbook. Note: When copying a formula, you can make it relative or absolute. Relative references adjust to location SUM(B3:B7) copied becomes SUM(C3:C7)Absolute references remain fixed regardless of location. Use the sign e.g. A 12. SubtotallingWe often need to calculate the grand total of values stored in a list. In addition, we mayneed to subdivide the data and calculate subtotals. Fortunately, Excel provides a subtotalfunction which can be used to carry out any subtotal calculations.When using the subtotalling function the data needs to be ‘tidy’ e.g: Column headings must be uniqueThere should be no blank rows or columns as Excel stops sorting when it meets ablank row or columnThe data must be sorted in the column to be subtotalledWhen using subtotalling Excel applies outlining to group data. You can use this outliningfeature to show and hide details using the expand and collapse buttons.Task 2.1 Creating subtotalsWhen subtotalling Excel adds a new row containing the subtotal at each change in thedata. It also applies outlining to the list.1.Open the file ProductSummarisingWe are going to create subtotals so first we will sort the data2.Click on any cell in the Store column3.On the Home tab of the ribbon, click on Sort & Filter in the Editing group andselect Sort A to ZWe now want to add subtotals for each store4
Excel Intermediate4.Click on the Data tab, then click on Subtotal in the Outline group,to display the Subtotal dialog box5.Click on the drop arrow for At each change in and clickon Store.6.Ensure the other settings are as shown in the screenshot and click OKAn outline will appear in the worksheet and the product costs will besubtotalled at each change in store.7.Scroll to the bottom of the data to see that Excel has also calculatedthe grand total of the costs.Subtotalling syntaxWhen you click on a subtotal within the data the formula bar shows the formulagenerated by subtotal function. In the case of a subtotal sum the formula is The format of the formula is SUBTOTAL(function num, ref1, ref2, .)The function num specifies the type of function to use in calculating subtotalswithin a list. The function number for the subtotal sum is 9. Popular functionnumbers include average, count and max as listed in the following table:FunctionNumber EVSUMCOUNT counts cells containing numbersCOUNTA counts non empty cellsThe PRODUCT function multiplies all thenumbers given as arguments and returns theproduct. e.g. if cells A1 and A2 containnumbers, you can use the formula PRODUCT(A1, A2)STDEV The standard deviation is a measureof how widely values are dispersed from theaverage valueThe SUBTOTAL function can be used directly. The first parameter in the sum subtotalfunction is 9. i.e. using 9 in the formula tells Excel to sum the figures, using 2 tells Excelto count the figures. e.g.Subtotal Average SUBTOTAL(1, F2:F8)Subtotal of Count SUBTOTAL(2,F2:F8)5
Task 2.2 Nested subtotalsYou can create another level of subtotalling in addition to subtotals of the cost. Creatinganother level produces nested subtotals. To create nested subtotals you first sort thedata again on the main grouping, and then on the secondary grouping.1.Continue working with the file ProductSummarising and ensure your cursor isplaced within the data table.2.Choose Sort & Filter on theHome tab. Select CustomSort3.Click the Add Level button4.Click the drop down arrowfor Then by, choose Product, and click OK5.Click OK to the message “This removesthe subtotals and sorts again”.6.Now click the Data tab, and choose Subtotal to display the Subtotal dialogue box7.Click the drop down arrow for At each change in, choose Store and click OK (thesettings should be retained from the previous exercise)We will now add a secondsubtotal8.Click on the Subtotalbutton again. This timeAt each change inselect Product9.This time remove the ticknext to Replace currentsubtotals (if it is ticked)and click OKNotice that both the Store andProduct subgroups aresubtotalled.10.Save and close the workbook.Tip: The subtotal function is used to sum or average sections of dataIt can be used for creating a subtotal:Sum, average, maximum or minimumExamples of use: student exam marks, sales figures, expenditure records Note: With Subtotalling and Filtering the data needs to be ‘tidy’ i.e. 6Column headings must be uniqueData must be sortedNo blank rows or columns
Excel Intermediate3. Renaming, managing and linking worksheetsRenaming worksheetsBy default, Excel names new worksheets consecutively: Sheet1, Sheet2 etc. In order tokeep track of, and add meaning to information in your workbooks you can rename them.Task 3.1 Renaming worksheetsTo give your sheet a different name to the default, double click the sheet tab, or rightclick the sheet tab and choose rename.1.Open the file Linking2.Activate the Sheet2 tab and rename it (by double clicking or right clickingand choosing rename) as Year20123.Repeat this procedure to rename Sheet3 as Year2013 and Sheet4 asYear2014Managing multiple worksheetsIn Excel you can insert, move and copy worksheets from one workbook to another. Youcan also refer to cells and ranges in other worksheets, or other workbooks.Inserting worksheetsTo insert a worksheet right-click the tab of the worksheet before which you want toinsert the new worksheet, choose insert and select worksheet from the General tab.Moving and copying worksheetsYou can move a worksheet by dragging it to a new location. To copy a worksheet holddown the Ctrl key when dragging the worksheet to a new location.Linking to other worksheetsTo link worksheets use references to cells in other worksheets to get data into your mainworksheet. e.g. you might keep a summary worksheet and link to this from otherworkshe
Excel Intermediate 1 Microsoft Excel Intermediate This course builds on your existing Excel knowledge. It is at intermediate level and is intended for those with previous experience in using Excel. Intermediate means different things to different people, and from time to time people find parts of the course easier or
Excel 5.0 Excel 5.0 1993 Excel 5.0 1993 Excel 7.0 Excel 95 1995 Excel 8.0 Excel 97 1997 Excel 98 1998 Excel 9.0 Excel 2000 1999 Excel 2001 2000 Excel 10.0 Excel XP 2001 Excel v.X 2001 Excel 11.0 Excel 2003 2003 Excel 2004 2004 2.1.2 Worksheet Document Definition: Worksheet Document A worksheet document consists of a single sheet only.
Edinburgh 2020 : The Edinburgh Tourism Strategy Foreword The past twenty years have seen a remarkable transformation in the scale and nature of Edinburgh's tourism industry and its impact on the economic, social and cultural life of the city. Once a highly seasonal destination, dominated by leisure tourism, Edinburgh now has a rich, .
Power Map Power Map provides a new perspective for your data by plotting geocoded data onto a three-dimensional view of the earth and optionally showing changes to that data over time. To use Power Map, you import raw data into a Microsoft Excel 2013 workbook, add the data to an Excel data model, and enhance the data in the data model if necessary.File Size: 1MBPage Count: 17Explore furtherGetting an excel list of all Azure Virtual machinesdbaharrison.blogspot.comDownload Azure Devops Board To Excelwww.how-use-excel.comGetting an excel list of all Azure Virtual machines .www.firstcloud.ioGetting an excel list of all Azure Virtual machines .laptrinhx.comRunning Excel On Azurewww.how-use-excel.comRecommended to you based on what's popular Feedback
The 12 major festivals in the collaboration are covered in more depth within Section 7 of this guide. They are: Summer festivals (June–September) Edinburgh International Film Festival Edinburgh Jazz & Blues Festival Edinburgh Art Festival Edinburgh Festival Fringe The Royal Edinbu
Click the Excel 2019 app to run the Excel app and display the Excel start screen Click the Blank workbook thumbnail on the Excel start screen to create a blank Excel workbook in the Excel window-7-Starting and Using Excel (3 o
While Excel 2010 documents share a file extension with Excel 2007 (*.pptx), the Excel 2010 file is a unique file type. Excel 2007 documents will open in “Compatibility mode” and will not have certain Excel 2010 tools available unless re-saved as an Excel 2010 document. Saving a Excel
The University of Edinburgh 13-15 South College Street Edinburgh EH8 9AA E: ddi@ed.ac.uk T: 44 (0)131 651 4299 ddi.ac.uk Development Opportunities in Fife Authors: Dr Fumi Kitagawa is a Senior Lecturer in Entrepreneurship and Innovation at the University of Edinburgh Business School, and has a PhD in Urban and Regional Studies. Her research has
Coronavirus (COVID-19) risk assessment 11 Hazard Risk rating Control measures Additional controls Residual risk Persons at risk Non-essential contractors were stood down (where the service was not required at this time) to reduce possible transmission of the virus. All contractors that are providing a service are contacted on a daily basis to ensure they adhere to hygiene requirements .
