Notes

1) What is the difference between the file extensions '.xlsx' and '.xlsm' ?

.xlsx - an XML based default file format.
.xlsm - an XML based file format that can contain macros.


2) What is the '.xlsb' file format ?

This is a binary based file format that can contain macros but not ribbon customisations.


3) Are there any advantages to saving a workbook with the '.xlsb' file extension ?

*) File size might be smaller
*) Read from and Write to is faster
*) Formulas larger than 8192 characters can be saved


4) What is the largest Excel file you can have ?

Either for a single workbook or a combination of open workbooks.
32 Bit - Excel 2013 / 2016 - 2 GB of memory.
64 Bit - Excel 2013 / 2016 - no hard limit. Workbook size is limited only by available memory and system resources.


5) What changes could you make to reduce the file size of a workbook ?

*) Check the last used cell on each worksheet and delete any redundant blank rows and columns.
*) Check for hidden worksheets and remove out of date sheets.
*) Compress or remove images and pictures
*) Reduce the size of pivot tables
*) Optimise formulas and remove array formulas
*) Remove any unnecessary formatting and use consistent formatting across worksheets.
*) Remove conditional formatting


6) What is Freeze Panes ?

This lets you lock the rows and columns so they stay visible when you scroll the worksheet.


7) Is there a way to close all my open workbooks at once without having to close them one at a time ?

Yes. Hold down Shift and click on the X (cross) in the top right corner of one of the open workbooks (or windows).


8) Is there a quick way to apply the same formatting to multiple worksheets ?

Yes. By grouping your sheets.


9) What is the difference between Wrap Text and Merge Cells ?

Wrap Text - when applied will mean that text will automatically appear on multiple lines to fit the column width.
Merge Cells - allows two or more cells to be combined to make one larger cell that can span multiple columns.


10) When would you use the 'Center Across Selection' horizontal text alignment ?

This allows you to center text across a selection of cells, without the disadvantages of actually merging the cells.


11) When would you use the Goal Seek data analysis feature ?

This can be used to calculate an unknown value in a given formula.


12) When would you use the Scenario Manager data analysis feature ?

Lets you construct various different scenarios with your data and allows you to perform 'what if' analysis.


13) What is the shortcut key for applying the percentage format with no decimal places ?

(Ctrl + Shift + %)


14) What is the quickest way to select all the objects on the active worksheet ?

F5 (GoTo dialog) Special objects
You can also hold down Ctrl to select individual objects


15) Can you describe Conditional Formatting ?

This is the automatic formatting of cells based on the values that the cells contain.


16) Can you describe Data Validation ?

This allows you to create restrictions on the type of data that can be entered in a cell.


17) Can you describe Outlines ?

The outlining feature lets you quickly group and summarise your data.
Using an outline helps you separate top level rows and columns from the breakdown.
(Alt + Shift + Right Arrow) - groups the selected rows


18) Does Excel contain any Track Changes functionality (like Word does) ?

Yes. This functionality can be accessed from the Review Tab, Changes group.


19) Can you provide the names of 6 different number format categories ?

Currency, Accounting, Percentage, Scientific, Date, Text


20) What is the name given to the default number format ?

General - this has no specific number format


21) When entering text into cells what would you press to insert a line break into your cell.

(Alt + Enter)


22) Where is the command located for displaying the Edit Links dialog box ?

Data Tab, Connections group, Edit Links
File Tab, Info Panel, Edit Links to Files


23) When is the Fill Handle displayed ?

It is always displayed in the bottom right corner of the active cell (or selection)


24) What features can be found on the Quick Analysis Smart Tag ?

This smart tag provides quick access to 5 features:
Formatting, Charts, Totals, Tables, Sparklines


25) When is the Quick Analysis Smart Tag displayed ?

It is displayed everytime you select two or more adjacent cells.


26) How do you find the last populated cell on a worksheet ?

Using the shortcut key (Ctrl + End).

ActiveSheet.UsedRange.Rows.Count 
ActiveSheet.UsedRange.Columns.Count
Range("A1048576").End(xlUp).Row


27) What is the Power Query add-in ?

This is a Microsoft add-in that enhances the self-service Business Intelligence experience by simplifying data discovery, access and collaboration.
In Excel 2016 this was replaced with the built-in Get & Transform group on the Data tab.


28) What is the Quick Access Toolbar ?

This is a customizable toolbar which can be used to contain your most frequently used commands.
This toolbar is independent of the tabs and is accessible from any tab.


29) What is the Custom UI Editor and when would you use it ?

This is a standalone program that can be installed to help you create and modify the ribbon UI in workbooks and add-ins.


30) Can you outline the steps to create an Excel add-in (.xlam) ?

Create a new workbook
Add the 2 application level event handlers (AddinInstall and AddinUninstall)
Save the workbook as an Excel Add-in (.xlam)


31) Is it possible to prevent a user from copying cell values from a worksheet ?

Yes. One way is is to prevent the cells from being selected by unlocking all the other cells and then protecting the worksheet by unticking 'select locked cells'.
Another way is to use VBA to reset the CutCopyMode property. This doesn't work if macros are disabled.

Sub Workbook_SheetSelectionChange(ByVal sh As Object, _ 
                                  ByVal Target As Range)
   Application.CutCopyMode = False
End Sub


32) What does a red triangle in the top right corner of a cell indicate ?

Comment


33) What does a green triangle in the top left corner of a cell indicate ?

Error Checking Smart Tag


34) What is Automatic Calculation ?

Excel will perform smart recalculation on all open workbooks whenever anything changes.
When you open a workbook if anything has changed then a smart recalculation is performed.
When you open a workbook that was saved in a previous version a full calculation is performed.


35) When should you change your calculation option to Manual ?

For workbooks that take more than a couple of seconds to recalculate.
If not then there will be a delay whenever anything changes.


36) If the word Calculate appears in the status bar, what does this mean ?

Your calculation is set to Manual and a formula in the active workbook needs recalculating.
The workbook contains circular references and the iteration option is switched on.
You have set the Workbook.ForceFullCalculation property to True.
The dependency tree has reached its limit.


37) The second time you calculate a workbook it can be significantly faster than the first time, why is that ?

Excel usually only recalculates cells and dependencies that have changed.
Excel stores and reuses the most recent calculation chain which means it doesn't have to build it again.
Excel caches recently used data.


38) Is there a way to interrupt the recalculation if it is taking too long ?

Yes. Pressing the ESC key should (in most cases) interrupt the calculation.


39) What changes could you make to speed up workbook recalculation ?

The speed taken to update depends on the number of cell references, the calculation operations and the efficiency of the functions being used.
Having more rows with fewer calculations will update faster than fewer rows with more complex calculations.
Remove any duplicated calculations
Remove any array formulas or replace them with user defined functions
Change the lookups to be performed on sorted data rather than unsorted
Use Excel or VBA built-in functions where possible
Reduce the worksheet usedrange
Avoid using any volatile functions
Replace dynamic named ranges with structured referencing
Design formulas to be forward referencing so they do not refer to any cells to the right or below
Avoid inter-workbook links
Consolidate data to fewer worksheets
Move all lookups and reference data to a single worksheet
Take out any references or expressions from your array formulas
Initiate the calculation from within VBA
Run time checks on different worksheets to identify bottlenecks


40) Is it possible to prevent the calculation from being interrupted ?

Yes, there are two ways this can be achieved:
Using the CalculationInterrupting property.
If you are running from VBA using the Application OnTime.

Application.CalculationInterrupting = xlCalculationInterruptKey.xlNoKey. 


41) When updating formulas what do the following shortcut keys do ?

(F9) - Smart Recalculation - recalculates all cells in all open workbooks.

Application.Calculate 


(Shift + F9) - Smart Recalculation - recalculates all cells in all the selected worksheets.

ActiveSheet.Calculate 
Worksheets().Calculate


(Ctrl + Alt + F9) - Full Calculation - calculates all cells in all open workbooks.

Application.CalculateFull 


(Shift + Ctrl + Alt + F9) - Full Calculation (dependency tree and calculation chain) - calculates all cells in all open workbooks.

Application.ForceFullCalculation (Added in 2007) 


42) What does the Range.Calculate method do ?

Smart Recalculation of the specified range.

Range("A1:D20").Calculate 


43) What does the Range.CalculateRowMajorOrder method do ?

Added in 2007.
Smart Recalculation of the specified range ignoring any forward references or within range dependencies.

Range("A1:D20").CalculateRowMajorOrder 


44) Is there a way in code to trigger a recalculation of the active sheet only ?

Togglying the EnableCalculation property and then recalculating.

ActiveSheet.EnableCalculation = False 
ActiveSheet.EnableCalculation = True
Activesheet.Calculate


45) Is there a way to check the calculation state ?

Yes. You can use the CalculateState property.

Application.CalculationState 


46) What does the Workbook.ForceFullCalculation property do and when would you use it ?

Workbook.ForceFullCalculation = True 

Returns or sets the workbook to forced calculation mode.
This property will be reset when Excel is restarted or the workbook is closed ?
When you want every calculation of the workbook to be a full calculation.
When this property is set to True the calculation time for data tables will increase significantly.
If a workbook has a large number of complex dependencies that takes ages to load or when a recalculation takes longer than a full calculation.


47) Write code that will trap the F9 events and redirect it to a subroutine.

Application.OnKey "{F9}", "HandleF9" 
Application.OnKey "+{F9}", "HandleShiftF9"
Application.OnKey "^+{F9}", "HandleCtrlShiftF9"
Application.OnKey "%^+{F9}", "HandleAltCtrlShiftF9"


48) What are Circular References ?

A formula in a cell that directly or indirectly refers to its own cell is called a circular reference.
Excel calculates circular references sheet by sheet without considering dependencies.
If your circular references span multiple worksheets you can get very slow calculation.
Most circular references can be removed by splitting the calculation into some additional cells.


49) Can you explain what Iterative Calculation means ?

Also known as repetitive calculation.
One of the most common ways of accommodating circular references is to increase the number of iterations.


50) How would you check if a workbook contains any Circular References ?

Switch off the "Enable iterative calculation" option.
Excel will provide a warning message if it encounters any circular references.


51) What is the Range.Dirty method ?

Designates a range to be recalculated the next time a recalculation is performed.

Range("A1:B10").Dirty = True 


52) What is Application.Volatile ?

This line of code can be included in your user defined function to force them to recalculate every time a value changes on that particular worksheet.
Volatile functions will always recalculate.


53) Does Excel have any Multi-Threading capabilities ?

Excel 2007 introduced the concept of multi-threaded calculation (and recalculation) of workbooks
Excel tries to identify parts of the calculation chain that can be recalculated concurrently on different threads.


54) What is a Formula ?

??


55) Can you describe the Evaluate Formula dialog box ?

This lets you see differen parts of a nested formula evaluated in the order the formula is calculated.


56) What is the difference between a Formula and an Array Formula ?

Formula - lets you perform a mathematical calculation in a single cell.
Array Formula - lets you perform complicated mathematical calculations in a single cell. These must be entered using (Ctrl + Shift + Enter).


57) Is there a quick way to see the formulas rather than the values ?

(Ctrl + ') - This is a single left quote, not an apostrophe.


58) What is a Worksheet Function ?

A function provides a convenient way to use a common calculation.


59) What is the quickest way to add up a column of numbers ?

AutoSum - clicking on this command will add the SUM worksheet function below the current region
SUM - using this worksheet function manually
AutoCalculate - highlight the cells and look in the status bar for the quick total


60) Can you describe the IF function ?

It checks whether certain conditions are true or false.

=IF() 


61) When would you use the VLOOKUP function ?

When you want to match a value in the first column of a table and return a specific item in that row.


62) What are the four arguments passed to the VLOOKUP function ?

Lookup value - the value to be found in the first column 
Table_array - the cell range containing the table of data
Column_index - the column number you want returned
Exact_match - (optional) whether to find an exact match


63) What 2 functions would you use if you wanted to lookup a value that was not in the first column ?

OFFSET & MATCH
INDEX & MATCH


64) What function would you use if you want to match a value in the first row of a table and return a specific item in that column ?

HLOOKUP


65) What is the difference between the COUNT function and the COUNTA function ?

COUNT - Returns the number of non blank cells in a cell range.
COUNTA - Returns the number of non blank cells in a cell range, including logical values and text.


66) Can you describe the CONCAT function ?

Added in Excel 2016, January Update to replace the CONCATENATE function.
This function is equivalent to the ampersand (&) character and can be used to join several strings together.

=CONCAT(A1," ",A2) = "A1value A2value" 


67) Using the NOW function inserts the current date and time. Is there a way to enter this information so it does not automatically update ?

Yes. Use this shortcut key instead
(Ctrl + ;) - inserts the current date
(Ctrl + Shift + ;) - inserts the current time
(Ctrl + ;) space (Ctrl + Shift + ;) - inserts the current date and time


68) What function would you use to calculate the difference between two dates ?

DATEDIF - Returns the number of units between two dates.

=DATEDIF() 


69) Can you describe the OFFSET function ?

Returns the cell value which is an offset from a given cell reference.

=OFFSET() 


70) Can you describe the MATCH function ?

Returns the position of a value in a list, table or cell range.

=MATCH() 


71) Can you describe the INDEX function ?

Returns the value from a cell range which is the intersection of a row and a column.

=INDEX() 


72) Can you describe the COUNTIF function ?

Returns the number of non blank cells that satisfies one condition.
Often referred to as conditional counting.

=COUNTIF() 


73) Can you describe the SUMIFS function ?

Returns the total value of the numbers that satisfies multiple conditions.
Often referred to as conditional summing.

=SUMIFS() 


74) Can you describe the INDIRECT function ?

Returns the text string of the contents of a given cell reference.

=INDIRECT() 


75) What is the difference between the SUBSTITUTE function and the REPLACE function ?

SUBSTITUTE - returns the text string with a substring substituted for another substring
REPLACE - this should be used when you want to replace text that occurs in a specific location in a text string.

SUBSTITUTE(text, old_text, new_text [,instance_num]) 
REPLACE(old_text, start_num, num_chars, new_text)


76) Are there any built-in worksheet functions that are volatile ?

Yes, TODAY, NOW, RAND, OFFSET, INDIRECT
CELL and INFO are also volatile but it depends on their arguments.


77) How would you sum all the values in column A where column B is greater than 15 ?

{=SUM(A1:A10*(B1:B10>=15))} 
=SUMIF(B1:B10,">=15",A1:A10)


78) What is an Excel Data Table ?

Not to be confused with a regular table.
This is a type of What If data analysis that creates a table based on the impact of one (or two) variables in a formula.
They provide a way to calculate multiple variations and display and compare the results in a table layout.
There is even a calculation option called "Automatic except for data tables".


79) How would you add a drop-down list to a cell to provide a list of possible values ?

Data Validation


80) Describe the different types of workbook protection ?

Read Only - requires a password to open the workbook
Structure Only - adding, deleting, hiding, unhiding worksheets
Windows Only - changing size or positions of windows
Individual Worksheets -


81) What is a Slicer and when would you use one ?

Added in 2010.


82) What is a Named Range ?

A named range is a short text description that can be used instead of the cell address to refer to individual cells or ranges of cells.


83) What are the advantages of using Named Ranges ?

You can insert or delete cells, rows and columns and the cell references remain intact.


84) What is the difference between a Workbook Level named range and a Worksheet Level named range ?

Workbook Level - These can be referenced from any worksheet in the workbook, without prefixing.

=Name_Wbk 

Worksheet Level - These are intended to only be used on one specific worksheet although you can reference them from other worksheets by prefixing the name of the worksheet.

=Sheet1!Name_Wsh 


85) What type of named range is created when you use the Name Box ?

Typing into the Name Box creates a named range with workbook level scope.


86) What is a Dynamic Named Range ?

A dynamic named range expands automatically when more data is added to the range.
This type of named range uses a combination of the worksheet functions OFFSET and COUNTA.


87) What are Natural Language Formulas ?

Also known as range labels.
This feature allowed people to use the column labels and row labels to refer to cells without explicitly defining them as named ranges.
This was disabled by default in Excel 2000 and removed completely in Excel 2007.
Any files that are opened that use this feature have their formulas automatically converted to use cell references.


88) Write the code to populate an array with the values in a Named Range ?

Dim arValues As Variant 
arValues = Range(NamedRange).Value
arValues = Application.Evaluate[NamedRange].Value
arValues = [NamedRange].Value


89) What is a Chart ?

A chart provides a visual representation of data found in a table.


90) What is the quickest way to create a chart ?

(F11) - highlighting the source data and pressing F11 will insert a chart sheet (not an embedded chart) defaulting to a column chart.


91) Can you give the name of 4 different built-in chart types ?

Column, Line, Pie, Area


92) Can you give the name of any new built-in chart types that were introduced in Excel 2016 ?

Box Whisker, Funnel, Histogram, Pareto, Sunburst, Treemap, Waterfall


93) What is a Table ?

Defining a table gives you an easy way to manage and analyze your data with lots of additional features including:
Built-in Filtering, Sorting, Row Shading, Quick Totals, Header Row, Calculated Columns


94) How can you create a table ?

??


95) What is the shortcut key for togglying the autofilter on a table ?

(Ctrl + Shift + L)


96) What are Structured References ?

These can be used in conjunction with tables.
When you create a table Excel automatically assigns a name to the table and to each column.
You can then use these names in your formulas instead of cell references.
The combination of table and column names is called a structured reference.


97) What is a Pivot Table ?

A pivot table lets you summarise, analyze, explore and present your data.
They are highly flexible and can be quickly adjusted depending on how you need to display your results.


98) Is it possible to create a pivot table using a dynamic named range ?

Yes. Create a dynamic named range that contains your data and then reference that when you create the pivot table.


99) Is it possible to use data from more than one worksheet to create a pivot table ?

Yes. As long as the data is in the same workbook.


100) Which event can you use to check if a pivot table has been modified ?

Application.SheetPivotTableUpdate 
Workbook.SheetPivotTableUpdate
Worksheet.PivotTableUpdate


101) What are the three different report layout types for a pivot table ?

Compact -
Outline -
Tabular -


102) How would you disable automatic sorting in a pivot table ?

Goto > More Sort Options > Right Click 'pivot tables' > Sort Menu > More Options > sort automatically


103) How would you prevent column widths from changing automatically when a pivot table is refreshed ?

pivot table options > enable preserve formatting > auto format


104) Can you display text in the data area of a pivot table ?

??


105) What is PowerPivot ?

This is an add-in that provides an extension to the built-in pivot table functionality.


106) What is a Macro ?

??


107) How would you run a macro that has been saved in the active workbook ?

Developer Tab, Macros


108) What is the Personal.xlsb ?

This is the personal macro workbook.
This is a special hidden workbook which you can use to store your macros.
It is open every time Excel is open assuming one has been created.


109) Can you assign a macro to a shape ?

Yes. Right click and select Assign Macro


110) If a VBA macros was running and needed to be interrupted what keys would you press to interrupt it ?

(Escape)
(Ctrl + Break)
(Ctrl + Shift + Break)


111) A macro is running very slowly, what changes could you make to speed it up ?

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
ActiveSheet.DisplayPageBreaks = False
Worksheet.EnableFormatConditionsCalculation = False
Read and write large blocks of cells in a single operation
Avoid selecting or activating any objects
Declare all variables with explicit data types
Never use the Variant data type


112) What is the difference between ThisWorkbook and ActiveWorkbook ?

Application.ActiveWorkbook 
Application.ThisWorkbook

ActiveWorkbook - the workbook in the active window.
ThisWorkbook - the workbook where the code is actually running.


113) What is the difference between UsedRange and CurrentRegion ?

ActiveSheet.UsedRange.Row 
ActiveSheet.ActiveCell.CurrentRegion.Select

UsedRange - the cell range of all the used cells on the worksheet.
CurrentRegion - the cell range inside blank rows and columns.


114) What is the difference between Range.Value and Range.Value2 ?

Value - This returns the formatted value.
Value2 - This returns the actual numerical value without formatting.


115) Is it possible to hide a worksheet so the user cannot unhide it ?

Sheets(2).Visible = xlSheetVisibility.xlSheetVeryHidden 


116) Write code to display the values in cells B1 to B5 in the Immediate Window ?

Dim lrowno As Long 
For lrowno = 1 To 5
   Debug.Print Range("B" & lrowno).Value
Next lrowno


117) Write a User Defined Function to return the day of the week ?

Public Function DAY_OF_WEEK1() As String 
   DAY_OF_WEEK = Application.WorksheetFunction.TEXT( _
                    Application.WorksheetFunction.NOW,"dddd")
End Function
Public Function DAY_OF_WEEK2() As String
   DAY_OF_WEEK = VBA.FORMAT(VBA.NOW,"dddd")
End Function


118) Write a User Defined Function that accepts two arguments and returns the lowest common denominator ?

Public Function LOWEST_COMMON_DENOM(ByVal Number1 As Double, _ 
                                    ByVal Number2 As Double) As Double
   LOWEST_COMMON_DENOM = Application.WorksheetFunction.LCM(Number1, Number2)
End Function


119) What is the Application.Union method ?

Returns a range object that represents the union of two or more ranges.

Application.Union 


120) What is the Application.Intersect method ?

Returns a range that object that represents the rectangular intersection of two or more ranges.

Application.Intersect 


121) Write code to select all the non-blank cells

Application.Union(Cells.SpecialCells(xlCellTypeFormulas, 23), 
                  Cells.SpecialCells(xlCellTypeConstants, 23)).Select

The 23 = xlSpecialCellsValue.xlNumbers | xlTextValues | xlLogical | xlErrors


122) What is Auto_Open and where should it be declared ?

This subroutine runs automatically when a workbook (or add-in) is opened manually.
It is not run when a workbook is opened programmatically using the Open method.
This subroutine must be placed in a standard code module (not a class module, ThisWorkbook, Sheet1 or Userform).
This has been replaced with the WorkbookOpen event and is only available for backwards compatibility.


123) Can you give the name of 3 workbook level events ?

NewSheet - Occurs before an additional worksheet is added to this workbook. 
Open - Occurs before the workbook is opened.
SheetCalculate - Occurs after the user re-calculates any worksheet in this workbook.


124) Can you give the name of 3 worksheet level events ?

BeforeDoubleClick - Occurs before any cell on the worksheet is double clicked. 
Change - Occurs when any cell on the worksheet has its value changed.
SelectionChange - Occurs when the selection is changed on the worksheet.


125) Can you give the name of 3 application level events ?

WorkbookBeforeClose - Occurs before any open workbook is closed. 
WindowActivate - Occurs when the user moves focus to another window or open workbook.
WorkbookModelChange - Occurs when a data model is updated.


126) Write code to test if a worksheet is a chart sheet ?

If (Sheets(1).Type = xlChart) Then 
If (VBA.TypeName(Sheets(1)) = "Chart Sheet") Then


127) Write code to ask a question and put the answer in cell A1 ?

Range("A1").Value = Application.InputBox("What is your name?") 


128) What are Form Controls and how are they different from ActiveX controls ?

Form controls were added in Excel 5.0. ActiveX controls were added in Excel 97.
Form controls are very basic and can be placed on charts. ActiveX controls are more complicated and cannot be placed on charts.
Both types of controls can be placed on worksheets.
Both types can link to cells and ranges on a worksheet.



© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Prev