Fundamental Excel Exam Practice
1.00 Point: XL Activity 2.22: Entering and Formatting Dates In the Online Sales sheet, click cell A16. On the Home tab, in the Number group, click the Number Format arrow, and then click More Number Formats. Under Type, click 3/14/12, and then click OK. In the Online Sales sheet, in cell A19, type the date 6/7/22. Use Format Painter to copy the date format from A16 to A19.
Format Painter is the paintbrush button next to the paste button
1.00 Point: XL Activity 3.18: Formatting Axes in a Line Chart Modify the labels of the Horizontal (Category) Axis labels in the line chart so that the years in the range B4:F4 display. Modify the Vertical (Value) Axis so that Minimum bound is 4000000. Modify the Vertical (Value) Axis so that the Major unit is 2000000.
Right click horizontal part of the chart, then click select data, click edit for horizontal side, and put range wanting to be displayed. Right click vertical part of the chart, then click format axis and do rest.
1.00 Point: XL Activity 3.08: Exploding and Coloring a Pie Slice Explode the Waterfront slice from the pie by 10%. With the Waterfront slice still selected, in the Format Data Point pane, under Series Options, click Fill & Line, and then click Fill to expand its options. Click the Gradient fill option button, click the Preset gradients arrow, and then in the fourth row, click the last gradient.
Similar to problem above except second line of info. Put % of explosion and click pie slice being exploded. Fill & Line is paint-bucket button under series options.
1.00 Point: XL Activity 2.16: Filtering an Excel Table and Displaying a Total Row Click the Category arrow. Click the (Select All) check box to clear all the check boxes. Click the Maple check box, and then click OK. On the Table Tools Design tab, in the Table Style Options group, click the Total Row check box. Click cell A43, then click the arrow that displays to the right of cell A43. Click Sum. Clear all filters from the table. Filter the table so that only Erosion Control displays in the Landscape Use column. Filter the table so that only Oak displays in the Category column.
Table Tools Design tab was highlighted in green "Clear all filters" the columns with drop down arrows will show funnel symbol and click that to click clear filters
1.00 Point: XL Activity 2.03: Using the SUM and AVERAGE Functions Click cell B4. On the Formulas tab, in the Function Library group, click AutoSum. Type A11:A39 and press ENTER. Click cell B5. On the Formulas tab, in the Function Library group, click More Functions, point to Statistical, click AVERAGE, type D11:D39, and then click OK.
Use only first line of box "Number 1"
1.00 Point: XL Activity 2.25: Grouping Worksheets for Editing Right-click the Online Sales sheet tab, and from the shortcut menu, click Select All Sheets. With the worksheets grouped, select columns A:G and set the column width to 85 pixels. In cell A2, type Week of May 24. Merge & Center the text across A2:G2. Apply the Heading 1 cell style to A2:G2. Ungroup the worksheets and display the In-Store Sales sheet.
When grouping whole columns changing one column width changes all selected colums To ungroup right click the sheet tab and you will find the button
1.00 Point: XL Activity 2.01: Using Flash Fill Select columns C:D, and then insert two columns. In Cell C11, type 13129 and then on the Home tab, in the Editing group, click Fill and then click Flash fill. In cell D11, type Oak. Select cell D11, and then press CTRL+E to fill the values in the column. Delete column B.
Just right click either selected column and insert
1.00 Point: XL Activity 2.19: Splitting a Worksheet Window into Panes Make cell F9 the active cell. On the View tab, in the Windows group, click Split. Remove the split bars from the window, and then make A1 the active cell. On the Page Layout tab, in the Themes group, click Themes, and then click Slice. Select the range A13:H13, and then apply the Heading 4 cell style. With the range A13:H13 selected, change the alignment to Center.
just click split again to remove split bars
1.00 Point: XL Activity 1.06: Constructing a Formula and Using the SUM Function Click cell B8 , and then type =b4+b5+b6+b7 to create a formula that adds the values in cells B4 through B7 . Click cell C8 , and then use the point and click method to enter a formula that adds the values in cells C4 through C7 . Click cell D8 . On the Home tab, in the Editing group, click Sum to enter a function to sum the values in cells D4:D7
method just means = SUM is E looking symbol aka autosum
1.00 Point: XL Activity 1.21: Using the Quick Analysis Tool Select the range E4:E9. In the lower right corner of the selected range, click the Quick Analysis button. In the displayed gallery, click Totals, and then click the first Sum button to sum the range E4:E9 and place the result in cell E10. Select the range C5:E9, and then apply Comma Style; Excel widens the columns to accommodate the data. Select the nonadjacent ranges C4:E4, E10, and then apply the Accounting Number Format. Click cell E10. From the Cell Styles gallery, apply the Total cell style
result placed itself in cell E10
1.00 Point: XL Activity 1.08: Using Merge & Center and Applying Cell Styles Select the range A1:F1, and then in the Alignment group, click Merge & Center. Select the range A2:F2, and then in the Alignment group, click Merge & Center. Apply the Title cell style to cell A1. Apply the Heading 1 cell style to cell A2. Select the range B3:F3, hold down CTRL, select the range A4:A8. With the non-adjacent ranges selected, apply the Heading 4 cell style.
CTRL let go after getting other range
1.00 Point: XL Activity 1.09: Formatting Financial Numbers Select the nonadjacent ranges B4:E4 and B8:E8, and then on the Home tab, in the Number group, click Accounting Number Format. Select the range B5:E7, and then in the Number group, click Comma Style. Select the range B8:E8, and then in the Styles group, display the Cell Styles gallery. Under Titles and Headings, click the Total cell style.
CTRL to select both ranges
1.00 Point: XL Activity 1.12: Using the Chart Tools to Apply Chart Styles In the chart, click anywhere in the text Chart Title to select the title box, type Second Quarter Cardio Sales and then press ENTER. Click inside the chart border, outside of the chart title. To the right of the chart, click the Chart Styles button. On the right side of the Style gallery, drag the vertical scroll bar down, and then click the sixth style- Style 6 . Above the Style gallery, click Color. Under Colorful, click the third row of colors. Position the chart so that the upper left corner of the chart aligns with the upper left corner of cell A10.
instead of pressing ENTER click outside of chart somewhere
1.00 Point: XL Activity 3.15: Calculating a Value After an Increase In cell C13, type the formula =b13*(100%+$b$10) to calculate the projected tourism spending after the forecasted increase in cell B10 is added to the first-year total. Use Format Painter to copy the format from B13 to C13. Copy the formula and formatting in cell C13 to the range D13:F13.
Copy and paste did work on this one.
1.00 Point: XL Activity 2.08: Using the COUNTIF Function In cell A10, type Oak Trees. With cell B10 active, on the Formulas tab, in the Function Library group, click More Functions, point to Statistical, and click COUNTIF. In the Range box type G14:G42 and in the Criteria box type Oak and click OK. Change the alignment of cell B10 to Align Left.
Copy and paste may not work in this case.
1.00 Point: XL Activity 3.07: Rotating a Pie Chart by Changing the Angle of the First Slice In the Format Data Series pane, under Series Options, click Series Options. Change the Angle of first slice to 250.
Degree sign one is angle.
1.00 Point: XL Activity 2.05: Using the MIN and MAX Functions In cell B7, insert a function that will display the lowest retail price for the range D11:D39. In cell B8, insert a function that will display the highest retail price for the range D11:D39.
Do as question 28 and 29 steps did except with MIN and MAX functions
1.00 Point: XL Activity 1.22: Copying Formulas Containing Absolute Cell References Click cell F4, and then type = (equal sign). Click cell E4, and then type / (division sign). Click cell E10, and then press F4 to set cell E10 as an absolute cell reference in the formula. To the left of the Formula Bar, click the Enter button to maintain cell F4 as the active cell. Copy the formula from cell F4 down through the range F5:F9.
Fn key needs to be on to click F4 I don't know what the "to the left" part means, just click ENTER
1.00 Point: XL Activity 3.21 Creating and Sizing a Map Chart In the Advertising sheet, select the range A3:B8, and then on the Insert tab, in the Charts group, click Maps, and then click Filled Map. Add Tourism Advertising by State as the chart title. Change the Height of the chart to 2 inches and the Width to 4 inches.
For last part when chart is selected Chart Tools Format tab next to Design tab and edit in Size group.
1.00 Point: XL Activity 2.31: Constructing Formulas that Refer to Cells in Another Worksheet In the Summary sheet, click cell C5. Type = and then click the Online Sales sheet tab. Click cell E12 and then press ENTER. In cell D5, create a formula to insert the Total Rose Supply Sales in cell F12 from the Online Sales worksheet. In cell C6, enter a formula to insert the Total Rose Sales in cell E12 from the In-Store Sales worksheet. In cell D6, enter a formula to insert the Total Rose Supply Sales in cell F12 from the In-Store Sales worksheet.
For step 2 hold down ALT and press =
1.00 Point: XL Activity 3.14: Using Format Painter Use Format Painter to copy the format of cell A2 to cell A8. Copy the value in cell F5 to cell B13.
Format Painter is paintbrush button next to paste button. Copy one cell and paste into other cell.
1.00 Point: XL Activity 3.13: Using Parentheses in a Formula to Calculate a Percentage Rate of Increase In the range B4:F4, use the fill handle to fill the years in one-year increments beginning with 2016 and ending with 2020. In cell C6, type the formula =(c5-b5)/b5 to calculate the rate of increase from the first year to the second year. Format cell C6 with Percent Style. Copy the formula in C6 across through cells D6:F6.
Put in first two years then fill. Copy and paste did not work on this one.
1.00 Point: XL Activity 2.26: Formatting and Constructing Formulas on Grouped Worksheets Group the Online Sales and the In-Store Sales sheets. In cell E5, enter a function that sums the values in the range B5:D5. Use the fill handle to copy the function in cell E5 to the range E6:E11. In cell G5, enter a function that sums the values in the range E5:F5. Use the fill handle to copy the function in cell G5 to the range G6:G11.
Right click sheet tab and select all sheet to group
1.00 Point: XL Activity 2.06: Moving Data and Resolving a # # # # # Error Message Select the range A4:B8, and then drag it to D4:E8. Select columns D:E and then AutoFit the selected columns. Select the range D4:E8, and then on the Home tab, in the Styles group, click Cell Styles and apply the 20% - Accent1 cell style.
To "AutoFit" go to right side of selected columns and then double click
1.00 Point: XL Activity 3.05: Formatting a Data Series with 3-D Effects Use the Format Data Series pane to apply the first bevel effect to the top of the pie chart with a width and height of 512 pt. Then apply the same effect to the bottom of the pie chart with a width and height of 512 pt. Change the surface material of the chart to Plastic, (under Standard, the third material).
To get to the Format Data Series pane double click on any pie slice not near labels, then go to effects, then 3-D format, then rest, hard.
1.00 Point: XL Activity 2.04: Using the MEDIAN Function Click cell B6. In the Function Library group, click More Functions, display the list of Statistical functions, scroll down, and then click MEDIAN. Type D11:D39 and click OK.
Use only first line of box "Number 1"
1.00 Point: XL Activity 1.07: Copying a Formula by Using the Fill Handle Click cell E3, type Total and then press ENTER. With cell E4 active, on the Home tab, in the Editing group, click AutoSum to sum the values in the range B4:D4. Click cell E4, and then drag the fill handle down through cells E5:E8 to copy the formula from cell E4 to the range E5:E8. Click cell F3, type Trend and then press ENTER.
aka autosum