1Manipulating Data in MS ExcelSorting DataWhen manipulating data within Excel, it is common to want to present a list of datain: Alphabetical order Numerical Order Sorted by Month, or day of the weekTo sort a list of data:1. Select one of the cells in the table of data that you want to sort2. Click on … Continue reading “Manipulating Data in MS Excel | My Assignment Tutor”
1Manipulating Data in MS ExcelSorting DataWhen manipulating data within Excel, it is common to want to present a list of datain: Alphabetical order Numerical Order Sorted by Month, or day of the weekTo sort a list of data:1. Select one of the cells in the table of data that you want to sort2. Click on the Data menu, and select the Sort option in the menu ribbonThis will present you with the following dialog box:3. Select the column that you wish the data to be sorted by clicking on the‘Sort by’ drop down menu.24. Select the criteria that you wish the columns to be sorted on, byclicking on the ‘Sort On’ drop down menu (Values is selected bydefault, further options for the type of data can be accessed from thismenu).5. Select whether you would like the list sorted in ascending ordescending order, by clicking on the ‘Order’ drop down menu.6. Once the sort criteria have been selected, press OK, to sort the data.Any columns in directly adjacent columns will also be changed tofollow the sorted column.FiltersFiltering is a quick and easy way to find and work with a subset of data in a list. Afiltered list displays only the rows that meet the criteria you specify for a column.Microsoft Excel provides two commands for filtering lists: Filter, which includes filter by selection, for simple criteria Advanced Filter, for more complex criteriaFiltering does not rearrange a list. Filtering temporarily hides rows you do not wantdisplayed. When Excel filters rows, you can edit, format, chart, and print your listsubset without rearranging or moving it.Display a subset of rows in a list by using filtersYou can apply filters to only one list on a worksheet at a time. To add a filter:1. Click a cell in the list you want to filter.2. Click on the Data menu, and select the Filter option in the menu ribbon.3. To display only the rows that contain a specific value, uncheck the (Select All)tickbox, and click the arrow in a tickbox that contains the data you want todisplay. Exercise 11. You have been provided with a spreadsheet called ‘Customers.xls’ in whicheach row represents the details of a delivery for a company.2. Using the data contained within ‘Customers.xls’, sort the data in alphabeticalorder of the cities.3. Now sort the data by date and provide a secondary sort, in alphabetical orderof the postcode.4. Using the same data, sort in descending order of weight to find the largestcustomer delivery. What strikes you as odd about these top few deliveries? 3Tip: you can select as many values as you wish by clicking on the tickboxadjacent to the value you wish to select.4. Click the value.5. To apply an additional condition based on a value in another column, repeatsteps 3 and 4 in the other column. (note that if data is subsequently added to anadjacent blank column after the filter has been applied, you will have to turnthe filter off, and back on again (by repeating step 2 twice))To filter the list by other criteria, ensure (Select All) is ticked, and click on oneof the options shown under Number Filters.4You will notice from the menu above that the more common filter options arepre-defined. You can also use the Custom Filter to display rows that meetmore than one condition for a column, such as rows that contain values withina number of specific ranges (such as values between 2,000 and 3000 ORbetween 3500 and 4000).Using Conditional functionsA formula in Excel makes calculations on values in your worksheet. Usually,formulas perform calculations on all the values in a given range. However, what ifyou want Excel to change your formula only if a certain condition is true, or what ifyou want to include only the values that meet certain conditions in the calculation?When you want formulas to perform conditional tests, you can use conditionalformulas in Excel.IFThe IF function returns one value if a condition you specify evaluates to TRUE andanother value if it evaluates to FALSE. You would use IF to conduct conditional testson values and formulas. An example of the use of an IF function is provided in thenext section.The syntax for using the IF function is as follows:IF(logical_test,value_if_true,value_if_false)Where, Logical_test is any value or expression that can be evaluated to TRUE orFALSE. For example, A10=100 is a logical expression; if the value in cellA10 is equal to 100, the expression evaluates to TRUE. Otherwise, theexpression evaluates to FALSE. This argument can use any comparisoncalculator operator (such as =, etc). Value_if_true is the value that is returned if logical_test is TRUE. Forexample, if this argument is the text string “Within budget” and thelogical_test argument evaluates to TRUE, then the IF function displays the Exercise 21. Using the data contained within ‘Customers.xls’, create a filter that showsonly the carrier NUNR in the Ship Method column.2. Using the same data, create a filter (using the number filters) that showsdeliveries by customer numbers between 3375 and 3411.3. Experiment with options in the custom filter list. 5text “Within budget”. If logical_test is TRUE and value_if_true is blank, thisargument returns 0 (zero). To display the word TRUE, use the logical valueTRUE for this argument. Value_if_true can be another formula. Value_if_false is the value that is returned if logical_test is FALSE. Forexample, if this argument is the text string “Over budget” and the logical_testargument evaluates to FALSE, then the IF function displays the text “Overbudget”. If logical_test is FALSE and value_if_false is omitted, (that is, aftervalue_if_true, there is no comma), then the logical value FALSE is returned. Iflogical_test is FALSE and value_if_false is blank (that is, after value_if_true,there is a comma followed by the closing parenthesis), then the value 0 (zero)is returned. Value_if_false can be another formula.Using the IF function – an ExampleOn a budget sheet, imagine a cell (for example A10) contains a formula to calculatethe current budget. If the result of the formula in A10 is less than or equal to 100, thenthe following function displays “Within budget”. Otherwise, the function displays“Over budget”. The following function could be used to create these displays:IF(A1050,”Pass”,”Fail”).If the figure in cell A1 is more then 50 list it as a pass, if not, list it as a fail.You could make this more complex however. We could have more options. If the passmark is 51, there could also be a merit mark of 66 or more, and a distinction mark of76 or more. For this situation we would create a nested IF clause as follows:=IF(A1>75,”Distinction”,IF(A1>65,”Merit”,IF(A1>50,”Pass”,”Fail”)))In English this reads as follows: If the number in cell A1 is more than 75, enter theword “Distinction”, if it is not more than seventy five ask if it is more than 65. If it isthen write the word “Merit”. If it is not more than 65, ask if it is more than 50. If it isthen write “Pass”, if it is not, write “Fail”.Do remember to write your nested IF the right way round. Think what would happenif you had written it the other way round, starting at the bottom.If A1 is more than 50 write “Pass”, if not you are only left with one option, that it isless than 50, which is a fail. This way you can’t get in the merit and distinction. Youmust start at the top and work down to be able to cover all your options.Pivot TablesA PivotTable report is an interactive table that you can use to quickly summarizelarge amounts of data. You can rotate its rows and columns to see different summariesof the source data, filter the data by displaying different pages, or display the detailsfor areas of interest.When would you use a PivotTable report?Use a PivotTable report when you want to compare related totals, especially whenyou have a long list of figures to summarize and you want to compare several factsabout each figure. Use PivotTable reports when you want Microsoft Excel to do thesorting, sub-totalling, and totalling for you. To create a PivotTable report, use thePivotTable and PivotChart Wizard as a guide to locate and specify the source data youwant to analyse and to create the report framework. You can then use the PivotTabletoolbar to arrange the data within that framework.7Creating a Simple PivotTableTo show how a pivot table might be created, a simple example will now be explained.First insert some data in the cells A1:D19 of a spreadsheet, as follows: WhoWeekWhatAmount(£)Dave1Train20Carol3Car15Bill2Train25Otto5Air120Helga3Car25Helga1Car30Otto1Taxi35Dave3Taxi30Dave5Train21Carol3Air320Carol6Train28Bill4Car22Helga6Train45Bill4Taxi33Helga7Taxi38Dave4Air168Dave3Train67Carol6Train55 Now choose any cell in this table and choose Pivot Table from the Insert menuribbon.Excel asks for the data source and suggests the table of data that you have inputted(Excel List or database).8You can display a PivotTable report in indented format, to view all the summaryfigures of the same type in one column.If you expect to add data in the future, set the data range to include as many rows asyou think you will ever need. Rather than A1:D19, you may want to specify$A$1:$D$500.Pressing OK results in a new sheet being created for the PivotTable. You now definethe format of the report using the PivotTable Tools, which is shown below.In the PivotTable Field List, click on each of the tick boxes, you will notice that theRow Labels area at the bottom becomes populated with the Who and What headings.The ∑ Values (Summation Values) area becomes populated with Sum of Amountand Sum of Week headings.Drag the header Sum of Week from the ∑ Values area into the Row Labels area(Leave the Column area as it is for now).Now you have your table, and it looks very much like a sorted version of the originaldata list, except for the automatic subtotals, as can be seen on the following page.9Now comes the really useful part! ……Grab the What Row Label in the PivotTable Field List and drag it up to the top of thelist.10When you drop it here, the table re-sorts and re-sums; you have a table sorted bytransport mode by person instead. Now drag the Week header to the top of the list andyou have a weekly report, as shown below:Clicking the Row Labels drop down button gives options of showing/hiding specificdata and removing sub-totalling for this column. Other options are available, amongthem Hide and Show Detail for reading totals only.11Using the same list, we can also present the data in an alternative way. Selecting anyitem in the original data list and choosing Pivot Table from the Insert menu ribbonagain, we can produce a new Pivot Table. This time, using the PivotTable Field List,drag What into the Column Labels area and untick Week in the fields to add to reportsection.This produces a table that is -among other things- very useful for graphing.It is important to note when using pivot tables, that they will not be automaticallyupdated when the source data is changed. You need to do this manually by selectingOptions->Data->Refresh. Exercise 41. Using the data contained within “Customers.xls”, create a PivotTablethat provides weight delivered data by country.2. Which country represents the largest sales and which the smallestsales.3. Change the PivotTable so that the weight delivered is ordered byShip Method and by Country.4. What percentage of total weight does the carrier NUNR deliver incountry GB. 12The Lookup FunctionThe vlookup function allow you to refer to a cell somewhere else on a worksheet withease – and can often allow you to dispense with complicated conditional functions.The syntax for the function is as follows:VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)Lookup_value is the value to be found in the first column of the array.Lookup_value can be a value, a reference, or a text string.Table_array is the table of information in which data is looked up. Use a referenceto a range or a range name.Col_index_num is the column number in table of data from which the matchingvalue must be returned. A col_index_num of 1 returns the value in the first column intable; a col_index_num of 2 returns the value in the second column in table, and soon.range_lookup should always be set to False so that the function returns an exact match Exercise 51. The spreadsheet you have been provided with, called “DealerHubdata.xls”, contains two worksheets called ‘Dealer’ and ‘Hub’. Each rowin the ‘Dealer’ worksheet corresponds to a delivery2. On the ‘Dealer’ worksheet, use the VLOOKUP function on the ‘HubID’ column to add ‘Hub Code’, ‘Hub Name’ and ‘Hub Location’ fromthe ‘Hub’ worksheet3. Use a pivot table to calculate the total quantity delivered by dealer. Sortthe pivot table into descending order to find the which are the largestdealers4. What percentage of the total delivered quantity is the largest dealer?5. How many dealers account for approximately 80% of the volume.6. Which hub supplied the largest quantity to the largest dealer?7. Out of all the hub deliveries, which hub supplied the least quantity to alldealers8. How many deliveries are there for each dealer and what is the averagedelivery size 13Using Charts and GraphsCreating Charts1. Select the information you would like to be included on the chart. Along withjust the data, you should also select your row and column headings so thatthey will be automatically incorporated into the chart.2. Now choose one of the chart types from the Charts section of the Insert menuribbon.3. Select the type of chart you want to create.4. The chart will be created, and displayed as an object within the sheet thatcontains the source data.Making Changes to a Chart1. Right click on what you would like to change. You will see a choice forFormat… For example, if you right click on the legend, you will see a choicefor Format Legend.2. Choose this Format choice and make any changes necessary.Changing the Type of Chart1. Select the entire chart by clicking on the white background of the chart.2. Right click the chart and choose Change Chart Type.3. Click on the type of chart you would like to use and click Ok.Changing Labels and Titles1. To delete a legend or title, select the item and press the [Delete] key.2. To change the wording of a Legend or Title, select the item you wish tochange and then left click into it. A cursor will appear and you can type anychanges or use the backspace/delete keys to make corrections. When you havefinished changing the text, click anywhere else on the chart to exit the editmode.Using Graphics as Your Bars141. Create your chart, using one of the column or bar choices (2-D usually worksbest)2. Click on the bars you would like to change.3. Right click on the bars and choose Format Data Series.4. Click on the Fill Effects button.5. Here, you can choose to use a pattern, a texture, or a picture. If using a picture,click on that option.6. Click on the File.. button under the “Insert from:” heading and find the pictureyou would like to use and click on Insert.7. Choose whether you want the picture stretched or stacked from the options,and click on Close.Reordering Your Series You may find that with some charts, it is easier to see a trend if your data is ina certain order. For example, from smallest amount to largest.1. Right click on any series and choose Select Data.2. Click on the name of the series you would like to move and the Move Up andMove Down buttons to reposition it.3. Click on Ok when finished.Data Tables A data table actually shows the data for your chart below the chart itself. Thisis handy if you want to have the actual numbers available, but do not want tohave to refer back to the spreadsheet (especially handy if the chart is on a newpage).1. Create your chart as usual.2. Click on the Layout menu, and select the Data Table option in the Labelssection of the menu ribbon.3. Select an option from the list provided.Callouts Callouts are generally used to make notes on your charts.1. Click on the Insert menu, and select the Shapes option in the Illustrationssection of the menu ribbon.2. Click on Callouts and the type of Callout you would like to use.3. Click and drag where you would like the callout to appear.4. Type in the note you would like to add, then click back on the chart.Trend Lines Trendlines are used when you have many data points and you want to fit a lineto the points.1. Create your chart as usual (usually scatterplot, column, bar, or line).152. Right click on one of the series on the chart.3. Choose Add Trendline.4. Choose the type of line that would best fit your data and click on Okay.Error Bars Error bars are used when data is potentially flawed or estimated. The bars canshow the standard deviation, the standard error, a fixed value, or fixedpercentage.1. Create your chart as usual.2. Right click on the data series.3. Click on the Layout menu, and select the Error Bars option in the Analysissection of the menu ribbon.4. Choose the type of error bars you would like and the Error amount (ifrequired).5. Click on Ok.A Secondary Y-Axis You may find that when you have two data series that are radically different, itmay be difficult to see both of the series.1. Right click on the series you would like to move to a secondary axis.2. Choose Format Data Series.3. Select the Secondary Axis option.Missing Data Points If you have a time scale axis with data points missing, Excel will usually justleave a gap, which may or may not be what you want. You can change thissetting so that the points will be skipped, plotted as zero, or interpolated.1. Click on the chart you would like to change.2. Right click and choose Select Data.3. Click on the Hidden and Empty Cells button.4. Select how you would like missing data dealt with.Printing Your Chart1. The first thing to do when printing is to select what you would like to print.2. If you are printing just your chart, click on it to select it. If you want to printout the spreadsheet also, highlight all of the cells that you have data in and thecells behind the chart.3. Choose Page Layout->Page Setup->Print Area->Set Print Area. You willnotice that a dashed line appears around your selection.4. Now click on ->Print->Print to print your document.5. If you would like to change the area to print, choose Page Layout->PageSetup->Print Area->Clear Print Area and start over with step #2.16Print Preview Choices1. When you are looking at a print preview, you will see some choices that mayhelp you in working with your document. Click on ->Print->PrintPreview.2. Choose Page Setup from the button bar above the preview. Page- This tab gives you choices for the layout of your page. You can changethe orientation of the page and the scaling (how much of the page yourselection fills). Margins- If you need to change the size of your margins, this is where you doit. You can also center your selection on the page here. Header/Footer- This is where you can create a header or footer that will bedisplayed on each page. There are choices for typical headers and footers ifyou click on the box next to Header: or Footer:. To create your own, click onCustom Header or Custom Footer. Sheet- Here, you can choose what you would like to be printed on the page.Gridlines are not included by default, but if you have a complex spreadsheet, itmay be easier to read with them printed out. You can also choose the order toprint out your selections if more than one fits on a page.Placing an Excel Chart in Word or PowerPoint1. Open the Excel file that has the chart you would like to insert.2. Click on the chart and choose Home->Copy.3. Open the Word or PowerPoint document you would like to insert it into.4. Place the cursor where you would like the chart to appear.5. Choose Home->Paste->Paste Special.6. Click on Microsoft Excel Chart Object.7. If you would like the chart linked to the file it came from (any changes in theExcel file will be reflected in the Word document) click on Paste Link.8. Click on Ok.What Type of Chart Should I Use?Column Charts Excellent when you need to compare categories. Good at showing changes over time, with time as the x-axis.Bar Charts Similar to column charts, but the bars extend horizontally instead of vertically.17 Avoid using a time-based scale as the category, studies show that this isgenerally confusing for people. Use a column chart instead when working withtimes or dates.Cylinder, Cone, And Pyramid Charts These are just like column charts, but use cones, cylinders, or pyramidsinstead of rectangles. These are 3-D, so they should be used when you have three axes to plotagainst (i.e. sales made for each month for each salesman).Line Charts Best show changes in a series over categories or time. Suggests that the data is continuous, so if you were to measure a pointbetween two points, the line would be correct.Pie Charts Best for showing percentages of the whole. Use the Exploded Pie choice to emphasize a particular category.Scatter Charts Used to show correlations between two sets of values, one on the x-axis andone on the y-axis. Generally not used with time, use a line chart instead.Area Charts Best for when you want to show a change over time but emphasize the total ofall the series combined. This is like the combination of a line chart and a piechart.18Other Charts There are around 70 chart types contained within the Excel library. Withunlimited flexibility in terms of formatting, the way that information can berepresented graphically is limited only by your imagination.Exercise 61. You have been provided with a spreadsheet file called ‘Regression analysisdepots.xls’.2. Using the data contained within the worksheet Paris Cost Function, create ascatter graph of throughput against fixed cost and show a trendline withformula and correlation coefficient. Create graphs with trendlines forthroughput against space, variable cost, people and stock quantity. Trydifferent curves to find which gives the closest correlation3. Using the data contained within the worksheet Cross WarehouseRegression, create graphs of units against cost and people. Apply theregression formula to estimate the cost and people for unit throughputs of500 and 12000
