Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

JW - Collection > Excel #1 > Flashcards

Excel #1 Flashcards

1
Q

F2?

A

Activates the editing mode for an active cell.
Brings back blue moving box showing range in question.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

CTL + 1

A

Formatting options.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

F7

A

Spell check
*Note use of CTL = SHF = Down!

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Activate PIN listing

A

Go to file

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

ALT + Q

A

Open search bar.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

CTL + F1

A

Minimise and maximise ribbon.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Right-click?

A

Mini Toolbar + 19 options

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Movements about a table of data?

A

Tab, Enter, Shift Tab (Back). Shift Enter (Up).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Box and move about parameters?

A

Mouse square, CTL + Enter on first cell, Move about square.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

F1?

A

Activates Help panel.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

File?

A

Backstage, Admin area.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

The seven manual controls?

A

CTL + Up/Down/Left/Right arrows, CTL+ SHIFT Down/Right, CTL + A, CTL + Enter.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Non-contagious cell selection?

A

SHIFT + Arrow, SHIFT + F8, SHIFT + Enter, SHIFT + F8….

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Name?

A

Name of formula or named range incorrect / absent.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Ref?

A

Refer to cells that no longer exist.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Div?

A

Trying to divide the number by zero.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Absolute reference?

A

F4 while active cursor in cell, Alternating.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Auto-sum?

A

ALT + =

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Series creation, row or column?

A

Right=click, One, One cell down, One cell back up, Right-click, Fill series.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Flash-Fill criteria?

A

Adjacent, CTL + Enter to stay in top cell, CTL + E to flash fill down.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Paste individual table components?

A

F3

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Go to table components?

A

CTL + G

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Format painter rules?

A

Click once for one use, Click twice for continual use until switched off.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

Add manual break in cell (Partick Thistle)?

A

Place cursor, ALT + Enter

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

UNIQUE?

A

Single list of unique items from longer list of repeating items (Dynamic array).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

Insert Filter?

A

CTL + Shift + L

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

Table creation?

A

CTL + T

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

Start and End of worksheet?

A

CTL + Home, CTL + End.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

Save as?

A

CTL + S, F12.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

Move between worksheets?

A

CTL + Page up. CTL + Page down.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

Delete worksheets?

A

CTL + Left-click to select pages, Right-click and select delete to remove.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

Double-click in cell?

A

Edits, Reveals formula.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

Fractions?

A

Space needed between number and fraction to work. 1 1/4 = 1.25 in cell.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

Automatically generate sequential?

A

CTL + Autofill (Drag down) every line.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

Zoom?

A

CTL + Mouse wheel.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

Add a column?

A

CTL + SHIFT + Plus, CTL + Minus.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

Fill down (List?)

A

CTL + D.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

Fill across (List?)

A

CTL + R

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

Insert cell comment?

A

SHIFT + F2.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

Same increase in size drag of box?

A

CTL + Drag.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

Round function? (Penny-rounding)?

A

=ROUND(E2 - (F2 + 1), 2)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

Hiding values in a cell while others remain visible?

A

Formatting - 3 x ; ; ;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

Copy Worksheet?

A

CTL + Drag worksheet to copy.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
44
Q

Move Worksheet?

A

Drag worksheet to move.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
45
Q

What do pivot tables allow?

A

(1) Quickly summarise large amounts of data. (2) Pivot data about. (3) Key Metrics, Trends, Issues, Successes and Failures.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
46
Q

Importance of cleaning data?

A

Data does not always import as expected. Non-clean data = Non-accurate data!

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

Removing blank rows (Cleaning data #1)

A

Home > Editing > Find and Select > Go to special > Select blanks > Delete all.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
48
Q

Removing duplicate rows (Cleaning data #2)

A

Click somewhere within the data set. Data > Data Tools > Remove duplicates. Note - Only if an exact duplicate.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
49
Q

Clearing formatting (Cleaning data #3)

A

Home > Editing > Clear. Formats, Contents, Comments, Notes and Hyperlinks.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
50
Q

Applying number formatting to pivot tables (Cleaning data #4)

A

Column by column when it comes to pivot tables. CTL + 1. Select from number tab. Use main ribbon also.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
51
Q

Changing the case (Cleaning data #5)

A

=UPPER(A1). =LOWER(A1). =PROPER(A1). Use HELPER COLUMN + Paste special to (replace).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
52
Q

Removing Non-Printing Characters and Spaces (Cleaning data #6)

A

=CLEAN(A1) = Non-printing characters. =TRIM(A1) = Spaces May have non-printing characters / blanks you can’t see.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
53
Q

Merging and Splitting columns (Cleaning data #7)

A

(1) Flash Fill. (2)
=CONCAT(John,” “,Walsh)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
54
Q

Convert numbers stored as text (Cleaning data #8)

A

Green triangles, Select all, Highlight all, Convert to number.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
55
Q

Finding and replacing text (Cleaning data #9)

A

(1) CTL + F = Find and Replace. (2) Home > Editing > Find and Select > Replace.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
56
Q

Spell Check (Cleaning data #10)

A

F7. Should do every time as pivot tables will display as separate items. Review > Proofing > Spelling also.

57
Q

Benefits of putting data into table prior to pivot table?

A

(1) Auto-expands. (2) Table Design ribbon and options. [Pivot table analyse also]. (3) Filters and sort functions applied

58
Q

What should every table have?

A

A name!

59
Q

4 option panes on a pivot table?

A

Filters, Rows, Columns, Values.

60
Q

Locking the pivot layout?

A

Pivot Table Analyse Ribbon > Pivot Table > Options = [Printing] [Data] [Alt Text] [Layout & Format] [Totals & Filters] [ Display].

61
Q

[Pivot Table Analyse]

A

Pivot Table/Options, Active Field, Group, Filter (Slicer/Timeline), Data, Actions, Calculations, Tools. Show - Field List / +/- buttons / Field Headers.

62
Q

[Pivot Table Design]

A

Layout, [Subtotals/Grandtotals/Report Layout/Blank Rows] Style Options, [Row Headers/Column Headers/Banded Rows/Banded Columns], Pivot Table Styles.

63
Q

Methods of Aggregation (Pivot Tables)

A

[Value Field Settings] - Right click or down arrow in miniature.

64
Q

Grouping and Ungrouping Data (Pivot Table)

A

Pivot Table Analyse > Group > Grouping options.

65
Q

Handling Cells with No Data (Pivot Table)

A

Pivot Table Analyse > Pivot Table > Options > Layout - Format > For error values show > For empty cells show.

66
Q

Sub Totals and Grand Totals (Pivot Table)

A

Design Tab > Subtotals & Grand Totals

67
Q

Choosing a Report Layout (Pivot Table)

A

Design Tab > Report Layout - (1) Compact (2) Outline (3) Tabular

68
Q

Modify Pivot Table Styles (Pivot Table)

A

Cannot modify pivot table styles. But CAN DUPLICATE!

69
Q

Sorting Pivot Table Data (Pivot Table)

A

All underneath drop down arrows -
Benefits of sorting
(1) Organises our data in logical order. (2) Increased readability. (3) Analyses the data
ORGANISES / READABILITY / ANALYSES

70
Q

Filtering Pivot Table Data (Pivot Table)

A

Again, all underneath drop down arrows. Series of boxes.

71
Q

Dynamic (Today’s date)

A

=TODAY( )

72
Q

Dynamic (Today’s date and time)

A

=NOW( )

73
Q

Fixed (Today’s date)

A

= CTL + ;

74
Q

Connecting Slicers & Timelines (Pivot Tables)

A

Pivot Table Ribbon > Slicer > Report Connections - Use names of charts > Tick boxes > Connected!

75
Q

Hyperlink?

A

CTL + K

76
Q

Data Validation?

A

Data > Data Tools > Data Validation

77
Q

Protecting a worksheet?

A

(1) By default all cells are locked. (2) Select cells & CTL +1 to unlock (2) Review > Protect Group > Protect Workbook. *Summary - We need to select the cells we want to allow users to type into and unlock them prior to protecting the sheet.

78
Q

[2] You can save workbook in different formats!

A

You can save workbook in different formats!

79
Q

[3] You can share the workbook!

A

You can share the workbook!

80
Q

[1] Inspect Workbook?

A

File > Options > Inspect workbook

81
Q

Designing better spreadsheets? (3+3)

A

(1) Adopt a standard and implement it. (Colours, Fonts & File names). (2) Identify your audience. (3) Include welcome sheets with instructions and key. (4) Separate your data (Source data/Calculation/Analysis should all have separate worksheets). (5) Design for longevity (Use tables / Don’t hardcode into cells.) (6) Use consistent and clear structure. (Colour code / Data validation / Protection).

82
Q

Basic Logical Operators

A

=, >, <, >=, <=, <>

83
Q

Basic Logical Statement IF

A

(1) =IF(B5>=$H$13,”Approval”,”Ok”) (2) Use of IF allows us to attribute more meaning to a logical statement at its most basic level.

84
Q

Basic Logical Statement AND

A

(1) =IF(AND(B23>=$H$22, C23 >=H23),”Pass”,”Fail”) (2) Needs to pass test 1 and test 2 to pass.

85
Q

Basic Logical Statement OR

A

=IF(OR(B32>=SHS31,C32>=$H$32),”Pass”,”Fail”)

86
Q

2 Points on Logical Formulas / IF Formula

A

(1) IF allows us to attribute more meaning to results by wrapping “””” in an F statement.

(2) Text in formulas must always be in quote marks .

87
Q

IFS Function

A

(1) Same as Nested IFS (IF statements inside other IF statements] but a much more efficient construction. (2) =IFS(G4=$J$5,$K$5,G4=$J$5,$K$6, ….. ) If G4 = 1, Bonus will be £100.

88
Q

Conditional IFS / Multiple criteria (SUMIFS, COUNTIFS, AVERAGEIFS)

A

=SUMIFS(Sum Range, Criteria Range #1, Criteria Range #2), £, Company Name, Microworld.

Going one step back - SUMIF (Range, Criteria, Sum_range)
A10:A1207, BS A25, AF10:AF1207 When you find 2040 within A10:A1207 (List of codes) return sum value in AF10:AF1207

Moving to one side,
VLOOKUP will return a single value based on a defined criteria
SUMIF will SUM all values that meet a certain, defined criteria

89
Q

Error Handling with IFNA and IFERROR.

A

Ex. =IFNA(Formula,”No Bonus”) IFERRORS Ex. =IFERROR(Formula,0) + Drag down

90
Q

Insert?

A

(1) Pictures (2) Shapes and textboxes (3) Icons and 3d models (4) SmartArt (5) Screenshots

91
Q

Conditional Formatting

A

Additional functions in Excel if you are a business analyst. #1
Conditional formatting.

(1) Highlight cell rules (2) Data bars (Left to right) (3) Colour scales (4) Icons sets.

If applying rule to a whole column, use format option box and select option to apply same rule/format all the way down.
If applying to a whole series of columns,
[1] Highlight all data - CTL + SHF + Down.
[2] New rule
[3] Formula to determine which cells to format
[4] =$B5 (First cell top corner) > 25,000,000.

  • $B5 allows this to work
92
Q

Sorting

A

(1) Single list sorting (2) Multi-level sorting (3) Sort using a custom list.

93
Q

VLOOKUP

A

(1) Lookup Value (2) Array (3) Column index number (4) True - Approx. (5) False - Exact.

Look up this, here in this column, and return value in second column (across from it).

Here, the word ‘value’ is key!

94
Q

Ranges vs Tables?

A

(A) Ranges allow non-contagious cells (B) (1) Tables do not but auto-expand, (2) have ++ Function & Design capabilities and (3) Sort and Filter functions.
AUTO-EXPANDS / DESIGN / FILTER

95
Q

XLOOKUP

A

First 3 mandatory, Second 3 optional. (1) Lookup value (2) Lookup array (3) Return Array (4) If not found “Not found” (5) Type of match (6) Search mode chosen. Note - If using table names in the land of square brackets.

96
Q

OFFSET function?

A

Used on its own but often with others (SUM & COUNTA) for powerful results. Brings back the call or range following ‘directions’ there. =SUM(OFFSET(B3, COUNTA(B4:B15),0,-6,1). Ex. Sum of the last 6 months of sales.

97
Q

Data Sort (Advanced Sorting & Filtering)

A

4-square sort, LEVELS. Up / Down, Cell colour, Font colour.

98
Q

Sort by Custom List (Advanced Sorting & Filtering)

A

Type out custom list, File >, Use custom list > Option to sort by custom list.

99
Q

SORT & SORTBY (Advanced Sorting & Filtering) 102, 117, 126

A

Dynamic arrays formula - Use it (1) so we can sort and paste anywhere in spreadsheet and (2) Use it with other formulas (Unique and extract).
SORT - Formula to sort list.
SORTBY - Sort by multiple columns / Sort a list on a column not included in sort array. Note - Use Tables for dynamic update.

The difference between SORT and SORTBY - is that instead of sorting by ascending or descending order we can (1) choose several ranges or arrays to sort by instead. (2) Sort by an array or range outside the selected data.

Using SORT. Sort Index refers to column number.
Dynamic update if using formula. East Wing. If addition at end of list, use table.
Note that SORT is still an ascending and descending selection. Columns have to be adjacent.

Using SORTBY. Sorting by multiple columns. Array 1 (2nd option) is a whole column selection.

100
Q

Advanced Filter ( Advanced Sorting & Filtering )

A

List out the criteria you require (in format). Copy to new worksheet. ex. Asia / 77,000.

101
Q

1E+40

A

Roman, Value too large! Mystery solved.

102
Q

N/A?

A

Data not available.

103
Q

NUM?

A

Contains invalid numeric values.

104
Q

VALUE?

A

Something wrong with formula you have typed (or) Something wrong with cells you are referencing.

105
Q

Data Validation (Main)

A

I Data Validation give user CONTROL over what goes INTO worksheets. II Use of UNIQUE to generate single use names for data validation. III Use of TABLES. To add new values automatically CREATE TABLE.

106
Q

Data Validation (Other Uses)

A
  1. Whole Number (Days of week) 2. Date (Over 18?) 3. Text Length (Airport code).

Uses validation box and drop down WITHIN but uses WHOLE NUMBER OPTION, DATES OPTION or TEXT LENGTH OPTION.
Addition option also includes both INPUT MESSAGE and ERROR MESSAGE.

107
Q

Dynamic Array. Advantages?

A

(1) Fewer formulas (2) Formulas much easier to write (3) Less errors (4) = More trustworthy spreadsheets (5) Solved some hard problems in Excel.

108
Q

Dynamic Array. What is it?

A

Enter one formula and get multiple results!

109
Q

Dynamic Arrays. 6 introductions in 2018?

A

(1) SEQUENCE - Generate an array of sequential numbers. (2) RANDARRAY/RANDBETWEEN - Generate an array of random numbers (3) UNIQUE - Extract unique values from a list or range (4) SORT - Sort range by column (5) SORTBY - Sort range by several columns or Sort by column outside array. (6) FILTER - Filter data and return matching records.

110
Q

Using wildcards - The asterisk and The question mark

A
  • The asterisk means that any number of wildcard letters can occur between the letters. ex. C*g finds Containing
    ? The question mark means that only one wildcard letter can occur between the letters. ex. S?d finds Sid
111
Q

Quick Analysis.

A

Quick analysis provides a simple way to do simple things.
(1) CTL + Q
(2) CTL + A on range / Table = Quick analysis in corner.

(1) Conditional Formatting (2) Charts (3) Totals (4) Tables (5) Sparklines

112
Q

Convert a table back into a range

A

(1) Click anywhere inside a table (2) Click Table Tool > Design > Tools > Convert to Range.
Also - Right click > Table > Convert to range.

113
Q

Standard Formula, CSE ARRAY, Dynamic array

A

CSE (CTL + SFT + Enter) allows (1) us to combine multiple calculations in one formula and (2) Uses { } brackets to tell us. { Ex =Sum(B11:B16*C11:C16) }

114
Q

RANDARRAY

A

=RANDARRAY(4,4,5,1000,TRUE/FALSE) = Generate random numbers 4x4 = 16 which are between 5 and 1,000 which are wither whole numbers (integers) or decimals.

Ex. Jump Order from list of names, Copy and paste special, sort values smallest to largest = Jump Order!

115
Q

RANDBETWEEN

A

=RANDBETWEEN(25000,5000) = Generate random listing of salaries. Random salaries for people.

=RANDBETWEEN(DATE(2005,01,01),DATE(2021,12,31)) = Generate random dates between two dates. Random dates for people’s start dates.

116
Q

XLOOKUP (a dynamic array formula) over INDEX and MATCH

A

More powerful, more flexible plus the notation is a lot easier.

117
Q

2-way look up. Look up information using 2 pieces of criteria as opposed to 1.

A

Months along top, Companies to side.

  1. Create Table - CTL + T
  2. Data Validation on both above using Transpose for Horizontal.
  3. Named Ranges x 3. These are - Months, Travel companies, Data.

=INDEX(Data,MATCH(B13,Months,O),MATCH(A14,
Companies,0))
See INDEX / MATCH / MATCH

=XLOOKUP(B13,Months,XLOOKUP(A14,Companies,Data,”Not found”0,1))
See XLOOKUP, XLOOKUP

118
Q

CHOOSE

A

=CHOOSE(A2,”Red”,”Blue”,”Red”) - Where A2 is either 1,2 or 3! In place of a VLOOKUP.

The (F3) 2 here is a cell reference where you enter 1 to pick out first item, 2 to pick out second item and 3 to pick out third item.
VLOOKUP can also be used but if table disappears then formula and working does also!

Employee - Code - Department (?) Table Codes & Departments.
With CHOOSE we are specifying all of our arguments within one function and we are not referring to external table or external data. Therefore no issue if table deleted while using look up function.
Also just want results, not tables of data.

For CHOOSE ( ) you need to specify an index number to evaluate, which can only be a whole number (1, 2, 3 etc.) whereas for SWITCH ( ) you can specify an expression to evaluate “ “

119
Q

SWITCH

A

=SWITCH(F3,(9,”Excellent”,8,”Very good”,7,”Good”)
The F3 here is a cell reference. Used in place of a lookup function. XLOOKUP.
With SWITCH we define our values within the formula. Therefore no issue if table deleted while using look up function.
Also just want results not tables of data.
Movies - Rating (?) - IMDB score Table has ratings and score.

For CHOOSE ( ) you need to specify an index number to evaluate, which can only be a whole number (1, 2, 3 etc.) whereas for SWITCH ( ) you can specify an expression to evaluate “ “

120
Q

Forecasting. Standard - Detail.

A
  1. Create forecasts 2. See trends 3. Predict the future - Always relies on time based data.
    X= Dates, Y = Values.

Line chart helpful as allows you to visually see what type of date you have and to select the appropriate function.

Ex. FORECAST.LINEAR (Date starts for forecasted data, X known (Values of data to date), Y known (Dates to date)
Drag down value. Insert Line Chart. Title. Format.

Ex. FORECAST.ETS (Date starts for forecasted data, X known (Values of data to date), Y known (Dates to date) + 3 more automatically, including seasonality.

Ex. FORECAST.ETS.CONFINT ((Date starts for forecasted data, X known (Values of data to date), Y known (Dates to date) + 4 more automatically, including confidence level (95%) and seasonality. Take value and add subtract and then place on line chart (Non-contagious collation).

121
Q

Forecasting. Express option - Forecast Sheets!

A

Data > Forecast Sheet.
Essentially, a set-up wizard.
Preview offered immediately.
Creates formulas automatically.

122
Q

Add in - FRED

A

Use of add-in to generate data to work with (UK option)

123
Q

SORT Function (Dynamic array) 102, 117, 127

A

Use it (1) so we can sort and paste anywhere in spreadsheet and (2) Use it with other formulas (Unique and extract).

=SORT(array, [Sort Index], [Sort Order], [By Row/Column])

Sort by country (ascending) and bib number (descending)
=SORT(A4:A15,{2,3},{1,-1},False)

Using SORT. Sort Index refers to column number.
Dynamic update if using formula. East Wing. If addition at end of list, use table.
Note that SORT is still an ascending and descending selection. Columns have to be adjacent.

Using SORTBY. Sorting by multiple columns. Array 1 (2nd option) is a whole column selection.

The difference between SORT and SORTBY - is that instead of sorting by ascending or descending order we can choose another range or array to sort by instead.

124
Q

Power Query

A

Power Query is a powerful BI tool, available in Excel that allows you to import data from different sources, clean and transform it and then reimport it back into Excel ready for analysis.
Query to the database allowing
(1) Updates with a click of a button.
(2) The process of acquiring and transforming data simple. [No TRIM, CLEAN, REPLACE,CONCAT, and Flash Fill]. Instead we use Power Query commands using a graphical user interface.
(3) Records all actions so we can easily back-track.

Steps
A. Importing data into the power query editor using GET and TRANSFORM
B. We clean and tidy our data
C. We re-export it out to Excel.

Option we have is to use standard housekeep formulas in Excel or to use Power Query.

125
Q

Power Query > Power Pivot > Power BI

A

Power Query > Power Pivot > Power BI
M language > DAX Language > Power Query & Power Pivot.

[Fetch > Reshape > Publish] - [Model > Relationships > Cube] - [Visualisations > Interactive experiences > Forecast analytics ]

126
Q

A Dashboard

A

A dashboard is where you gather together all different types of analysis and display them on one worksheet. Gives a really good idea of what is going on in the business. Ex. Sales dashboard. Interactive example.

Required.
A raw data set. Table / Pivot Table
A linked-through calculations page.
Charts used x3
(1) Combo boxes and (2) Charts and/or Pivot tables used.

127
Q

Combo Box Drop Down [Dashboard #1]

A

Combo Box Drop Down is key to whole dashboard.
Developer Ribbon > Combo Box [Form Control]

Draw Combo Box

Combo Box as a Title
(1) Unique List of years
2018 (a6)
2019 (a7)
2020 (a8)
(2) Selection
1, 2 or 3 (a12)
(3) Selected Year
=INDEX($A$6:$A$8, $A$12)

1 is now 2018, 2 is now 2019 and 3 is now 2020.

Developer > Properties >
(1) Input Range [$a$6:$a$8)
(2) Cell link ($a$12)
Now drop down menu is complete showing 2018, 2019 and 2020.
and if we look at [Calcs] page we see that any change in the drop down combo box on lead page now changes all formulas on [Calcs] page.

128
Q

Simple solution - In place of using Name Manager

A

Simply select the range with mouse and enter name in text box, Repeat over if required.

129
Q

CSV file

A

Comma Separated Value.

130
Q

SUMIFS and COUNTIFS [Dashboard #2]

A

=SUMIFS(Sales/Units sold column - CTL+SHF+Down, Country column - CTL+SHF+Down, Japan on Cells worksheet,
Year column - CTL+SHF+Down, Year cell selection - Combo box on Cells worksheet)
*Here it is SUMIF because we are totalling units sold, that is 5 in this line (Order), 3 in this line (order), etc.

=COUNTIFS(Products column - CTL+SHF+Down, Product on cells worksheet, Years column - CTL+SHF+Down, Year cell selection - Combo box on Cells worksheet).
*Here it is COUNTIF because we are totalling how many individual lines of orders there are, not totalling numbers between those lines.

131
Q

INDEX

A

2010 a11
2011 a12
2012 a13
2013 a14
2014 a15
2015 a16

BOX a20

a24 = =INDEX(A11:A16,a20)

Now when 1, 2, 3, 4, or 5 are entered into cell a20, a24 will show 2011, 2012, 2013, 2014 and 2015 respectively!

132
Q

INDEX and MATCH

A

Data Validation - Intermediate. Use of whole numbers.

Row #1 is days listed (A6-A12)
Row #2 is Numbers 1-7 corresponding with days before. (B6:B12)

We then have a pick a number box (E7) and a corresponding day (E8) where we are placing the INDEX & MATCH formula.

Formula in (E8) is =INDEX(A6:A12,MATCH(E7,B6:B12,0)
- match E7 entry (1 to 7) on the second column containing the corresponding numbers and return the corresponding value on the written day column!

INDEX - Returns a value or reference of the cell at the intersection of a particular row or column, in a given range. MATCH - Automates the finding of the row number / Returns the relative position of an item in an array that matches a specified value in a specific order. Use of INDEX and MATCH offer a more powerful way to perform Lookups. (Overcomes order of column look up). = INDEX (A6:A16, MATCH (H5, B6:B16,0)

133
Q

Linking Charts to Combo Boxes [Dashboard #3]

A

[1] Think outside the box. Need Chart to link through.
[2] Insert very small Pivot Table. How do we link this to the Combo Box?
[3] Place on (Charts - Units Sold) worksheet / This is a separate sheet.
[4] Put just the years in PivotTable columns and switch off Grand Totals.
[6] Underneath the Pivot Table, place Index formula. =INDEX (Years on Pivot Table A24:C24, Cells $A12)
[7] Now if you select year, say 2018 in Combo Box, Chart updates also!
[8] Everything is now linking through.
[9] Chart data is coming from the Calcs page.
[10] Values on Chart data depends on whatever is in selected year, which comes from Combo Box.
[11] When selected year changes, the values change and in turn the chart changes as well.

134
Q

WHATIF Analysis

A

WHATIF Analysis is the process of changing values in cells to see how these changes will effect the outcome of formulas in the worksheet.

Data > What If Analysis >

Goal Seek - Find the right input for the value you want.
Scenario Manager - Create different groups of values and scenarios and switch between them
Data Table - See the results of multiple inputs at the same time.
Solver - Solves problems presented to it.

135
Q

Goal Seek

A

Goal Seek - Specify a value in a cell and then it will adjust figures in the other cells to achieve that value.

1/
Rate 3% p/a
Term 60 periods
Loam amount £24,000
PMT [Formula] = £1,924

2/ £3m Budget to spend after allocating 2% to all employees.

In summary -
Set Cell
To Value
By changing

136
Q

Scenario Manager

A

Scenario Manager - Create different groups of values and scenarios and switch between them.

P&L

Named Ranges please!

Select changing cells within Scenario Manager.

Build 3 scenarios.

[Scenario Summary] to finish for 3-way illustration.

137
Q

Data Tables

A

Data Table - See the results of multiple inputs at the same time.

Rate
Term
Loan Amount
PMT

One variable Data Table
Interest Rate PMT here
1% Auto-fills using Data Table
1.25% Auto-fills using Data Table

Two Variable Data Table
PMT 1.0% 1.25% 1.50%
5,000
5,250
5,500

138
Q

Solver

A

Solver - Solves problems presented to it.

Tickets sold
Price per ticket
Revenue

Costs per ticket
Total costs

Profit

Want profit of £60,000 but costs can’t change.
What price do we have to change is sales numbers stay the same?
[Okay, price to high!]. If maximum price as is, how may do we need to sell?

Identify changing cells.
Add constraints [ $B$4 > = 30 ]

139
Q

INDIRECT FUNCTION

A

Unique function in Excel, in many ways.
Does not perform calculations, evaluate conditions or perform logical tests.
Indirectly references cells, ranges, other sheets and workbooks and lets you return the reference of a cell based on its string representation. As a result you can change your cell reference within a formula without changing the formula itself.
3 examples.

JW - Collection (8 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions