Excel-2007-2016 - Access Analytic

Welcome to your free Excel 2007-2016 test

I have an Excel 2003 file that uses the range names Qtr1, Qtr2 and Qtr3. Why does my file produce incorrect results in Excel 2007+?

Excel 2007+ range names must refer to months or years, not quarters

In Excel 2007+, QTR1, QTR2 and QTR3 are cell references

Excel 2007+ does not allow a mixture of alpha and numeric characters in range names

Excel 2007+ automatically assigns these range names when it recognises quarterly data

What formula would you enter in B16 to show the total for just the person selected on only the date entered?

=SUMIFS(Sales,Name,B15,SaleDate,B14)

=SUMIFS(Sales,SaleDate,B14,Name,B15)

=SUMPRODUCT((SaleDate=B14)*(Name=B15)*Sales)

All of the above will produce the correct answer

The Developer Ribbon is hidden by default. Where would you go to make it visible in Excel 2010+?

File > Options > Add-ins > Excel – Add ins

File > Options > Customise Ribbon > New Tab (Name as Developer)

Right Click on the Ribbon > Customise the Ribbon > Main Tabs > Developer Checkbox

Right Click on Ribbon > Customise Quick Access Toolbar > Choose Commands from > Developer tab

What has happened to Excel?

It's operating in Page Layout view

It's operating in Word compatibility mode

It's operating in Page Break Preview mode

It's showing the sheet in Print Preview mode

Why is there such a big gap between the Ribbon and Excel cells?

A long formula has been entered, causing the Formula Bar to automatically re-size itself

Excel has crashed

Excel is operating in 2003 compatibility mode

The Formula Bar has been re-sized to make it taller

(i) Why do these dots show in the Name Box and (ii) what can you do about it?

(i) they indicate a dynamic range name (ii) change the range name to static

(i) they indicate a worksheet range name scope, rather than Workbook scope (ii) change all names to Workbook scope

(i) they indicate a range name that is based on a table (ii) change the range name to use cell references instead

(i) they indicate the range name is too long to be displayed (ii) drag the edge of the Name Box to make it wider

The headings for columns A, B and C have been changed. How has this been done?

Using the R1C1 reference style

Using an Excel table. This is what happens when you scroll down the page.

Using Find and Replace advanced settings

By applying Excel's new Conditional Formatting rules

The range A2:C12 has consistent cell background and font formatting across all cells. Why do some cells appear different?

A conditional formatting rule has been applied using the formula =COUNTA($A2:$C2)=COLUMNS($A2:$C2)

A dynamic range name has been used

Volatile VBA Macro formatting is being used

These cells are formatted using a user-defined VBA function

How do you restore the fill handle? (the little + that is usually in the bottom right-hand corner of a cell)

Unprotect Sheet

Unprotect Workbook

File > Options > Formulas

File > Options > Advanced

10.

Even though the word “Heading” appears to be highlighted it is not showing up in the formula bar, why not?

The formula bar has been formatted to show white text on a white background

The word has been typed in A1 and then the 3 blue cells have been formatted using Centre Across Selection

There is a glitch when using certain fonts

B1 is not actually highlighted, it’s just a border around the cell

Be sure to click the button below to check your answers! By checking your answers, you agree to receive communications from Access Analytic. You can unsubscribe at any time. Click here to review our Privacy Policy.

First Name

Time's up

Excel Office Scripts for Effortless Data Movement
Dynamic Data Validation with Tables in Excel
SquareWars – A Fun Way to Learn Excel 365
Unleash ChatGPT’s Power in Office 365
Power Query Challenge
Excel Skills Assessment

Related Posts:

Share This Story, Choose Your Platform!