Intro

Seite 1

press Alt key to get to shortcuts

cell grid can be identifed by cellname : A3 | B10 ; or you can give a name for the cell by yourself in the Namebox window;this name can be used for calculating the formula;

you can move popularly used tools to quickaccess toolbar

Seite 2

Lesson 2:

use CTRL+T to convert data set to table

go to insert menu and add slicer; (available only after in excel 2010 and above)

go to design menu and add the total row (for average total count operations)

ex.,=SUBTOTAL(101,staff[age]) ; 101 => average; staff is tablename ; age is columnname

go to design meanu and you can change table name:

Seite 3

Lesson 3

cleaning data:

flashfill : (excel helps autosuggest : extracting country field from a string)

manual option : Ctrl + E : extracting first letter of alphabet from first and last name

how to identify blanks?

click on column : remove duplicates removes duplicate rows;

how to identify fields with blanks in table ?

click on column : press F5 ; click special; click blanks; selects rows; click Ctrl - ; deletes all rows with specific blankl

how to identify strings?

select column; Ctrl+F; find all ;

select all rows;

close window;

Ctrl- ;

Seite 4

lesson 4: Powerquery is a powerful option

cleanup using power query

go to data:

from table

opens query editor

select department column: filter : remove check off the NULL

go to add column : extract : how many characters: trim option

close&load

cleaned data appears as new table in new sheet;

if you update original table and refresh all, new table gets updated;

Seite 5

lesson 7 : charts

you can add a title of chart as a formula from cell!

combine string and the number : ex: totalheadcount (USA:45,NZ:34)

=B14&" (USA:"&C14&", NZ:"&D14&")"

Seite 6

lesson 8: conditional formatting

using conditional formatting : we can find the duplicates and highlight them using colors

using conditional formatting : we can find values in a range and highlight again

using conditional formatting : we can find top 10 values and color

using conditional formatting : we can point to the trend : increase | decrease;

hoe to do it: use conditional formattin: Icon sets : Up/Down/no change;

change the filter from % to number; green : > 0; red otherwise;

select icons only : no number!

Seite 7

Lesson 9:

Working with filters:

shortcut for creating filter : Ctrl+shift+L;

once applied use the same shortcut for removing the filter;

after applying filter : for numbered fields: you can use average ; below average ; above average;

for names : you can do sort by names

Seite 8

Lesson 5:

Formulas:

Referencing types:

Structural reference :

Cell reference

Name Reference

Structural reference : used for tables:

Ex.,= SUM(Table1[column])

Cell reference : C4,D4

Name Reference: give name to Cell;

Rename C4 as bracketName

Seite 9

lesson 6

Pivot tables

Create table

Then click pivot table

Add values:

Add rows

Add columns

Go to analyze :

Then add pivot chart

Add slicer to it;

Seite 10

Lesson 10:

Shortcuts:

F2 : Edit a cell;

F3 : Show all the names

F4:

Ctrl + 1 : format cell | format shapes

Ctrl+shft+L - on/off filters in a table

TAB: autocomplete the formula: example : SUMI=> excel shows SUMIF=> press TAB to autocomplete;

Seite 11