Microsoft Excel - Excel from Beginner to Advanced

Question 1

What are the different forms of launching Excel?

Answer 1

Shortcut, File Menu, Template

Question 2

What is name and function of the top left portion of the interface?

Answer 2

It is the quick access toolbar.

Question 3

What is located below the quick access toolbar of the interface?

Answer 3

The ribbon; which contains menus and submenus– which are configurable.

Question 4

What is located below the ribbon of the interface?

Answer 4

The formula bar; which is configurable and can also be turned off.

Question 5

What happens if you double click a tab in the ribbon?

Answer 5

You toggle between hiding and unhiding it.

Question 6

How do you customize the quick access toolbar?

Answer 6

You click on the downward arrow and select which tools to activate. You can also right click a menu option and choose add to quick access toolbar.

Question 7

What’s at the bottom of the Excel interface?

Answer 7

The status bar; which contain view buttons and zoom control; among other things.

Question 8

How can you customize the status bar in Excel?

Answer 8

By right clicking it.

Question 9

What’s at the center of the interface and occupies most of the screen space?

Answer 9

The workbook.

Question 10

What’s at the bottom of the workbook?

Answer 10

The sheet tab(s) and an add button.

Question 11

How is each worksheet organized?

Answer 11

Each sheet is organized into lettered columns and numbered rows.

Question 12

How do you save a spreadsheet in Excel?

Answer 12

You go to the File tab
and select Save or Save As option.

Question 13

How do you open an existing document in Excel?

Answer 13

You go to the File tab

and select Open.

Question 14

What are some common Excel shortcut keys?

Answer 14

Close a spreadsheet: Ctrl+W

Open a spreadsheet: Ctrl+O

Go to the Home tab: Alt+H

Save a spreadsheet: Ctrl+S

Copy: Ctrl+C

Paste: Ctrl+V

Undo: Ctrl+Z

Question 15

How do you save a workbook?

Answer 15

Options-Save As

Options-Save

Ctrl+S

Question 16

Where does text align in a cell?

Answer 16

Text aligns to the left.

Question 17

Where do numbers align to in a cell?

Answer 17

Numbers align to the right.

Question 18

What do you put in a cell to signal a function?

Answer 18

You put the equals sigh (“=”) to signal a function.

Question 19

What are the two type of cell references?

Answer 19

Relative and absolute.

Question 20

What is a relative cell reference?

Answer 20

It is a reference where the referenced cell is relative to the position of the current cell.

Question 21

What is absolute cell reference?

Answer 21

It is a reference where the referenced is independent to the position of the current cell and does not change.

Question 22

How do you signal absolute cell references?

Answer 22

You signal absoluto cell references by putting a dollar sign ($) before the column and row.

Question 23

What is the order of (arithmetic) operations in Excel?

Answer 23

PEMDAS:

Parentheses

Exponents

Multiplication

Division

Add

Subtract.

Or BODMAS:

Brackets

Orders

Division

Multiplaction

Add

Subtract

Question 24

How can you evaluate a formula?

Answer 24

Selecting Formulas tab,

then Formula Auditing section,

then Evaluate formula.

Question 25

What are the different parts of an Excel function?

Answer 25

The equal sign.

The function name.

The function arguments.

Question 26

What is the Function Windows and how do you access it?

Answer 26

It is a sub-window that displays the arguments for a function; along with an explanation of the purpose of the function and the arguments and a help link. You access it by clicking the function button on the function bar.

Question 27

What is the Excel Autosum Shortcut Key?

Answer 27

Alt-“=”

Question 28

How does the autofill command work?

Answer 28

Drag the square in the selection outline to autofill with the selected content.

Question 29

How can you move a selection within a worksheet?

Answer 29

After selecting the range, you move the cursor over one of the borders until it turns into a cross, and while holding down the mouse button, you move the selection to its desired location.

Question 30

How do you move a range within a worksheet?

Answer 30

First select the range;

then hover the mouse over a border until the pointer changes to a four-headed arrow and hold the mouse button;

then move the range while holding the mouse button.

Question 31

What is a keyboard shortcut for inserting a column o row in a worksheet?

Answer 31

Select the column or row where to insert and press Ctrl - +

Question 32

How do you adjust the height of rows or the width of columns?

How do you do if for multiple rows or columns.

Answer 32

You float the cursor over the edge of the row or column label until a double headed arrow appears; hold the mouse button and drag to the desired width or height is obtained.

For multiple rows or columns, you select the range and then do the same as above.

Question 33

How do you automatically fit the height of rows or width of columns?

How do you do it for multiple rows or columns?

Answer 33

You float the cursor over the edge of a column or row label; then double click the mouse button to automatically adjust the heigth or width.

For multiple rows or columns you select the range and do as above.

Question 34

How can you hide from view a row or column?

How can you bring it back to view?

Answer 34

You select the range and right click on the row or column label and select the Hide option.

To bring back to view you do as above but select the Unhide option;

or

Hover the cursor over the row or column labels bordering the hidden row or column until the cursor turns int a double arrow with a hollow space between them; then hold and drag the cursor to bring to view.

Question 35

How can you rename your worksheets?

Answer 35

You can double click on the sheet name and type in the new name

or

You can right click on the sheet name and chose the Rename Sheet option.

Question 36

How do you delete a worksheet?

Answer 36

You right click on the sheet name and choose the Delete option.

Sheet deletions are permanent and cannot be undone.

Question 37

How can you move a worksheet?

Answer 37

Click and hold the sheet name and move the cursor laterally; the sheet will be moved to where the upside down arrow points.

Question 38

How can you copy or move a worksheet to another workbook?

Answer 38

Right click on the sheet name and choose the Copy or Move option and fill in the dialog box.

Question 39

How can you customize borders?

Answer 39

Select “More Borders” from the drop-down Borders menu.

Question 40

How do you format a cell to display currency symbols?

Answer 40

You select the cell or range, then choose the currency symbol in the Number menu, then select the desired currency.

Question 41

How do you format a cell for percentage?

Answer 41

You select the cell or range, then select the “%” symbol in the number menu.

Question 42

How do you copy the formatting only from one cell to another cell or range?

Answer 42

You go to the original cell and then click on the Format Painter option on the Clipboard section. Then select the target cell or range and click on it.

If you double click the format painter, the pasting of format continues until you press Esc or click on the format painter again.

Question 43

How do you create and use cell syles?

Answer 43

Select the cell or range to apply the style to.

Then go to the Styles section and open the Cell Styles Menu;

then choose New Cell Style and choose the corresponding values.

Finally go to the Cell Styles menu and choose the corresponding style from the Custom menu.

Question 44

How do you merge and center cells?

Answer 44

You select the cells to merge and go to the Home Tab, and in the Alignment section choose the Merge and Center option.

Question 45

What is Conditional formatting?

How do you activate it?

Answer 45

It is the formatting of a cell or range if the cell contents meet certain conditions or criteria.

You activate it by selecting the cell or range, then go to the Home Tab, then the Formatting section and choose the Conditional formatting option.

Question 46

How do you edit conditional formatting rules?

Answer 46

You go to the Home menu, then Styles section and open the Conditional Formatting menu and choose Manage Rules option.

Question 47

How do you insert images into a worksheet?

Answer 47

You go to the Insert tab, then the Ilustrations section and open the Pictures menu.

Question 48

How do you insert shapes into a worksheet?

Answer 48

You go to the Insert tab;

then the Ilustrations menu;

and select the Shapes option.

Question 49

How do you edit a shape in a worksheet?

Answer 49

You select the shape and then choose the corresponding section and menu in the Shape Format tab.

Question 50

How do you insert SmartArt into a worksheet?

Answer 50

You go to the Insert tab and then choose the Illustrations menu and choose the SmartArt option.

Question 51

How do you create a bar chart in a worksheet?

Answer 51

You first select the data to be charted; then go to the Insert tab in the ribbon; then the Charts sections and choose the corresponding chart.

Question 52

How do you edit a chart in Excel?

Answer 52

You select the chart and either choose the icons to the right of the chart or choose the Chart Design and Format tabs in the ribbon.

Question 53

How do you add or modify chart data in Excel?

Answer 53

First select the chart

then go to the Chart Design section

then choose the Select Data option

and follow the dialog box instructions.

Question 54

How do you format a chart or its elements in Excel?

Answer 54

First select the chart or the element and go to the Chart Design or Format tabs and choose the relevant section and option.

Question 55

How do you move a chart to its own worksheet?

Answer 55

First select the chart, then;

go to the Chart Design tab, then;

choose the Move Chart option and follow the instructions on the dialog box.

Question 56

How do you preview how a worksheet will print through Excel?

Answer 56

You go to the worksheet and choose the File menu then;

choose the Print option;

the right hand side will display a preview of the printed document.

Question 57

How do you modify the print settings of an Excel worksheet?

Answer 57

You go to the worksheet and choose the File menu then;

choose the Print option;

the left hand side will display the settings of the printed document.

Question 58

How do you modify the print margins in Excel?

Answer 58

You go to the File Menu;

then Print Submenu;

then Page Setup Link;

then Margins Tab and input your selected values.

Question 59

How do you select the Page Layout view to see how each page will print?

Answer 59

You go to the View Menu;

then Workbook Views section;

then Page Layout option.

Question 60

How do you add Header and Footer content to an Excel Worksheet?

Answer 60

In the Page Layout view, click on the respective rectangle of the header or footer.

(If they do not appear, go to the top edge of the page until a double arrow appears and double click for the Header and Footer areas to appear.)

Click on the respective rectangle and type the content you wish to appear; you may use the Header and Footer menu to add custom and dyanmic content. (i.el page number, date, time, file path, etc.).

Question 61

How do you print a specific area of the worksheet?

Answer 61

First method:

Select the range and go to the File menu;

then Print option:

then Print Active Worksheets drop down menu;

Then Print Selection option.

Second method:

Go to the Page Layout menu:

Then Page Setup section;

then Print Area option.

Question 62

How do you use an existing template in Excel?

Answer 62

You go to the File menu;

choose the New option;

and choose fromt the templates shown or use the search box.

Question 63

How do you sort by special criteria in Excel?

Answer 63

Place cursor anywhere in the list;
then go to Data menu
and chose Sort section and
Sort button
and choose Custom list for sort order in dialog box.

Question 64

How do you add subtotals to a list in Excel?

Answer 64

You firs sort by the column you wish to calculate subtotals for.

Then go to the Data tab and then Outline section and choose Subtotal option and fill the dialog box.

Question 65

How do you remove duplicate rows in a list?

Answer 65

You go to the Data menu
and Data Tools section
and choose Remove Duplicates option
and fill in the dialog box.

Question 66

What’s de difference between SUM and DSUM functions?

Answer 66

SUM adds all the value of a range
while DSUM adds only the values that meet the criteria specified in the argument box.

You can use OR and AND operators with DSUM.

Question 67

What’s de difference between AVG and DAVG functions?

Answer 67

AVG averages all the value of a range; while DAVG averages only the values that meet the criteria specified in the argument box.

You can use OR and AND operators with DAVG.

Question 68

What’s de difference between COUNT and DCOUNT functions?

Answer 68

COUNT counts all the value of a range; while DCOUNT counts only the values that meet the criteria specified in the argument box.

You can use OR and AND operators with DCOUNT.

Question 69

What’s de difference between DCOUNT and DCOUNTA functions?

Answer 69

DCOUNT counts all the numeric values of a range; while DCOUNT counts all the values that meet the criteria specified in the argument box.

You can use OR and AND operators with DCOUNT.

Question 70

How do you create a Validation List in Excel?

Answer 70

You go to the Data Tab, then Data Tools Section, then Data Validation Button, and fill in the dialog box.

Question 71

How do you create a Pivot Table?

Answer 71

1. • Create or select a Table.
2. • Go to Insert Menu.
3. • Select Pivot Table.

Question 72

How do you configure a Pivot Table?

Answer 72

Drag the fields in fields section to the areas below.

Question 73

How do you group data in a Pivot Table?

Answer 73

Select the first group of data/rows.

Go to the Pivot Table Analyze menu.

Go to the Group Section.

Select the Create Group option.

OR

Drag the fields into the rows section in the order of desired grouping.

Question 74

How do you format values in a Pivot Table?

Answer 74

You select the respective value in the Values section of the Pivot Table menu.

Select Value Field Settings option.

Select the Number Format button in the pop up menu.

Select the desired settings.

Question 75

How do you modify Pivot Table calculations?

Answer 75

You select the respective vaue in the Values section of the Pivot Table menu.

Select Value Field Configuration.

Select the Show Values As tab in the pop up menu.

Select the appropriate values in the each field.

Optionally, you may personalize the name.

Question 76

How do you drill down the values of a cell in a Pivot Table.

Answer 76

You double click on the cell in question; a new worsheet with the related rows of the original table is created and shown.

Question 77

How do you create a Pivot Table Chart?

Answer 77

While on the Pivot Table, you go to the Pivot Table Analyze Tab.

Then Select Pivot Table Chart option.

Select the desired chart format.

Question 78

How do filter Pivot Table data with the Pivot Table Field menu.

Answer 78

You drag the desired filter field to the Field section in the menu.

Question 79

How do you filter Pivot Table data with the Slicer?

Answer 79

First click on any cell of the Pivot Table.

Then go to the PivotTable Analyze menu.

Select the slicer option in the Filter section.

Select the value you wish to filter by and a button menu will be created.

Question 80

How do you enable Power Pivot in Exce?

Answer 80

You go to the File menu.

Select Options.

Select Add Ins.

Select COM add Ins in the drop down menu.

Check the Power Pivot box in the menu and select Accept.

Question 81

How do you creat a Data Model for a Power Pivot Table?

Answer 81

You click on any part of the desired worksheet.

Select the Power Pivot Menu.

Select Add to Data Model in Tables menu.

Select and add again for every additional table you wish to add to the model.

Question 82

How do you specify print headers in Excel?

Answer 82

Go to the Page Setup menu;

then Page Layout section;

then Print Titles option;

then Sheet tab.

Question 83

How do you consolidate data across multiple worksheets in Excel?

Answer 83

Go to Data menu;

then Data Tools section;

then Consolidate option.

Question 84

How do you name a range of cells in Excel?

Answer 84

Select the range;

Then click on the Name Range Box;

then type in name; no spaces.

Question 85

How do yo edit a range name?

Answer 85

Go to the Formulas section;

select the Defined Names section;

select the Name Manager option;

use the pop up menu.

Question 86

What does the AND () function do?

Answer 86

It evaluates various conditions and returns TRUE if ALL of them are TRUE.

(Each condition is separated by a comma and all are enclosed inside parentheses.

Question 87

What does the IF() function do?

Answer 87

It evaluates a conditions and carries out an action if true or a different action if false.

The condition and actions are enclosed in parentheses and separated by commas.

Question 88

What does the COUNTIF() function do in Excel?

Answer 88

It counts the number of times a condition is true within the specified range.

The format is COUNTIF(range,condition)

Question 89

What does the SUMIF() function do in Excel?

Answer 89

Is sums up the values of the range if the criteria is met.

Question 90

What does the IFERROR() function do in Excel?

Answer 90

It returns a specified value if the condition results in an error.

Question 91

What does the VLOOKUP() function do in Excel?

Answer 91

It returns a specified value from a table if the criteria is met.

Question 92

What does the HLOOKUP() function do in Excel?

Answer 92

It returns a given value that meets the specified criteria in a table.

Question 93

What does the INDEX() function do in Excel?

Answer 93

It returns the value inside the specified array at the specified row and column.

Question 94

What does the MATCH() function do in Excel?

Answer 94

It returns the row of the specified value within the specified range.

Question 95

What do the LEFT(), RIGHT(), and MID() functions do in Excel?

Answer 95

They return a specified string from a specified cell from the respective left, right, and middle of the string.

Question 96

What does the LEN() function do in Excel?

Answer 96

It returns the number of charachters (i.e.: the length) in the specified cell.

Question 97

What does the SEARCH() search function do in Excel?

Answer 97

It returns the position of the specified character inside the specified cell; from left to right.

Question 98

What does the CONCATENATE () function do in Excel?

Answer 98

It joins all the specified cells and values into one.

Question 99

What is referred to by Tracing Precedents and how do you accomplish in Excel?

Answer 99

Tracing precedents is visualizing the values that precede the calculation of a formula.

You accomplish it by selecting the cell with the formula in question;

then select the Formulas tab;

than select the Formula Auditing section;

then select the Trace Precedents option.

Question 100

What is referred to by Tracing Dependents in Excel and how do you accomplish it?

Answer 100

Trancing dependents referrs to visualizing the cells with formulas that depend on the specified cell for their calculations.

You accomplish this by selecting the specified cell;

then select the Formulas tab;

then select the Formula Auditing section;

then select the Tracing Dependents option.

Question 101

What is referred to by Watch Window and how do you accomplish it?

Answer 101

Watch Window is a means where you visualize the value of a cell within a sheet while modifying or working on a different sheet.

You accomplish by selecting the Formulas tab;

then select the Watch Window option;

then select the Add Watch button on the pop up menu;

then selecting the cell to be watched.

The pop up menu remains visible with the respective value regardless of the sheet one selects.

Question 102

How do you visualize the formulas in a sheet?

Answer 102

You select the Formulas tab;

then select the Formula Auditing section;

then select the Show Formulas option;

instead of the values of formulas, the formulas themselves are visualized.

Question 103

How do you protect a worksheet from modifications by other users? How do you allow for modifications of certain cells within a protected worksheet?

Answer 103

You protect a worksheet by selecting the Review tab;

then select the Protect section;

then select the Protect Sheet option.

To allow a certain range of cell to be modified, first select the range;

then go to the Home tab;

then select the Alignment pop up menu;

then select the Protection tab in the menu;

then unselect the Locked check box;

then select the desired properties to allow.

When this is done, protect the sheet; the specified range wil remain unprotected.

Question 104

How do you protect the structure of a workbook in Excel?

Answer 104

You select the Review tab;

then go to the Protect section;

and then select the Protect Workbook option.

Question 105

What is the procedure for adding a password to a workbook in Excel?

Answer 105

Select the File menu;

then select the Info section;

then press the Protect Workbook button;

then select the Encrypt with Password option.

Question 106

What does the Goal Seek function work and how do you use it?

Answer 106

Goal Seek calculates the needed value of a given cell in order to achieve a value in another cell.

Go to the Data tab;

then select Forecast section;

then select the What If Analysis menu;

then fill in the desired values of the pop up menu.

Question 107

How does the Solver Tool work and how do you use it?

Answer 107

The Solver Tool analyzes a range and specified constraints and calculates the necessary range values needed to achieve the required target values.

Solver tool is and Add-In that first needs to be activated.

To use it, go to the Data tab;

then Select the Analyze section;

then select the Solver option;

and then specifiy the required values in the pop up menu.

Question 108

What does a Data Table under What If analysis do? How do you set it up and use it?

Answer 108

A Data Table under What If analysis evaluates various results for a number of alternative values.

First set up a table with the alternative values on the left column and the formula as the header for the second column;

then select the table;

then go to the Data tab;

then go to the Forecast section;

then select the What-If Analysis menu;

then the Data Table option;

then specify the cell to change for each alternative value–

the function fills out the respective results in the table.

Question 109

What are Scenarios in Excel and how do you implement them?

Answer 109

Scenarios are alternative results for a given table given different ranges of values or scenarios.

You implement them by using the Scenario Manager;

first select the range of values to evaluate;

then go to the Data tab;

then go to the Forecast section;

then select the What-If option;

then choose Scenario Manager;

Add scenarios with respective values for the initially selected range.

Question 110

What are macros in Excel?

Answer 110

They are a series of predefined operation which can be recorded and reutilized within Excel.

Question 111

What is the Developer Tab in Excel? How do you activate it?

Answer 111

It is a Tab in Excel through which you can access Macro commands; among other things.

One way of activating it is to righ click on the ribbon;

then choose Customize the Ribbon;

then check the box of Developer in the pop up menu.

Question 112

How do you create a Macro with Macro Recorder?

Answer 112

You go to the Developer tab;

then select the Code section;

then select the Record Macro option;

carry out the operations you wish to have recorded;

then select the Stop Recording option.

Question 113

How do you go about editing a Macro with VBA?

Answer 113

Go to the Developer tab;

then go to the Code section;

then select the Visual Basic option;

this will open up a separate window for Visual Basic.

Then select the relevant Module in the left pane and edit the Macro on the right.

Question 114

How do you go about creating a button to run a Macro in Excel?

Answer 114

First select the Developt tab;

go to the Control section;

choose the Insert option;

fill in the required information in the pop up menu, mainly the Macro name;

optionally, you may edit the button name.

Question 115

How do you create a button for a macro in Excel?

Answer 115

First select the Developer tab;

then go to the Controls section;

then go to the Insert option;

then go to the Form Controls section of the pop up menu;

then drag the mouse where button will be created and;

select Macro to be assigned to button.

Question 116

How do you edit a Macro in Excel?

Answer 116

Go to the Developer tab;

then go to the Code section;

then go to the Visual Basic option;

a Visual Basic window will appear;

within this windows go to the Project and then Modules option and;

edit the macro text which appears on the right side of the window and;

close the windows.

Question 117

How do you acces the Visual Basic editor?

Answer 117

Go to the Developer tab;

then go to the Code Menu;

then go to the Visual Basic menu option;

and the Visual Basic editor window with various components will show up.

Question 118

What is the Project Explorer window and how do you acces it?

Answer 118

The Project Explorer window is where you view and edit your VBA code.

To acces it, within the VBA window;

go to the View menu;

then select the Project Explorer option.

Question 119

What is a VBA module and how do you create one?

Answer 119

A VBA module is repository for VBA code.

You create a module by going to the Insert menu in the VBA window;

then select the Module option.

Question 120

What is an Excel VBA Procedure and how do you create one?

Answer 120

A Procedure is a series of steps to be carried out by VBA, a Macro by another name.

You create one by going to the Insert Menu;

and select the Procedure option.

Question 121

How to do you create VBA variables?

Answer 121

You write the following code:

dim [variable name] as {type of variable].

(Content inside brackets is set by programmer.)

Question 122

What is an IF statement for in Excel VBA and how do you write it?

Answer 122

An If statement evaluates a condition and executes certain code if the condition is true; optionally it executes other code if the condiditon is false.

IF statements follow the following format:

IF [condition] THEN

(code)

ELSE

(code)

ENDIF

Question 123

What do loops do in Excel VBA?

What is the syntax of the DO WHILE loop?

Answer 123

Loops repeat a block of code while a condition remains true.

The syntaxis of a DO WHILE loop is:

DO WHILE {condition]

[code]

LOOP

Question 124

What does an InputBox do in Excel VBA and what is the syntax?

Answer 124

An InputBox opens a dialog box an asks for user input; the syntax is:

InputBox ([prompt text]).

Question 125

What does the instruction msgbox () do?

Answer 125

It creates a messabox displaying the content between parentheses.