Prove It Excel Tutorial
Author: Ashley Jaime Priyanto
if you’re on the desktop, you can slow down the video by going to the video settings
Usually in the test there will only be 30 of these questions, a combo of easy, medium and hard. They are not necessarily in the order shown in the above video. Many recruiters will allow you to take the test multiple times, so don’t stress out!
This might be handy, search the question in the video above.
0.03 Open Workbook
0.08 Insert a row above the selected row (between 1 and 2) Select the lower of 2 rows
Home – Insert
0.12 Change the font style of the cell to bold
Home – Select the Bold icon
0.17 Change the selected cell to 20pt
Home – 20pt drop down
0.21 Format selected range to US currency
Home – $ symbol
0.26 In the current cell, enter the formula to calculate total revenue
Formulas – Autosum
0.33 Copy the selected cell
Home – paste button
0.37 Paste selected into cell F5
Home – paste button
0.42 Save the workbook
File – Save
0.45 Change the alignment to right
Home menu – right align button
0.49 Select A3-F3
Click and drag using the small + in the bottom right corner of the cell.
0.57 Create a border around the cells using all borders or outline and inside.
Select the rows, home – borders icon (looks like a square window)
1.01 Rename sheet 1 as revenue
Double click the name of the tab and type.
1.14 Insert a column to the left of column F
Select column F, Home- insert
1.21 In the current cell, use the average function. Calculate the ave quarterly revenue for fiscal year.
Select – Autosum – average
1.30 Change the width of the column F so contents fit.
Format – autofit
1.37 Create a chart form the selected cells
Select -insert – first option in drop down
1.43 Change the chart style to 8
design – 8th option in the list
1.50 Modify the format of the cell to auto wrap
Home – choose the button next to the format on the right at the bottom
1.55 Insert a new worksheet
Click on the circle next to the tabs at the bottom of the worksheet
1.57 Merge and centre new cells
Home – button next to align left
2.01 Sort by last name ascending
Home – A-Z button on far right
2.06 Change the page margins to wide
Page layout – margins – wide
2.11 Change orientation to landscape
Page layout – orientation – landscape
2.17 Footer P1 to current worksheet
Insert – text – header and footer –footer- P1
2.29 Spell check
Review – spelling
2.38 Center the selected w/sheet hoz and vert
Page layout – margins – narrow in the popup check options hoz and vert – ok
2.48 Create report – Quarterly salary x territory
Insert – table – pivot tables – ok
choose territory in fields – quarterly sales
3.00 Set up worksheets so they each print on one page
Page layout – choose the tiny icon under print titles – fit to- 1 page
3.07 Print the worksheets without print preview
File – print
3.13 Hide col 1
Home – select 1 -format button – visibility – hide and unhide
3.20 Freeze top row of w/sheet
View – freeze panes – freeze top view
3.24 in C2 – formula to combine A2 blah blah blah
With catalog ID selected in formula field type =CONCATENATE(A2, B2)
3.46 In G2 create formula that puts YES if cell F2 is less than 25% of H2 of NO if cond is not met
Formulas – fx (insert fn) IF 1) F2<(.25*H2) 2) YES 3) NO
4.20 Select G2 and C2 without selecting D2-F2
Use the mouse and control button
4.28 Copy selected cells and paste actual values
Home – two boxes icon to the right of Paste on the far left of the pane
4.38 Use fill handle to fill empty cells G3:G11
Click fill handle (the + in the corner of the cell selected) hold down the mouse and drag to release
4.47 Format selected column (G) to highlight the cells with light red fill and dark red text if cell = yes
Home – conditional formatting – select highlight cell rules – equal to.., yes and click OK
5.03 Sort cells by time to reorder? In A2 and quantity to reorder by smallest to largest
Click the sort button (AZ) and filter -custom sort, sort by time to reorder Z to A add level then by qty to reorder A- Z
5.24 Change the table style to table style Light 2
Home- format as table button – table style – light 2 – ok
5.30 using the pivot table add task name as column label
Right click task name and add to column labels.
5.39 Modify pivot table so vacation entries are removed
Click task name drop down arrow, click vacation
5.46 Creare pivot chart that displays project name and time in hours
Insert – pivot chart – ok. Click on the project name button and the time in hours checkbox.
6.00 Using macro recorder, make a macro that will type 800 555 5555 in the cell, macro name Phone, shortcut ctrl E
– View – Macros – record macro. Type Phone, add the shortcut beneath it, then type the number into the cell and then go macros – stop recording
6.46 Run the macro ‘Phone’
View – click the top half of the macros button, click RUN
6.55 Turn on autofilter for selected cells
Click Sort and Fiter (A/Z icon) the FILTER
6.59 Apply filter to TASK NAME (D) so only design tasks are shown.
Click on Column drop down arrow – select all – design – ok
7.07 Divide F2 by F76 use relative reference for cell F2and mixed cell ref to refer to row for cell F76
In the cell G2 type =F2/F$76 and enter.
7.22 Split column using column delimiter
Click DATA tab next to columns button – uncheck TAB box – check OTHER then : – next – finish
7.44 In cell F2 create a vlookup formula
Formulas – lookup and reference – vlookup – type E2 into the lookup_value field. Click on table array and type K:L – COL_INDEX_NUM and type 2. – Range_Lookup field – false -ok.
8.12 In G2 create formula that will place no. of occasions B2 is in column B
Click on the fx button – category dropdown, statistical, COUNTIF, ok. 8:8 in range tab- criteria field: B2.
8.37Create custom list cells in A2:A12
File- options – advanced – edit custom list – import
8.48 Select worksheet option 1,2,3
Hold down shift and click option 3 tab.
8.59 Without changing the view insert a page break above row 21
Click the row 21 – page layout- breaks drop down arrow – insert page break.
9.08 Set print area to contain selected cells A1:C7
With area selected in worksheet, go to page layout – print area – set print area
This is SO great, thank you.
Learned a lot!
Thank you for uploading this, you are the real MVP.
It goes to fast …no time to read the question
Thank you for taking the time to upload this clip. Was simply searching for what the test would consider the default method to arrive at the correct response but after a few hours of pausing the player and reading the step by step instructions, I actually learned how to create more complex macros and PT’s. Knowledge is power and you have given “us” some of yours. Appreciate that and may good fortune follow you.
fucking no sound
Which version of Excel is this???
Ashley Jaime Priyanto you are an Amazing Person, an Angel sent from above..for opening up your mind and heart to share this Prove It Excel Tutorial screen by screen. This is truly a treasure of help when these jobs and agencies are asking people to take these tests so people can get a job if you dont pass then no job. Thank you, and May God continue to Bless you !!!
Thank you for posting this video, it’s a life saver. I did a 30 question assessment and the questions were exactly in this order, except it didn’t tell me great job or what my final score was.
To the people saying its too fast…..for Gods sake, PAUSE!!!!!!!! Boy oh boy……..
This was exactly my test from Prove It. It didn’t tell me great job. But it moved just as fast.
can we download this to practice ourselves? where can we go to download this?
This is very helpful. Thank you for sharing.
office 2016 product key here >>>#plus.google.com/u/0/104028188633487744432, works great.
I cannot begin to express how helpful this was, thank you.
looks very similar to the test I took. It would be great to get a 100% .
Thanks, made an 87
0:04 Open the Excel workbook “Revenue.xls” from the default directory 0:09 Insert a row above the selected row ( between row 1 and 2) 0:12 Change the font style of the selected cell to Bold 0:18 Change the selected cell to 20 pt. 0:22 format the selected range as U.S. currency 0:29 in the current cell, enter the formula that will calculate the total revenue for year 2011. (For purpose of this test do not use the insert Function Wizard (tx.) ) 0:34 Copy the selected cell 0:39 You just copied the selected function. Paste formula into cell F5. 0:42 Save the active workbook. Do not alter file or folder name. 0:46 change the alignment for the selected cells to the right. 0:49 select cells A3 through F3. 0:57 create a boarded around selected cell using All borders and Outline and Inside. ( For purpose of this test do not use any of the Draw Border features.) 1:01 Rename Sheet1 as Revenue. 1:14 Insert a Column to the left of Column F. 1:21 In the Current cell, use the Average Function to calculate the average quarterly revenue for the fiscal year 2011 (For purpose of this test, do not use the Insert Function Wizard (tx.) ) 1:30 Change the width of Column F so that the content automatically fit within the column. 1:36 Create a chart from selected range of cells. Choose the first column chart on the left. Do not change any other chart settings. 1:44 Change the Chart Style to Style 8 ( 4th column, 2nd row). 1:51 Modify the format of the selected cell, so that the text automatically wraps around. 1:53 Insert a new worksheet. 1:58 Merge & Center the selected cells. 2:01 Sort the selected cells by Last Name, in ascending order. 2:07 Change the Page Margins to Wide or where the Top, Left, Bottom, and Right Margins is at 1”. Do not change the view to perform the task and use all other defaults. 2:12 change the orientation of this worksheet to Landscape. Do not change the View to perform the task and use all other defaults. 2:18 Add the Footer “Page 1” to the current worksheet. Do not manually type the footer or use the Custom Footer and use all other defaults. 2:30 Perform a Spelling Check on the active worksheet. 2:38 Perform the commands to center the selected worksheet both horizontally and vertically on the printed page. Do Not use Print Preview to perform this task and do not print at this time. 2:48 Using current worksheets data, create a Report that displays the Quarterly Sales by Territory. Create as a Table (not a chart) and use all other defaults. 3:00 Set up the selected worksheet so that they will each print on one page. Do not use Print Preview to perform this task and sin not print at this time. 3:08 Print the selected worksheet (without using Print Preview or changing the View) Use all other defaults. 3:14 Hide the Column I. 3:20 Freeze the top row of the current worksheet. 3:26 In the cell C2, create a formula to combine the item in A2 (CH-AD) and Inventory ID in B2 (2986) with no spaces between them, so that C2 lists “CH-AD2986”. For the purpose of this question, do not use absolute cell references. 3:46 in the cell G2, create the formula that will place the word “Yes” if cell F2 (Quarterly in Stock) is less than 25% of H2 (Quarterly in Reorder) or “No” if this condition is not met. 4:21 Selected cells G2 and C2 without selecting cells D2, E2, and F2. 4:28 Copy the selected cells and paste with actual values. 4:39 Use the fill handle to fill the empty cells from G3:G11 4:48 Format the selected column ( column G) to highlight cells with the “Light Red fill with Dark Red Text” option if the cell is equals to “Yes” 5:04 Sort the selected cells by Time and Reorder (in Z to A order) and then Quantity in Reorder (Smallest to Largest) 5:23 Change the Table Style to Table Style Light 2 ( 2nd Column, 1st Row) use all other defaults. 5:31 Using the current worksheet’s PIVOT Table, add the Task name as a Column Label. 5:39 Modify the Pivot Table so that Vacation entries are removed. 5:46 Using the current worksheet’s selected data, create a PivotChart that displays that Project Name and Time in Hours. Use all other defaults. 6:01 Using the Macro Recorder, record a macro that will type “800-555-5555” in the current cell. The Macro Name is “Phone” and the shortcut-key to assign to the macro is “CTL+e” . Use all other defaults. 6:55 Turn on the AutoFilter feature for the selected cells. 7:00 Apply a filter to Task Name (column D) so that only Design Tasks are shown. 7:08 Divide F2 by F76 . Use a relative reference for cell F2 and a mixed cell refences to refer to the cell F76 . 7:23 The selected column contains a list of airport codes and the location of the airport. Split the selected column using common delimiter, a colon (), so that the airport codes are in column K and the airport locations are in column L. Use all other defaults. 7:45 In cell F2 create a VLOOKUP formula that will look up the airport code in E2 and display the appropriate city name (using columns K and L) Look for exact match. Use all other defaults. 8:13 In cell G2, create a formula that will place the number of occasions B2 is represented in column B. 8:38 Create a custom list using cells A2:A12 8:47 Select worksheets Option 1, Option 2, and Option 3. 8:59 Without changing the View, insert a page break for the selected sheets above row 21. 9:08 Set the Print Area to contain the selected cells A1:C7
Very helpful! I have to do an assessment and this will help so much!
whats the point….its toooo fast
Great video for rehearsal. However, too fast for a beginner. Can be paused from time to time to understand the steps.
Wow thank you so much I scored 100% indeed this information was very resourceful for me. God bless
Exactly the same. Thank you so much!!! Only got to take the test once, but it went in order for 30 questions
is this an actual test?
Human Verification: In order to verify that you are a human and not a spam bot, please enter the answer into the following box below based on the instructions contained in the graphic.
Send this to a friend