Microsoft Excel - Excel from Beginner to Advanced Flashcards
Comprehensive Excel Course
What are the different forms of launching Excel?
Shortcut, File Menu, Template
What is name and function of the top left portion of the interface?
It is the quick access toolbar.
What is located below the quick access toolbar of the interface?
The ribbon; which contains menus and submenus– which are configurable.
What is located below the ribbon of the interface?
The formula bar; which is configurable and can also be turned off.
What happens if you double click a tab in the ribbon?
You toggle between hiding and unhiding it.
How do you customize the quick access toolbar?
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.
What’s at the bottom of the Excel interface?
The status bar; which contain view buttons and zoom control; among other things.
How can you customize the status bar in Excel?
By right clicking it.
What’s at the center of the interface and occupies most of the screen space?
The workbook.
What’s at the bottom of the workbook?
The sheet tab(s) and an add button.
How is each worksheet organized?
Each sheet is organized into lettered columns and numbered rows.
How do you save a spreadsheet in Excel?
You go to the File tab
and select Save or Save As option.
How do you open an existing document in Excel?
You go to the File tab
and select Open.
What are some common Excel shortcut keys?
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
How do you save a workbook?
Options-Save As
Options-Save
Ctrl+S
Where does text align in a cell?
Text aligns to the left.
Where do numbers align to in a cell?
Numbers align to the right.
What do you put in a cell to signal a function?
You put the equals sigh (“=”) to signal a function.
What are the two type of cell references?
Relative and absolute.
What is a relative cell reference?
It is a reference where the referenced cell is relative to the position of the current cell.
What is absolute cell reference?
It is a reference where the referenced is independent to the position of the current cell and does not change.
How do you signal absolute cell references?
You signal absoluto cell references by putting a dollar sign ($) before the column and row.
What is the order of (arithmetic) operations in Excel?
PEMDAS:
Parentheses
Exponents
Multiplication
Division
Add
Subtract.
Or BODMAS:
Brackets
Orders
Division
Multiplaction
Add
Subtract
How can you evaluate a formula?
Selecting Formulas tab,
then Formula Auditing section,
then Evaluate formula.
What are the different parts of an Excel function?
The equal sign.
The function name.
The function arguments.
What is the Function Windows and how do you access it?
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.
What is the Excel Autosum Shortcut Key?
Alt-“=”
How does the autofill command work?
Drag the square in the selection outline to autofill with the selected content.
How can you move a selection within a worksheet?
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.
How do you move a range within a worksheet?
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.
What is a keyboard shortcut for inserting a column o row in a worksheet?
Select the column or row where to insert and press Ctrl - +
How do you adjust the height of rows or the width of columns?
How do you do if for multiple rows or columns.
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.
How do you automatically fit the height of rows or width of columns?
How do you do it for multiple rows or columns?
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.
How can you hide from view a row or column?
How can you bring it back to view?
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.
How can you rename your worksheets?
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.
How do you delete a worksheet?
You right click on the sheet name and choose the Delete option.
Sheet deletions are permanent and cannot be undone.
How can you move a worksheet?
Click and hold the sheet name and move the cursor laterally; the sheet will be moved to where the upside down arrow points.
How can you copy or move a worksheet to another workbook?
Right click on the sheet name and choose the Copy or Move option and fill in the dialog box.
How can you customize borders?
Select “More Borders” from the drop-down Borders menu.
How do you format a cell to display currency symbols?
You select the cell or range, then choose the currency symbol in the Number menu, then select the desired currency.
How do you format a cell for percentage?
You select the cell or range, then select the “%” symbol in the number menu.
How do you copy the formatting only from one cell to another cell or range?
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.
How do you create and use cell syles?
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.
How do you merge and center cells?
You select the cells to merge and go to the Home Tab, and in the Alignment section choose the Merge and Center option.
What is Conditional formatting?
How do you activate it?
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.
How do you edit conditional formatting rules?
You go to the Home menu, then Styles section and open the Conditional Formatting menu and choose Manage Rules option.
How do you insert images into a worksheet?
You go to the Insert tab, then the Ilustrations section and open the Pictures menu.
How do you insert shapes into a worksheet?
You go to the Insert tab;
then the Ilustrations menu;
and select the Shapes option.
How do you edit a shape in a worksheet?
You select the shape and then choose the corresponding section and menu in the Shape Format tab.
How do you insert SmartArt into a worksheet?
You go to the Insert tab and then choose the Illustrations menu and choose the SmartArt option.
How do you create a bar chart in a worksheet?
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.
How do you edit a chart in Excel?
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.
How do you add or modify chart data in Excel?
First select the chart
then go to the Chart Design section
then choose the Select Data option
and follow the dialog box instructions.
How do you format a chart or its elements in Excel?
First select the chart or the element and go to the Chart Design or Format tabs and choose the relevant section and option.
How do you move a chart to its own worksheet?
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.
How do you preview how a worksheet will print through Excel?
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.
How do you modify the print settings of an Excel worksheet?
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.
How do you modify the print margins in Excel?
You go to the File Menu;
then Print Submenu;
then Page Setup Link;
then Margins Tab and input your selected values.
How do you select the Page Layout view to see how each page will print?
You go to the View Menu;
then Workbook Views section;
then Page Layout option.
How do you add Header and Footer content to an Excel Worksheet?
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.).
How do you print a specific area of the worksheet?
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.
How do you use an existing template in Excel?
You go to the File menu;
choose the New option;
and choose fromt the templates shown or use the search box.
How do you sort by special criteria in Excel?
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.
How do you add subtotals to a list in Excel?
You first 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.
How do you remove duplicate rows in a list?
You go to the Data menu
and Data Tools section
and choose Remove Duplicates option
and fill in the dialog box.
What’s de difference between SUM and DSUM functions?
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.
What’s de difference between AVG and DAVG functions?
AVG averages all the values 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.
What’s de difference between COUNT and DCOUNT functions?
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.
What’s de difference between DCOUNT and DCOUNTA functions?
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.
How do you create a Validation List in Excel?
You go to the Data Tab, then Data Tools Section, then Data Validation Button, and fill in the dialog box.
How do you create a Pivot Table?
- Create or select a Table.
- Go to Insert Menu.
- Select Pivot Table.
How do you configure a Pivot Table?
Drag the fields in fields section to the areas below.
How do you group data in a Pivot Table?
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.
How do you format values in a Pivot Table?
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.
How do you modify Pivot Table calculations?
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.
How do you drill down the values of a cell in a Pivot Table.
You double click on the cell in question; a new worsheet with the related rows of the original table is created and shown.
How do you create a Pivot Table Chart?
While on the Pivot Table, you go to the Pivot Table Analyze Tab.
Then Select Pivot Table Chart option.
Select the desired chart format.
How do filter Pivot Table data with the Pivot Table Field menu.
You drag the desired filter field to the Field section in the menu.
How do you filter Pivot Table data with the Slicer?
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.
How do you enable Power Pivot in Exce?
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.
How do you creat a Data Model for a Power Pivot Table?
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.
How do you specify print headers in Excel?
Go to the Page Setup menu;
then Page Layout section;
then Print Titles option;
then Sheet tab.
How do you consolidate data across multiple worksheets in Excel?
Go to Data menu;
then Data Tools section;
then Consolidate option.
How do you name a range of cells in Excel?
Select the range;
Then click on the Name Range Box;
then type in name; no spaces.
How do yo edit a range name?
Go to the Formulas section;
select the Defined Names section;
select the Name Manager option;
use the pop up menu.
What does the AND () function do?
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.
What does the IF() function do?
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.
What does the COUNTIF() function do in Excel?
It counts the number of times a condition is true within the specified range.
The format is COUNTIF(range,condition)
What does the SUMIF() function do in Excel?
Is sums up the values of the range if the criteria is met.
What does the IFERROR() function do in Excel?
It returns a specified value if the condition results in an error.
What does the VLOOKUP() function do in Excel?
It returns a specified value from a table if the criteria is met.
What does the HLOOKUP() function do in Excel?
It returns a given value that meets the specified criteria in a table.
What does the INDEX() function do in Excel?
It returns the value inside the specified array at the specified row and column.
What does the MATCH() function do in Excel?
It returns the row of the specified value within the specified range.
What do the LEFT(), RIGHT(), and MID() functions do in Excel?
They return a specified string from a specified cell from the respective left, right, and middle of the string.
What does the LEN() function do in Excel?
It returns the number of charachters (i.e.: the length) in the specified cell.
What does the SEARCH() search function do in Excel?
It returns the position of the specified character inside the specified cell; from left to right.
What does the CONCATENATE () function do in Excel?
It joins all the specified cells and values into one.
What is referred to by Tracing Precedents and how do you accomplish in Excel?
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.
What is referred to by Tracing Dependents in Excel and how do you accomplish it?
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.
What is referred to by Watch Window and how do you accomplish it?
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.
How do you visualize the formulas in a sheet?
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.
How do you protect a worksheet from modifications by other users? How do you allow for modifications of certain cells within a protected worksheet?
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.
How do you protect the structure of a workbook in Excel?
You select the Review tab;
then go to the Protect section;
and then select the Protect Workbook option.
What is the procedure for adding a password to a workbook in Excel?
Select the File menu;
then select the Info section;
then press the Protect Workbook button;
then select the Encrypt with Password option.
What does the Goal Seek function work and how do you use it?
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.
How does the Solver Tool work and how do you use it?
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.
What does a Data Table under What If analysis do? How do you set it up and use it?
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.
What are Scenarios in Excel and how do you implement them?
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.
What are macros in Excel?
They are a series of predefined operation which can be recorded and reutilized within Excel.
What is the Developer Tab in Excel? How do you activate it?
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.
How do you create a Macro with Macro Recorder?
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.
How do you go about editing a Macro with VBA?
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.
How do you go about creating a button to run a Macro in Excel?
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.
How do you create a button for a macro in Excel?
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.
How do you edit a Macro in Excel?
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.
How do you acces the Visual Basic editor?
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.
What is the Project Explorer window and how do you acces it?
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.
What is a VBA module and how do you create one?
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.
What is an Excel VBA Procedure and how do you create one?
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.
How to do you create VBA variables?
You write the following code:
dim [variable name] as {type of variable].
(Content inside brackets is set by programmer.)
What is an IF statement for in Excel VBA and how do you write it?
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
What do loops do in Excel VBA?
What is the syntax of the DO WHILE loop?
Loops repeat a block of code while a condition remains true.
The syntaxis of a DO WHILE loop is:
DO WHILE {condition]
[code]
LOOP
What does an InputBox do in Excel VBA and what is the syntax?
An InputBox opens a dialog box an asks for user input; the syntax is:
InputBox ([prompt text]).
What does the instruction msgbox () do?
It creates a messabox displaying the content between parentheses.
