SPREADSHEET MAGIC – PRACTICE EXERCISES
Example #1:
1)Copy 456 to additional fields in Column A using the Auto-Fill feature.
2)Copy 789 to additional fields in Column B using the Auto-Fill feature.
3)Add additional numbers using Column C using the Auto-Fill feature.
4)Add additional days of the week using Column E using the Auto-Fill feature.
5)Add additional months using Column G using the Auto-Fill feature.
6)Add additional dates (trending) using Column I using the Auto-Fill feature.
7)Reformat column K to “Long Dates” using Number formatting – Long Dates.
8)Re-size Column K using the column with formatting options.
9)Add additional monthly in row 27 – starting with cell E27 using the Auto- Fill feature.
Example #2.a: formatting the tier number into separate columns.
1)The Tier Number column is saves as a formula rather than a number. Adjust the column width of the Tier Number column.
2)Remove all columns except Tier Number and Account Name/Address.
3)Insert two additional empty columns between Tier Number and Account Name/Address.
4)Use Text to Columns function from the Data menu to separate the tier number to three columns
1)Fixed Width – separated between NPA/NXX/Line Number (insert break lines between each)
2)Formatted as Text
3)Finish
5)Format Column C as Custom Formatting “0000” so that the numbers are leading zero-filled.
6)Change headings to “Area Code”, “Exchange”, and “Number.”
7)Resize the columns to fit nicely.
Example #2.b: formatting the name, address, city, state, and zip into separate columns. Before proceeding – notice that there are six spaces between the Name and the Address, and four spaces between the Address and City. A delimiter (semicolon) is needed between each of these.
1)Highlight the Account Information column. Use Find/Replace function to replace six spaces with a semi-colon.
2)Highlight the Account Information column. Use Find/Replace function to replace four spaces with a semi-colon.
3)Use Text to Columns function at the Data menu to separate the Account Information into three columns.
1)Delimited
2)Separated by semi-colon
4)Change headings to “Name”, “Address”, “City ST Zip”.
5)Resize the columns to fit nicely.
Example #3:
- If needed, add a column for your end results.
- Highlight the cell that you want your results to appear in. In this case, we’ll focus on Cell D1.
- From the Formulas menu, select Text – Concatenate.
- At the Text 1 field, select cell A1.
- At the Text 2 field, select cell B1.
- At the Text 3 field, select cell C1.
- Click the OK button.
- Review the information in cell D1. It should be a combination of the information from columns 1, 2, & 3 in row 1.
- Use the auto-fill tool to fill in the rest of column D with results from concatenating each row’s information.
Example #4.a: find the length of the details in a specific cell or column.
1)Highlight cell C2.
2)From the Formulas menu, select Text – LEN.
3)At the Text field, select B2. Click OK to continue. This should return the character length of cell C2.
4)To check the remainder of the cells in the column, use the Auto-Fill option to spread the formula to additional rows in the column.
Example #4.b: change details from mixed or lower case to upper case.
1)Highlight cell D2.
2)From the Formulas menu, select Text – Upper.
3)At the Text field, select B2. This should return the information from B2 to all uppercase.
4)Use the Auto-Fill option to spread the formula to additional rows in the column.
Example #4.c: display only a certain number of characters at the beginning of a field.
1)Highlight cell E2
2)From the Formulas menu, select Text – Left.
3)At the Text field, select D2. At Num_chars, choose 8 characters to be returned. This should return the first few characters of field D2.
4)Use the Auto-Fill option to spread the formula to additional rows in the column.
Example #4.d: display only a certain number of characters at the end of a field.
1)Highlight cell F2
2)From the Formulas menu, select Text – Right.
3)At the Text field, select D2. At Num_chars, select 12 characters to be returned. This should return the last few characters of field D2.
4)Use the Auto-Fill option to spread the formula to additional rows in the column.
Example #5:
1)Format the column headers to adjust the widths.
2)Format columns D, E, and H as currency.
3)Highlight the entire spreadsheet.
4)From the Data tab, select Subtotal.
5)From the Subtotal tool, select the following:
1)At each change in: Account No
2)Use function: Sum
3)Add subtotal to: Total Write-Off, Total Payments, Net Write Off
4)Replace current subtotals
5)Summary below Data
6)Click OK.
Example #6.a: – Formatting & Sorting
1)Highlight the entire spreadsheet and adjust the column widths.
2)Format columns G, H, and I as Currency.
3)Format Column E as Short Date.
4)Left justify all columns.
5)Highlight the entire spreadsheet and click on the Sort & Filter – Custom Sort option.
6)Select the My data has headers checkbox, which will display the headers as your optional dropdowns.
7)Sort by Invoice date. Select Add Level.
8)Next, sort by Cust ID. Select Add Level.
9)Next, sort by Customer Name. Click OK.
Example #6.b: Subtotaling
1)Highlight the entire spreadsheet.
2)From the Data tab, select the Subtotal button.
3)At each change in: Invoice Date
4)Use function: Sum
5)Add subtotal to: Invoice Amount, Taxable Amount, Tax Amount.
6)Click OK.
Example #7.a:
1)Format the column widths
2)Format column F as Short Date.
3)Format columns G, H, I, J, K, L, M, and N as Currency.
4)Highlight the amounts in the Last Payment column (G) and focus your cursor on an empty field below that column.
5)Use the AutoSum tool, and select Average.
6)The average should appear in the field.
7)Use the Auto-Fill tool to spread that formula to columns H, I, J, K, L, M, and N.
Example #7.b:
1)Delete all columns except the Account Name, Account Number, Last Payment Date, Last Payment Amount and Balance 30-59 Days.
2)Focus on cell F2.
3)Select Formulas – Logical – If.
4)From the Function Arguments screen, use Logical_test E2>=200.00; Value_if_true “DISCONNECT”; Value_if_false “OK”
5)Click OK.
6)Use the auto-fill to spread the formula throughout column F.
If the formula doesn’t give you the desired results, you may edit it in the same way. Change the value to compare to $100.
Example #7.c:
1)At the formula bar, change the value from 200 to 100.
2)Use the auto-fill to spread the formula throughout column F.
Example #8.a:
1)Delete all columns except for Bundle, Amount, Description, Element Code, Element Amount, Element Description, and GL Account.
2)Highlight entire spreadsheet and adjust column widths.
3)Update the Amount and Element Amount columns to currency.
4)Update the GL Account Column to increase the number of decimals to 3.
5)The report is already sorted by Bundle code – so no further sorting is needed.
6)Select Data – Subtotals.
7)At each change in Bundle, Use function Sum to Add a subtotal to Element Amount.
8)Click OK.
9)Add an extra row below each Bundle’s subtotal line (this is just for appearance / ease of reading the report).
Example #8.b:
1)Insert a new column between the Element Description and GL Account columns.
2)Label cell G1 “Percentage.”
3)Focus on cell G2.
4)At cell G2, use the following mathematical calculation: =SUM(E2/B2)
5)This formula takes the amount in cell E2 and divides it by the amount in cell B2, thus calculating the percentage of the total coast allocated to each element in the bundle.
6)Post the formula to cell G2.
7)Spread the formula to the other cells in column G
8)Clean up any division errors caused by blank rows.
9)Format column G to Percentage.
Example #8.c:
1)Add two more columns to the right of the current data – one called Proposed %, one called Proposed $.
2)Format Column I as Percentage.
3)Format Column J as Currency.
4)In column J2, create the formula =SUM(B2*I2).
5)This formula will multiply the percentage entered in cell I2 against the total bundle amount stored in cell B2.
6)Spread the formula to additional cells in column J.
7)Enter your Proposed Percentages in column I, and let the system calculate the corresponding amounts in column J.
Example #9.a:
1)Focus on cell B2.
2)Select Data – Data Validation.
3)At Settings, Allow a Whole number between 1 and 5.
4)At Input Message, Enter a Title of “Ratings Scale” and an Input Message of “1=Poor, 2=Below Average, 3=Average, 4=Good, 5=Excellent”
5)At Error Alter, enter a Warning Style with the Title “Wrong Answer” and the Error message “Try a number between 1 & 5”.
6)Click OK.
7)Test the results.
Example #9.b: (manually typing the list)
1)At cell B3, select Data – Data Validation.
2)Allow List.
3)At the Source field, enter Yes, No, Maybe.
4)Click OK.
Example #9.c: (selecting a list from a range of cells)
1)Clear the validation at cell B3.
2)At cell B3, select Data – Data Validation.
3)Allow List.
4)Select the icon to the right of the Source field.
5)Select the details in cells J2-J4.
6)Click OK.
Example #10:
1)Highlight columns A-L and Set Print Area.
2)Select the Page Layout option.
3)At Page – Select Landscape, 1 page wide by 2 pages tall, Legal
4)At Margins – Select Center Vertical & Center Horizontal
5)At Header/Footer – Create a Custom Header titled Aging Report – center – make the font bold, 14-point.
6)At Header/Footer – Create a Custom Footer. At the Left footer, add the File Name. At the Right footer, add Page #1 of #.
7)At the Sheet page – repeat row 1 throughout.
8)Print Preview.