More Excel Practice

Car Data

    Filtering and Sorting
  • Download the 'Car Data' from above it is already in Excel
  • Filter the data first for the state of California
  • Next apply a second filter for the Make of 'Toyota'
  • Sort the data by Last Name in alphabetical order
  • Create a new worksheet titled "Sorted & Filtered' and copy and paste your sorted and filtered data into this worksheet
  • Subtotals
  • To do subtotals follow the directions below
  • Open a new worksheet and label it 'Subtotals'
  • Return to the 'Car_Data worksheet and remove the filters from above by clicking on the 'Filter' button
  • Sort the data by 'Car Make' in alphabetical order
  • To apply Subtotals click the 'Data' tab and select 'Subtotal'
  • On the 'Subtotal' dialogue box in the 'At each change in:' drop-down box select 'Car Make'
  • In the 'Use function:' drop-down box select 'Count'
  • In the 'Add subtotal to:' box select the 'Car Make' check box and uncheck all other check boxes
  • Check the 'Replace current subtotals' check box
  • Click 'OK'
  • On the left side of the table is an outline of the subtotal groups click the number 2
  • This collapses the data so just the subtotals can be seen
  • Now copy and paste the subtotals by highlighting the subtotals of the Car make and count
  • Press F5 to bring up the 'Go To' menu and then click 'Special'
  • Click the 'Visible cells only' radial button and click 'OK'
  • Right click in the highlighted cells and select 'Copy'
  • Paste the subtotals into the 'Subtotal' spreadsheet
  • Creating a pie chart
  • Create a new worksheet titled 'Sales' in the same Excel workbook
  • Enter the following data into the new worksheet
  • Create a pie chart using the data below
  • Leave the pie chart on the 'Sales' worksheet

                               A         B
                            1  Make      Sales
                            2  Ford      4267
                            3  BMW       2512
                            4  Mazda     1164
                            5  Hyundai   3097
                    
    COUNT and COUNTA Functions and Online Research
  • Create a new worksheet titled 'Count'
  • Enter the following data (cell B7 is intentionally left blank)
  •                             A                   B           C
                            1   Function            Numbers     Text
                            2                       45          Monday
                            3                       76          Tuesday
                            4                       23          Wednesday
                            5                       14          Thursday
                            6                       57          Friday
                            7                                   Saturday
                            8                       66          Sunday
                            9
                            10  COUNT
                            11  COUNTA  
                            
  • Go out onto the internet and search for how to use the 'COUNT' and 'COUNTA' functions. There are many websites that contain information on how to use these functions.
  • Use the Excel function 'COUNT' in cell B10 and C10 to count the number of cells with numeric data
  • Use the Excel function 'COUNTA' in cell B11 and C11 to count the number of nonempty cells
  • Save this Excel spreadsheet as HW14 and turn it in as an attachment for your homework

Save the Excel document as 'hw14.xlsx' in the 'Excel' format. Send hw14.xlsx as an email attachment to your instructor.

In the subject line for your email, include the assignment number, your name and the last 4 digits of your RunnerID in this form:

Homework 14, 1234, Samantha Spade

(Replace Samantha Spade with your name and 1234 with the last four digits of your student ID). The subject line must be exactly in this format (including capital letters and commas) in order for my grading script to download your assignment correctly, so please follow these instructions carefully.

Errors in your homework grade must be worked out with your instructor within one week of grades being posted. You will be asked to show the assignment in the sent email box with the attachment as proof that you submitted your work on time.

Remove your USB drive and take the drive with you when you leave. This should be the last action when you leave the lab every time. The lab computers are wiped clean every night and all data stored on them is deleted. USB drives left plugged into the computer usually find a new owner.