Public Courses | Spreadsheet Architecture

Excel: Professional Foundation or Power?

Test your knowledge

Below 75% you should attend the Excel Professional Foundation User course:

Score Over 75% and you will be OK to attend the Power course.

DO NOT CLICK BACK - this will clear any answers you have selected to that point.

In the following questions, please select the best answer for the question.

It is possible there is more than one correct answer.

1. Structure: When setting up an important spreadsheet which approach do you adopt?

Keep it in a working directory until finished - then name it
Select a descriptive file name and tab structure and put in an explanations tab
Build the spreadsheet as I go
Involve colleagues from the outset
I don't build big spreadsheets

2. Standards: In new important spreadsheets the approach I adopt is:

I focus on building a great spreadsheet rather than following any rules
I just build it - and sort it out later
To use a few agreed standards but each one is different
To adopt a clear set of policies and naming conventions always.

3. Formula: Which is the correct construction to compute a total of the cells in range B5 to G7?

=+B5...G7
=total(B5..G7)
=sum(B5:G7)
create a range of the cells B5 to G7 - then use =rangename
=(+B5:G7)

4. Quick Find: What is the best way to set up links between tabs to allow your users to move quickly between cells where you put important information?

Put a formula into a cell that takes the user to a target cell
Add a link to the menu ribbon
Put a description in the target cell - in other tabs put a formula that links to that description
create a macro that moves the user to a target cell
Insert buttons that use macros to move users to locations in the tabs

5. Formula: Which is the correct construction to compute the total capital and interest due at the period end based on Capital in B3, Annual Interest rate D6, period in days G11?

=(B3*D6*G11)/365
=B3+(B3*D6*g11/365)
=B3 +(B3 x D6 x G11/365)
=(G11/365)*(B3+D6)
=(1+D6)*B3*G11

6. Formula: Which is the correct construction to compute the formula A cubed plus Square Root of B, minus C?

=(A*3) + SquareRoot(B-C)
= (A*3)+SquareRoot(B)-C
= (A^3)+SQRT(B-C)
= (A^3)+SQRT(B)-C
=(A power3)+SQRT(B-C)

7. Looking Better: What is the best way to format cells in a standard way?

Point to the cell: Right Click: Choose Format Cell
Point to the cell: choose a style from styles menu in ribbon
Set up standard styles - then ensure other people adopt the rules
Select from Menu - Format - Style
Write a company procedure - publish and everyone will do it

8. Lists: You use a list of locations regularly. How do you access these as a list, easily?

Type them in
Set up a list - use VLookup to find them
Set up an Index/Match to find them
Create a Custom List in File - Options - Advanced
Set up a data validation list - Copy elsewhere as required -

9. Formula: What is the correct formula to add 10 days to a date in another cell (say cell B7)?

= B7 + 10
= B7 + 10*24
=date(B7) + day(10)
=day(B7) + day(10)
=day(B7) + 10

10. Format: What is the quickest way to format one cell as a date?

Ribbon menu> date>format>enter
Right click>format cells > select as required
Ribbon menu (number micro arrow)> select format
Ribbon menu styles> select as required
Special Copy Format from another cell

11. Insert: Which of the following does NOT result in a row being inserted in your spreadsheet?

Alt-i-R
RibbonMenu-Insert row
RibbonMenu-Home-Insert-Row
Click on the row number-right click-insert
On any cell-right click-insert-R{enter}

12. Copy: You have some source calculated data, and you wish to copy the values of the range to elsewhere in the spreadsheet:

In the target cell: press = and link to original data: copy to other cells as required
Highlight source data: create a range name: in the target sheet paste the range-name
Highlight source data: Ctrl+c: in the target spreadsheet Ctrl+v
Highlight source data: Ctrl+c: in target spreadsheet insert rows
Highlight source data: Ctrl+c: in target spreadsheet - Paste Special Values

13. You have several pieces of data in different parts of the spreadsheet. What is the best way to create a chart of this information?

Range value data to a new location: create a range name of it: Ribbon menu- insert - chart - select as appropriate
In a clear part of the tab with the data: Highlight the data: Ribbon menu -insert - chart - select: move the chart to where required
Highlight the data: Ribbon menu - insert - chart - select - add chart to new tab
Highlight the data: Ctrl+c: Ribbon menu - insert - chart - select - add chart to new tab
On new tab: Link cells in new data-set to original cells: Highlight: Ribbon menu - insert - chart - select - add chart where required.

14. Formula: Your formula in G7 (shows Jan. sales total for product A) is computed from B7 (constant prices) * G2 (the month sales volume). What is the best construction of this formula so you can copy it across the columns for other months and also downwards for other products?

B7*G2
B7*$G2
$B7*$G2
$B7*G$2
$B$7*G$2

15. Insert: You created the first month of a forecast with 50 row items using a fixed price on the row, and factors at the head of the column, plus several sub-totals. What is the best way of multiplying up the column details for future forecast months?

Copy each cell into future months
Highlight the cells and drag them to the right
Highlight the column - Copy - then right click on the column and Insert
Highlight the column then Ribbon menu-Copy-{enter}
Drag each cell to the right.

Please provide your email to receive your results

Please wait ...
This may take a few minutes.

I'm a human I'm a robot
Username:
Password:
	Reset Password

Reset Your Password
I'm a robot Email me

Enter your email address:

Duplicate
Into Portal
X-Group:
As Name:

Delete this In-Group
Include X-Group:

X-Group
Create new group
Add to existing