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.
You can group sheets by holding down Ctrl and selecting the different tabs.

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, Queries & 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).


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
Save the workbook as an Excel Add-in (.xlam)
You can add two optional event handlers if you need to (AddinInstall and AddinUninstall)

31) Is it possible to prevent a user from copying cell values from a worksheet ?
Yes. One way to prevent the cells from being selected is 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) Is it possible to prevent a user from adding or removing worksheets ?
Yes. Review > Protect Workbook > Protect Structure and Window.

33) What does a red triangle in the top right corner of a cell indicate ?
This indicates that a comment has been added to the cell.

34) What does a green triangle in the top left corner of a cell indicate ?
This indicates that the Error Checking Smart Tag has identified a potential issue.

35) 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.

36) 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.

37) 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.

38) 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.

39) 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.

40) Is it possible to prevent the calculation from being interrupted ?
Yes, there are two ways this can be achieved:
If you are running from VBA using the Application OnTime.
Using the CalculationInterrupting property.

Application.CalculationInterrupting = xlCalculationInterruptKey.xlNoKey. 

41) 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 'formulas and 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

42) When updating formulas what do the following shortcut keys do ?
(F9) - Smart Recalculation - recalculates all cells in all open workbooks.


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


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


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

Application.ForceFullCalculation (Added in 2007) 

43) What does the Range.Calculate method do ?
Smart Recalculation of the specified range.


44) What does the Range.CalculateRowMajorOrder method do ?
Added in 2007.
Smart Recalculation of the specified range ignoring any forward references or within range dependencies.


45) 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

46) Is there a way to check the calculation state ?
Yes. You can use the CalculateState property.


47) 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.

48) 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"

49) 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.

50) 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.

51) 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.

52) What is the Range.Dirty method ?
Designates a range to be recalculated the next time a recalculation is performed.

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

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

54) 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.

55) What is a Formula ?
A formula is a way of performing a calculation using data in your workbook.

56) Can you describe the Evaluate Formula dialog box ?
This lets you see different parts of a nested formula evaluated in the order the formula is calculated.

57) 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).

58) Is there a quick way to see the formulas rather than the values ?
(Ctrl + ') - This is a single left quote, not an apostrophe.

59) What is a Worksheet Function ?
A function provides a convenient way to use a common calculation.

60) 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.

61) Can you describe the IF function ?
It checks whether certain conditions are true or false.


62) 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.

63) 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" 

64) 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 these shortcut keys instead:
(Ctrl + ;) - inserts the current date
(Ctrl + Shift + ;) - inserts the current time
(Ctrl + ;) space (Ctrl + Shift + ;) - inserts the current date and time

65) What function would you use to calculate the difference between two dates ?
DATEDIF - Returns the number of units between two dates.


66) Can you describe the OFFSET function ?
Returns the cell value which is an offset from a given cell reference.


67) Can you describe the MATCH function ?
Returns the position of a value in a list, table or cell range.


68) Can you describe the INDEX function ?
Returns the value from a cell range which is the intersection of a row and a column.


69) Can you describe the COUNTIF function ?
Returns the number of non blank cells that satisfies one condition.
Often referred to as conditional counting.


70) Can you describe the SUMIFS function ?
Returns the total value of the numbers that satisfies multiple conditions.
Often referred to as conditional summing.


71) Can you describe the INDIRECT function ?
Returns the text string of the contents of a given cell reference.


72) 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)

73) Are there any built-in worksheet functions that are volatile ?
CELL and INFO are also volatile but it depends on their arguments.

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


75) 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.

76) 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

77) 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 - returns the value in a row that matches a value in the top row of a table.

78) When would you use a combination of the INDEX and MATCH functions ?
If you wanted to lookup a value that was not in the first column.

INDEX('col_return', MATCH('match_item', 'col_data',0), 1)  

79) When would you use a combination of the OFFSET and MATCH functions ?
If you wanted to lookup a value that was not in the first column.

OFFSET('col_header', MATCH('col_data','table',0) -1, col_offset, 1, 1) 

80) 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.

81) What are the advantages of using Named Ranges ?
You can insert or delete cells, rows and columns and the cell references remain intact.

82) 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.


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.


83) 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.

84) 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.


85) 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.

86) 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

87) What is a Chart ?
A chart provides a visual representation of data found in a table.

88) 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.

89) Can you give the name of 4 different built-in chart types ?
Column, Line, Pie, Area

90) 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

91) 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

92) Can you describe the advantages of using a table rather than a named range ?
Columns can be referenced using column names in a Table but in a Named Range you have to use an index number.
Sorting and Filtering can be easily done using the drop-down menus in a Table but in a Named Range you have to use the Ribbon commands.

93) How can you create a table ?

94) What is the shortcut key for togglying the autofilter on a table ?
(Ctrl + Shift + L)

95) 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.

96) 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.

97) 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.

98) 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.

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


100) What are the three different report layout types for a pivot table ?
Compact -
Outline -
Tabular -

101) How would you disable automatic sorting in a pivot table ?
Goto > More Sort Options > Right Click 'pivot tables' > Sort Menu > More Options > sort automatically

102) How would you prevent column widths from changing automatically when a pivot table is refreshed ?
pivot table options > enable preserve formatting > auto format

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

104) What is PowerPivot ?
This is an add-in that provides an extension to the built-in pivot table functionality.

105) 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".

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

107) 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 -

108) What is a Slicer and when would you use one ?
Added in 2010.
A slicer is an embedded object that provides a visual interface for filtering data in Tables, Pivot Tables and Cube Functions.

109) What is a Macro ?
A macro can be defined as lines of code that can be easily run multiple times.
When you have a repetitive task you can use a macro to quickly repeat the steps.

110) How would you run a macro that has been saved in the active workbook ?
Developer Tab, Macros

111) 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.

112) Can you assign a macro to a shape ?
Yes. Right click and select Assign Macro

113) If a VBA macros was running and needed to be interrupted what keys would you press to interrupt it ?
(Ctrl + Break)
(Ctrl + Shift + Break)

114) 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

115) What is the difference between ThisWorkbook and ActiveWorkbook ?


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

116) What is the difference between UsedRange and CurrentRegion ?


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

117) 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.

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

Sheets(2).Visible = xlSheetVisibility.xlSheetVeryHidden 

119) 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

120) 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( _
End Function
Public Function DAY_OF_WEEK2() As String
End Function

121) 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

122) What is the Application.Union method ?
Returns a range object that represents the union of two or more ranges.


123) What is the Application.Intersect method ?
Returns a range that object that represents the rectangular intersection of two or more ranges.


124) 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

125) 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 can be replaced with the Workbook_Open event in 90% of cases.

126) Can you compare the Auto_Open event with the Workbook_Open event ?
When you set Application.EnableEvents = False the Auto_Open event still runs.
The scope of Auto_Open does not have to be Public (it can be changed to Private).
Holding down Shift when opening the workbook will disable both Auto_Open and Workbook_Open.
If you open the workbook using automation the Auto_Open event is not triggered.
You can call the Auto_Open function manually using Workbook.RunAutoMacros
Workbook_Open is called before Auto_Open if both events exist.

127) 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.

128) 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.

129) 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.

130) 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

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

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

132) 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.

© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrev