Excel Assessment

Tasks

1. Left justify the text in this cell. 111
2. Right justify the text in this cell. 222
3. Center the text in this cell. 333
4. Change the font color of the text in this cell to blue. 444
5. Change the background color of this cell to green. 555
6. Merge & Center the text in cells B7 & C7, increase the row height to 50, wrap the text. 666
7. Change the font size of the text in this cell to 14 777
8. Format cell B10 with a dollar sign and two decimal places. 888
1234.5678 999
9. Format cell B12 with commas, but no dollar sign, no decimal places. 4995
1234.5678
10. Copy the data in cells K2:K10 and transpose paste below in cells B14:J14.
11. Get rid of the green triangle showing in cell A15.
12. Copy & paste value cell K11 to cell B17.
13. Change the calculation option of this worksheet to Manual.
14. Change the calculation option of this worksheet to Automatic.
15. Freeze the panes of this worksheet, so that rows 1 & 2 are frozen.
16. Create another tab in this workbook, copying the ‘Data’ tab, labeling it ‘Data2’, and then filtering the ‘Data Type’ column to show “ACH” only.
17. In cell N22, use the Sum function to show the result of adding cells D2:14 on the ‘Data’ tab.
18. In cell N23, use the IF function to show the result of adding only the amounts on the ‘Data’ tab if the EFT Type is “wire”.
19. In cell N24, use the Count function to show how many wires and ACH’s are listed on the ‘Data’ tab.
20. In cell N25, use a function to show the average of all amounts listed on the ‘Data’ tab.
21. In cell N26, use a funtion to show the minimum value listed on the ‘Data’ tab.
22. In cell N27, use a funtion to show the maximum value listed on the ‘Data’ tab.
23. If you wanted to find the highest amount on the ‘Data’ tab, would you use the Filter, Sort, or Sum function? Answer
24. Create a pivot table below showing the bank names in columns and the EFT type and total amounts in rows.

Data

Date EFT Type Bank Amount
1/1/21 Wire Bank of America $ 1.00
1/2/21 ACH Wells Fargo $ 2.00
1/3/21 Wire HSBC $ 3.00
1/4/21 ACH PNC $ 4.00
1/5/21 Wire US Bank $ 5.00
1/6/21 ACH Bank of America $ 851,860.00
1/7/21 Wire Wells Fargo $ 1,061,740.00
1/8/21 ACH HSBC $ 1,271,620.00
1/9/21 Wire PNC $ 1,481,500.00
1/10/21 ACH US Bank $ 1,691,380.00
1/11/21 Wire Bank of America $ 1,901,260.00
1/12/21 ACH Wells Fargo $ 2,111,140.00
1/13/21 Wire HSBC $ 2,321,020.00
1/14/21 ACH PNC $ 100.00
1/15/21 Wire US Bank $ 1,000.00
1/16/21 ACH Bank of America $ 10,000.00
1/17/21 Wire Wells Fargo $ 100,000.00
1/18/21 ACH HSBC $ 1,000,000.00
1/19/21 Wire PNC $ 851,860.00
1/20/21 ACH US Bank $ 1,061,740.00
1/21/21 Wire Bank of America $ 1,271,620.00
1/22/21 ACH Wells Fargo $ 1,481,500.00
1/23/21 Wire HSBC $ 1,691,380.00
1/24/21 ACH PNC $ 1,901,260.00
1/25/21 Wire US Bank $ 2,111,140.00
1/26/21 ACH Bank of America $ 2,321,020.00
1/27/21 Wire Wells Fargo $ 100.00
1/28/21 ACH HSBC $ 1,000.00
1/29/21 Wire PNC $ 10,000.00
1/30/21 ACH US Bank $ 100,000.00
1/31/21 Wire Bank of America $ 1,000,000.00
2/1/21 ACH Wells Fargo $ 851,860.00
2/2/21 Wire HSBC $ 1,061,740.00
2/3/21 ACH PNC $ 1,271,620.00
2/4/21 Wire US Bank $ 1,481,500.00
2/5/21 ACH Bank of America $ 1,691,380.00
2/6/21 Wire Wells Fargo $ 1,901,260.00
2/7/21 ACH HSBC $ 2,111,140.00
2/8/21 Wire PNC $ 2,500,000.00
2/9/21 ACH US Bank $ 100.00
2/10/21 Wire Bank of America $ 1,000.00
2/11/21 ACH Wells Fargo $ 10,000.00
2/12/21 Wire HSBC $ 100,000.00
2/13/21 ACH PNC $ 1,000,000.00
2/14/21 Wire US Bank $ 851,860.00
2/15/21 ACH Bank of America $ 1,061,740.00
2/16/21 Wire Wells Fargo $ 1,271,620.00
2/17/21 ACH HSBC $ 1,481,500.00
2/18/21 Wire PNC $ 1,691,380.00
2/19/21 ACH US Bank $ 1,901,260.00
2/20/21 Wire Bank of America $ 2,111,140.00
2/21/21 ACH Wells Fargo $ 2,321,020.00
2/22/21 Wire HSBC $ 100.00
2/23/21 ACH PNC $ 1,000.00
2/24/21 Wire US Bank $ 10,000.00
2/25/21 ACH Bank of America $ 100,000.00
2/26/21 Wire Wells Fargo $ 1,000,000.00
2/27/21 ACH HSBC $ 851,860.00
2/28/21 Wire PNC $ 1,061,740.00
3/1/21 ACH US Bank $ 1,271,620.00
3/2/21 Wire Bank of America $ 1,481,500.00
3/3/21 ACH Wells Fargo $ 1,691,380.00
3/4/21 Wire HSBC $ 1,901,260.00
3/5/21 ACH PNC $ 2,111,140.00
3/6/21 Wire US Bank $ 2,321,020.00
3/7/21 ACH Bank of America $ 100.00
3/8/21 Wire Wells Fargo $ 1,000.00
3/9/21 ACH HSBC $ 10,000.00
3/10/21 Wire PNC $ 100,000.00
3/11/21 ACH US Bank $ 1,000,000.00
3/12/21 Wire Bank of America $ 851,860.00
3/13/21 ACH Wells Fargo $ 1,061,740.00
3/14/21 Wire HSBC $ 1,271,620.00
3/15/21 ACH PNC $ 1,481,500.00
3/16/21 Wire US Bank $ 1,691,380.00
3/17/21 ACH Bank of America $ 1,901,260.00
3/18/21 Wire Wells Fargo $ 2,111,140.00
3/19/21 ACH HSBC $ 2,321,020.00
3/20/21 Wire PNC $ 100.00
3/21/21 ACH US Bank $ 1,000.00
3/22/21 Wire Bank of America $ 10,000.00
3/23/21 ACH Wells Fargo $ 100,000.00
3/24/21 Wire HSBC $ 1,000,000.00
3/25/21 ACH PNC $ 851,860.00
3/26/21 Wire US Bank $ 1,061,740.00
3/27/21 ACH Bank of America $ 1,271,620.00
3/28/21 Wire Wells Fargo $ 1,481,500.00
3/29/21 ACH HSBC $ 1,691,380.00
3/30/21 Wire PNC $ 1,901,260.00
3/31/21 ACH US Bank $ 2,111,140.00
4/1/21 Wire Bank of America $ 2,321,020.00