Excel Online Practice Test 2023

Inside the data set, click any single cell. Remove Duplicates may be found in the Data Tools category on the Data tab. All of the check boxes should be unchecked, and then click OK.

Choose a cell for your drop-down list. To open the Data Validation dialog box, go to the Data tab and select Data Validation. Select List under Allow in the Settings tab. Click OK after selecting the Source field.

While clicking the first cell in the range you want to combine, press Shift and click the last cell in the range you want to merge. Only one of the range’s cells should have data. Select Merge & Center from the Home menu.

To check for duplicates, select the cells you want to check. Click Conditional Formatting in the Styles category on the Home tab. Select Highlight Cells Rules, Duplicate Values. Click OK after selecting a formatting style. After that, the duplicate names are now highlighted.

Select cells you’d want to lock. Click the small arrow in the Alignment group on the Home tab to access the Format Cells popup window. Select the Locked check box on the Protection tab, then click OK to exit the popup. Select Protect Sheet or Protect Workbook from the Changes group on the Review tab in the ribbon, and then reapply protection.

Select a cell for your drop-down menu. At the top of the screen, select the Data tab. Select Data Validation.  Select the cells you intend to include in your drop-down menu’s selection options and click OK.

Scroll through your spreadsheet until the row you want to lock is the first row visible beneath the letter row. Click “View” in the menu, then select “Freeze Panes” in the ribbon, and then “Freeze Top Row”.

Click the cell you intend to split in the table. Select the Layout tab.  Click Split Cells. Select the desired number of columns and rows in the Split Cells dialog, then click OK.

Type the equal symbol (=) in the cell where you want the result to display. Enter the first number, then the minus sign, then the second number. By pressing the Enter key, you may finish the formula.

To add a single column, right-click the entire column to the right of where you want to add the new column and choose Insert Columns. Select the same amount of columns to the right of where you want to add additional ones to insert multiple columns. Select Insert Columns when you right-click the selection.

Choose the cell where you want the combined data to go. Select the first cell you wish to combine and type =. Type & and enclose it in quotation marks with a space. Press enter after selecting the next cell to combine.

Inside the data set, click any single cell. Click PivotTable in the Tables group on the Insert tab. The dialog box below appears. Excel will choose the data for you automatically. New Worksheet is the default place for a new pivot table. Then, click the OK button.

Fill up the blanks in Excel with your data. To create a graph or chart, choose from nine graph and chart options. Select your data and then ‘Insert’ the graph you want.  Change the colors and style of your data. Change the size of the legend and axis labels on your chart. If necessary, change the Y-axis measurement options. If necessary, rearrange your data. Give your graph a title. Save your graph or chart.

Place your cursor over the top of the column you want to move in the worksheet where you want to rearrange columns. Hold the Shift key on the keyboard, then click and drag the right or left border of the column you wish to shift to the right or left. The darker border will show where your column will be relocated.

For the hidden columns, choose the adjacent columns. Unhide the chosen columns by right-clicking them and selecting Unhide.

At the very bottom of the left bar, choose Options after clicking the File tab. Select Trust Center from the left-side pane, and then click Trust Center Settings. Click Macro Settings on the left in the Trust Center dialog box, then Enable all macros and select OK.

To freeze a column, select the column or columns to the right of you intend to freeze. On the Ribbon, select the View tab. Choose Freeze Panes from the drop-down menu after selecting the Freeze Panes command. As shown by the gray line, the column will be frozen.

In the cell where you want the percentage, type the formula Part/Total and copy it down to as many rows as you need. To show the decimal fractions as percentages, click the Percent Style button (Home tab > Number group).

In the top cell, enter a formula. Hover the mouse cursor over the Fill handle, which is a little square in the lower right-hand corner of the cell. The cursor will turn to a thick black cross when you do this. Hold the fill handle down the column and drag it over the cells you wish to copy the formula to.

Select the cell where the VLOOKUP formula should be applied. At the top of the screen, select Formulas. On the Ribbon, choose Lookup & Reference. At the bottom of the drop-down menu, select VLOOKUP. Choose the cell where you’ll insert the value whose data you’re looking for. In the table_array box, specify the data you wish VLOOKUP to use for its search. In the col_index_num box, specify the column number that VLOOKUP will use to find the appropriate data. Enter FALSE (exact match) or TRUE (approximate match) in the range lookup box to specify if you want an exact match. At the bottom of the popup window, click OK and type the value whose data you’re looking for.

Choose a cell in the column or columns that you want to hide. Click Format in the Cells group on the Home command tab. Select Hide & Unhide from the Format menu’s Visibility section, then select Hide Columns. The column is now hidden.

By two different cells:

Type “=” in a cell. To multiply the first number, click in the cell that contains it. “*” should be typed in. Press Enter after clicking the second cell you want to multiply. Create a column of numbers to multiply, then place the constant in a different cell.

By cells and numbers using the product formula:

For example, Excel would multiply (A1 x A3 x A4 x A5 x B1 x 10) in the formula “=PRODUCT(A1,A3:A5,B1,10) since A3:A5 indicates that it should multiply A3, A4, and A5.

By a column of values by a constant:

Create a column of numbers to multiply, then place the constant in a different cell. Type “=” in a new cell and then click the first cell you’d want to multiply. Add a “$” before both the letter and the number to the name of the cell that contains the constant. To do the multiplication on the other numbers, press enter, then copy and paste this into new cells.

In Excel, go to the Home tab in the top menu bar and then “Find & Select” on the right side. A pop-up box will show once you select “Go to Special.” Select “Blanks” and then “OK.” After all of the blank rows have been highlighted, go to the Home tab and click the “Delete” button on the right-hand side. The blank rows will be removed if you choose “Delete Sheet Rows.”

Click Format in the Cells group on the Home tab. Under Visibility, click Hide & Unhide, then click Unhide Rows.

Click Blank workbook if you wish to start a new spreadsheet. Click Open Other Workbooks in the lower-left corner, then Browse on the left side of the resulting window to open an existing spreadsheet. To add a new worksheet, go to the bottom of the list of sheets and click the + button. Simply click into a blank cell and begin typing to insert data. The data from the spreadsheet may be copied and pasted into another column. You may also copy data into Excel from other programs. It’s so simple to do addition, subtraction, multiplication, and division. Simply put =4+6 or =2*5, then press enter to get the answer. Excel includes a lot of helpful functions. To find the function that will solve a certain problem, go to the Formulas tab and scroll through the various functions until you find the one you need. To save, use Ctrl + S. You may also use the Quick Access Toolbar’s Save button.

Wrap Text automatically:

Wrap Text is located in the Alignment group on the Home tab. The data in the cell wraps to fit the column width, thus if the column width changes, the data wrapping changes as well.

Wrap Text manually:

To open the Format Cells dialog, use Ctrl + 1. (or right-click the selected cells and then click Format Cells). Select the Wrap Text option on the Alignment tab and click OK.

Add or delete the desired item from the worksheet or area of your current worksheet where your list items are located. Select the cell that includes the drop-down list, then click Data Validation in the Data menu at the top of your screen, then Data Validation again from the menu. Adjust the cell range in the pop-up menu’s Settings tab, under Source, to include the new cell you put data into or to exclude the one you erased it from. Then press OK.

Click the Filter command after selecting the Data tab. For each column, a drop-down arrow will show in the header cell. The Filter menu will display once you click the drop-down arrow for the column you wish to filter. Click OK when you’re finished.

Select “Error checking” and “Circular References” from the “Formulas” tab. Excel will show you where circular references are found in each cell.

Allow Excel to calculate the sum of a column or row of numbers for you. Select a cell next to the numbers you wish to add, then press Enter after clicking AutoSum on the Home tab.

To alphabetize data in Excel, go to the Data Ribbon, click Sort, and then choose the column you wish to alphabetize by. With your cursor, select the data you want to alphabetize. If you want to include more information, you can select multiple columns instead of just one.

When you apply the formula to the numbers in column A, it will appear like this: =STDEV. S(A2:A10). Excel will provide the standard deviation as well as the average of the applied data.

With the Ampersand Symbol:

Select the cell where the combined data should be placed. After typing “=”, click the first cell you’d want to combine. Click the second cell you wish to combine and type “&.”  Click the Enter key.

With the CONCAT Function:

Select the cell where the combined data should be placed. After typing “=CONCAT(,” click the first cell you’d like to combine. Click the second cell you wish to combine and type “,”. Press the Enter key after typing “)”.

In Excel, type the syntax =AVERAGE or pick AVERAGE from the formula dropdown menu to determine the mean.

Choose the column that is directly to the right of the one you want to freeze. Select the View tab, Windows Group, and then Freeze Panes from the drop-down menu. Excel adds a thin line to indicate the start of the frozen pane.

Place the cursor where you wish to insert the symbol in your file. The Symbol dialog box will appear. Select Wingdings in the Font box. Enter 252 in the Character code box at the bottom. Choose the check mark that you desire then click insert.To dismiss the dialog box, click Close. You may also modify the size and color of it.

Just choose the same number of rows above the ones you want to add additional ones to insert multiple rows. Select Insert Rows from the context menu when you right-click the selection.

To start, choose the data you want to include in the chart and then go to the Insert tab on the Ribbon. Insert a pie or doughnut chart in the Charts group. Click 2D Pie in the resulting menu, and it will appear. You may also customize your chart to your preference; there are many possibilities available.

Choose File, then Info. Choose Encrypt with Password from the Protect Workbook box. Select OK after entering a password in the box.  Select OK after confirming the password in the Reenter Password box.

To sort a column, click any cell in the column. Click AZ in the Sort & Filter group on the Data tab to sort in ascending order.

Choose a chart. Select the (+) in the top right corner of the chart. Trendline is the option to choose. Note that Excel only shows the Trendline option if you pick a chart with several data series without first selecting a data series. Select any data series options you wish in the Add Trendline dialog box, then click OK.

=DATEDIF(birth date,as of date,”y”) is the most basic and accurate method for calculating age in Excel. This provides the rounded-down number of years.

Select the cells you want to format in an Excel sheet. To open the Format Cells dialog, use Ctrl+1. Select Custom from the Category list on the Number tab, then input the date format you desire in the Type box. To save the changes, click OK.

The first step is to fill an Excel spreadsheet with the data you need. After you’ve set and accounted for all of the data values, go to the Number section of the Home tab and assign the proper data type to the different columns. Select the type of Excel graph you’d want to make.

To divide a number in cell A2 by 5, for example, use =A2/5. To divide a cell A2 by cell B2 is =A2/B2 . Type cell references separated by the division symbol to divide several cells in a row.

A column of numbers should be highlighted. Select the line chart icon from the insert tab. Then pick the first 2-D line. Then you made a line graph.

Click the row you intend to move first. Move your cursor to the edge of the selection after picking the row. Hold down the left mouse button and drag it (top or bottom) to the desired location for the row to be moved. Then, when you let off of the left mouse button, you’ll receive a message that says, “There’s already data here. Do you want to replace it ? ”. To replace a row, click OK.

Select the cells on the worksheet that you wish to be the print area. Hold down the Ctrl key and click the locations you want to print while holding down the Ctrl key. Click Print Area, then Set Print Area, on the Page Layout tab, in the Page Setup group.

Choose the data you’d want to sort. Select Sort & Filter from the Home tab of the menu bar (commonly known as the ribbon) at the top of your screen. Then select either Sort Oldest to Newest or Sort Newest.

Simply select the cell, open the Format Cells dialog, and select the Strikethrough box if you want to apply Strikethrough formatting to the entire contents of the cell. Select the text you want to format in the formula bar or in the cell itself if you only want to apply Strikethrough to a part of a cell.

Select the IF function from the “Insert Function”. The goal is for the function to display “Yes” if the result is higher than ten, and “No” if the result is less than ten. The logical test will determine whether the function displays “Yes” or “No.” The function will display “Yes” if it is true.

To insert a row, just right-click the whole row above the one you want to replace, then select Insert Rows.

Select cell (or a range of cells) to the right or above where you want to add more cells. Choose the chosen cells while holding down control, then click Insert from the pop-up menu. Select whether the chosen cells should be moved down or to the right of the newly inserted cells from the Insert menu.

Choose the column or columns you’d want to edit. Format may be found in the Cells group on the Home tab. Click AutoFit Column Width under Cell Size.

On the Developer tab, under the Controls group, click Insert, and then pick Check Box under Form Controls.

To select all of the cells on the sheet, use the keyboard shortcut Ctrl + A. Format cells by right-clicking and selecting Format cells. Uncheck the Locked option on the Protection tab, then click Ok. Select only the cells, columns, or rows that you want to protect.

Select the bar graph option from the Insert tab, then proceed to the section Charts. There are more graph design possibilities available, but we’ll stick with the first one for now. The graph will be created and inserted into the spreadsheet instantly by Excel.

Using the Insert tab, select the entire dataset. Select “Insert Static Chart” from the Charts group. The Histogram chart icon may be found in the HIstogram group. Right-click on the vertical axis and choose Format Axis to customize the chart.

Choose a cell from your data. After selecting Home, click Format as Table. Pick a design for your table. Set your cell range in the Format as Table dialog box. If your table has headers, mark it, then click OK.

Go to the Home tab on the main ribbon menu. Click the Format drop-down arrow in the Cells group. Select Unprotect Sheet from the list. To remove the password, type the password and then click OK.

For example, if cell A2 has an invoice date of 1/1/2021 and cell B2 contains a paid date of 1/30/2021, you may use the formula =B2-A2 to calculate the number of days between the two dates, which is 29.

Excel macro is a sequence of activities that you may record, label, save, and run as many times and at any time as you want.

You can easily determine the percentage change between two values using the formula =(new_value-old_value)/old_value.

Open Microsoft Excel. Select New from the Menu. Then, either click on Blank workbook to make a simple worksheet or just simply press Ctrl + N to make a new spreadsheet. If you open the spreadsheet for the first time, Sheet1 will be generated as a worksheet, and the name of the spreadsheet will be given as Book1.

Open the worksheet that contains the data you want to plot in a scatter chart, or copy the data from the worksheet into a new worksheet. Choose the data you want to display in the scatter chart. Select Insert Scatter (X, Y) or Bubble Chart from the Insert tab. Select Scatter.

Select Developer. Click the Macro button in the Code group. The ‘Record Macro’ dialog box will appear. Give your macro a name in the Record Macro dialog box. Make sure “This Workbook” is chosen in the “Store macro in” option. It starts recording your actions in Excel as soon as you click OK. When you’re finished, go to the Developer tab and click the Stop Recording button.

Go to the Page Layout. Uncheck the View option in the Sheet Options group, under Gridlines.

Select the complete name column from which you want to separate the names. Select Text to Columns from the Data tab, then the Data Tools group. Choose the Delimited option and click Next on the first step of the Convert Text to Columns Wizard. You then click Finish after selecting the data format and destination.

The given time, for example, is 9:15:00 AM in cell B1 and 4:50:15 PM in cell B2. To subtract time, type =B2-B1, and the elapsed time will be returned. It will be shown as an AM time, so right-click and select Format Cells, then change the format to h:mm. It’ll return in time format.

Select all of the data, including the headings and footers. Use the CTRL+C key to copy it. Right-click on the cell where you want to transpose the table. Select paste special. To open the paste special dialogue, use CTRL+ALT+V. Check the transpose checkbox in the bottom-right corner. Press the OK button.

Unhide a worksheet by right-clicking on any visible tab and selecting Unhide. Click the sheet you need to unhide in the Unhide dialog box, then select OK.

For example, to count the numbers in the range A1:A20, use the following formula: =COUNT (A1:A20).

Click Header & Footer in the Text group on the Insert tab. In Page Layout view, Excel shows the worksheet. Click the left, middle, or right header or footer text box at the top or bottom of the worksheet page to add or update a header or footer. Create a new header. To exit the header section, click anywhere in the worksheet when you’ve finished. Press Esc to exit the header box without keeping your changes.

Open Chart Tools by selecting a chart.Select Design then Change Chart Type . Select Combo, Cluster Column, and Secondary Axis Line. For the data series you want to display, click Secondary Axis. Choose Line from the drop-down arrow then select OK.

To determine the average, click a cell below the column or to the right of the row of the numbers you like to calculate. Select Average from the AUTOSUM on the HOME tab, then press Enter.

Choose the cell in which you’d want the result to show. Click More Functions on the Formulas tab, then Statistical, and then one of the following functions: COUNTA if you’re counting cells that aren’t empty. If you’re counting cells with numbers, use COUNT. If you want to count blank cells, use COUNTBLANK. COUNTIF is used to count cells that meet a set of criteria. Press RETURN after selecting the desired range of cells.

Select the Record Macro button in the Code group on the Developer tab OR click the button in the bottom left corner of your screen that like a spreadsheet with a red dot in the top left corner. Give your macro a name. Make a choice for a shortcut key. Because it will replace the original, be sure the letter you pick isn’t already in use as a shortcut key. Choose a location to save your macro. This Workbook is usually where you’ll save your macro. In the Description box, type a brief description of what your macro will do, then click OK.

By dragging your mouse over the row headers, you may choose several rows. Click the CTRL and “-” (minus sign).

Press Alt+Enter after clicking the location inside the cell where you wish to break the line or insert a new line.

The first row of the new page should be selected. Click Breaks in the Page Setup group on the Page Layout tab. Select  Insert Page Break. Excel shows a solid line to indicate the beginning of the new page.

Choose the excel cell you like to enlarge to meet the text size. Then go to Format after clicking Home. To enlarge the menu item, select AutoFit Row Height / AutoFit Column Width.

Choose which worksheets you’d like to print. See Select one or more worksheets for further details. Select the Print check box under Gridlines in the Page Layout tab’s Sheet Options group. Select File tab, then click Print.

Select all cells with hyperlinks by pressing Ctrl+A, or select all cells by pressing Ctrl+A. Remove Hyperlinks by right-clicking and selecting Remove Hyperlinks.

Choose the range of cells you’d want to format. Go to Home, then Format as Table. Select table style having alternate rows of shading. Select the table, click Design, and then uncheck the Banded Rows box and check the Banded Columns box to change the shading from rows to columns.

Choose a cell in the column that needs to be sorted, or a range of cells in the column that needs to be sorted. Choose any of the choices under the Sort & Filter group, depending on whether you want to sort in ascending or descending order, and then click the Data tab in the Menu Bar to run a quick sort.