Skip to content

AMAZING Power BI & Excel CALL 08 6210 8500

Pay my invoice
Contact Us

Amazing Power BI
Amazing Excel
Solver
Microsoft Fabric
Insightful Training
Resources
Blog
About Us

New Excel Features

Home/New Excel Features

Welcome to your free Excel test

1.

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

NewExcelFeatures q6

(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

None

2.

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

NewExcelFeatures q9

Unprotect Sheet

Unprotect Workbook

File > Options > Formulas

File > Options > Advanced

None

3.

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

NewExcelFeatures q8

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

None

4.

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

NewExcelFeatures q2

=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

None

5.

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

NewExcelFeatures q3

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

None

6.

What has happened to Excel?

NewExcelFeatures q4

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

None

7.

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

NewExcelFeatures q5

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

None

8.

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

NewExcelFeatures q7

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

None

9.

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

NewExcelFeatures q10

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

None

10.

Which Excel formula correctly retrieves the age of "Alice" using the XLOOKUP function?

(1) =XLOOKUP("Alice", A2:A5, B2:B5)

(2) =XLOOKUP(A2:A5, "Alice", B2:B5)

(3) =XLOOKUP("Alice", B2:B5, A2:A5)

(4) =XLOOKUP(B2:B5, "Alice", A2:A5)

None

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

Email

Time's up

Related Posts:

How ChatGPT’s Premium Features are Revolutionising…
Unleash the Power of Cloud-Based Budgeting &…
Complete guide to making faster, better decisions with…
Navigating the New Normal: Why CFOs are embracing Data…
Mastering Dynamic Measure Formatting in Power BI |Part 3:…
Excel Office Scripts for Effortless Data Movement

Jackie Meddows-Taylor2024-10-04T10:56:06+08:00

Share This Story, Choose Your Platform!

Facebook Twitter LinkedIn Email

Subscribe

* indicates required

Email *

First Name *

Last name *

Business name

Country

State

Newsletter Sign Up

First name(Required)

Email address(Required)

CAPTCHA

Δ

Categories

Arrays
Auditing
Budgeting/Forecasting
Business Intelligence
CFOs
Charts
ChatGPT
Data Handling
Excel
Excel 2007
Excel 2013
Excel 2016
Financial Modelling
Formatting
Functions
Keyboard
Microsoft Fabric
Mouse
Office 365
Pivot Tables
Power BI
Power Pivot
Power Query
Printing
Productivity
Teams
Uncategorized
VBA – Macros

Microsoft Power BI
Microsoft Excel
Solver Software
Training & Courses

Free Resources
Blog Articles
About Us
Pay My Invoice

Contact Us

Suite 143, Level 1, 580 Hay Street Perth, WA

Phone: (08) 6210 8500

Do you want to optimise your business efficiencies?

Download our 2 FREE Solver white papers on Budgeting Process and Monthly Reporting today.

First Name(Required)

Email(Required)

CAPTCHA

Δ

Copyright 2024 Access Analytic | All Rights Reserved | Privacy Policy | Engagement Terms & Conditions

Brought to you by: Huddle Up Creative

LinkedIn Twitter Facebook Rss YouTube Email

Jeff is the founding Director and Principal Business Analyst at Access Analytic Solutions. He has over 20 years’ experience in chartered accounting, business management and consulting.

Jeff holds a Masters in Applied Finance and is a Certified Information Systems Auditor. He is highly regarded and recognised around the world as an expert in the field of financial modelling and Power BI reporting. His achievements have been recognised by his peers and he is a Fellow of both the Financial Services Institute of Australasia and the Australian Institute of Management. Jeff has extensive experience in best practice management reporting, data analytics, business analysis, and financial modelling and is also an entertaining international presenter and trainer on these subjects.

Jeff is the author of a number of white papers on financial modelling on subjects such as Financial Modelling Best Practices and Financial Model Auditing. He provides training for companies throughout Australia, New Zealand, Asia, South Pacific, the Middle East, and Africa.

Yury Tokarev is a highly accomplished finance professional and international trainer with over 25 years of experience in financial modelling and advanced analytics. As a Director at Access Analytic, Yury specialises in leveraging data-driven insights to enhance strategic decision-making through sophisticated analytical tools, including Power BI and AI.

Yury’s extensive background includes developing complex financial models across various industries, automating Excel-based solutions for efficient data management, and designing Power BI reports that integrate a variety of metrics, including financial, people, and ESG indicators. His expertise uniquely positions him to address the evolving needs of senior financial professionals as they embrace digital finance and sustainability initiatives.

Prior to joining Access Analytic, Yury gained significant experience in investment banking, corporate finance, and business services and advisory. These roles provided him with a deep understanding of strategic financial planning, complex financial transactions, and advisory services, which he now leverages to deliver cutting-edge solutions in digital finance transformation and analytics.

As an engaging and interactive trainer, Yury has delivered numerous workshops and training sessions globally, helping professionals elevate their understanding of financial modelling, Power BI, and AI analytics. His teaching approach combines practical insights with real-world applications, empowering participants to effectively apply advanced analytics and AI in their strategic roles.

Wyn Hopkins
B.Sc. (Hons) Accounting & Financial Management, Microsoft MVP, Excel Expert (MS), Author

Wyn has been awarded MVP status by Microsoft every year since 2017 and is the author of the book “Power BI for the Excel Analyst”. He is known worldwide for sharing his knowledge via the Access Analytic YouTube channel with over 5 million views and 90,000+ subscribers.

He gained his Chartered Accountant qualification at PwC in 2000 and has extensive Business Analyst experience with HBOS and Barclays in the UK plus multinational clients in Perth.

Wyn is skilled in Excel and Power BI development, Power Query automation, management reporting and is an experienced Excel and Power Bl trainer.

Ready to kickstart your career?

To express your interest in our Internship Program, please fill out the form and attach your cover letter and resume, and we’ll get back to you shortly!

First Name(Required)

Last Name(Required)

Email address(Required)

Phone number

Why do you want to intern with us?(Required)

Resume(Required)

Drop files here or

Max. file size: 300 MB.

Cover letter

Drop files here or

Max. file size: 300 MB.

Consent(Required)

I confirm that I am a student currently enrolled at a university in Perth and residing in Perth.(Required)

Δ

First Name(Required)

Email(Required)

So we can contact you with updates - you can opt out any time.

Δ

First Name(Required)

Email(Required)

So we can contact you with updates - you can opt out any time.

Δ

First Name(Required)

Last Name(Required)

Email Address(Required)

Topic

Phone Number

Business Name

Preferred method of contact

Email

Phone

Message

CAPTCHA

Δ

First Name(Required)

Last Name(Required)

Email Address(Required)

Phone Number

Business Name

What is your enquiry in regards to?

Message

Consent

I agree to receive communications from Access Analytic. You can unsubscribe at any time. Click here to review our Privacy Policy.

CAPTCHA

Δ