For MacBook!!
Lesson 1- introduction
● Decimals and thousands: Always 1,000.000 To change it::
- File (top left corner), options, advanced, remove the tick on “use
system separators”. “,” for decimal separators, “.” for the thousands
separators.
● The cell has to be formatted in a way that fits the data (Number, accounting,
percentage)- That is in “home”, top-middle.
● With the formatting cells, you can also add short (09.05.2021) or long dates
(9 May 2021)
● Para colour, select the column and click “conditional formatting”
● Using formulas: To start a calculation, always put “=”. To lock a formula
either put the dollar sign or click on the cell and press F4.
Lesson 2- graphs and charts
Bar chart and histogram
● select data and add chart
● Add chart title: “layout” in “chart tools”. Click in “chart title”
● Add Axis title. Layout, axis titles.
● Adjust layout of the chart: Click legend w the right mouse button and choose
“Select Data”. A new window will open, select Series1 (left side of the
window) Click on edits and type “Series name” into the corresponding field.
“OK”.
The Pie chart
Countif and countifs
● Countif: =COUNTIF(B2:B31;"1")
● Countifs: =COUNTIFS(C2:C31;">1980";D2:D31;">50000")
Lesson 3- pivot table
● selecciona datos, insertar pivot table.
● Selecciona las variables que quieres poner. Trucos:
Lesson 4 If, And, Or function
● IF: its in formulas (logical function).
- In logical test “F5>$H$2” = True. If cell 5 is larger than or equal to H2
- Value if true F5*$I$2 = 502.6. If yes, then F5*I2 needs to be locked
- Value if false 0. If not, 0
- ok
or just write down =if(D1=”RED”,”yes”,”No”)
IF=AND(A8=”apple”,A9=”green”),”yes”,”no”
● AND(A8=”apple”,A9=”green”)
● OR(A8”apple”,A9=”green”)
=IF(AND(F5<$I$2,”F5>=$I$3),F5*$J$3,0)
Lesson 1- introduction
● Decimals and thousands: Always 1,000.000 To change it::
- File (top left corner), options, advanced, remove the tick on “use
system separators”. “,” for decimal separators, “.” for the thousands
separators.
● The cell has to be formatted in a way that fits the data (Number, accounting,
percentage)- That is in “home”, top-middle.
● With the formatting cells, you can also add short (09.05.2021) or long dates
(9 May 2021)
● Para colour, select the column and click “conditional formatting”
● Using formulas: To start a calculation, always put “=”. To lock a formula
either put the dollar sign or click on the cell and press F4.
Lesson 2- graphs and charts
Bar chart and histogram
● select data and add chart
● Add chart title: “layout” in “chart tools”. Click in “chart title”
● Add Axis title. Layout, axis titles.
● Adjust layout of the chart: Click legend w the right mouse button and choose
“Select Data”. A new window will open, select Series1 (left side of the
window) Click on edits and type “Series name” into the corresponding field.
“OK”.
The Pie chart
Countif and countifs
● Countif: =COUNTIF(B2:B31;"1")
● Countifs: =COUNTIFS(C2:C31;">1980";D2:D31;">50000")
Lesson 3- pivot table
● selecciona datos, insertar pivot table.
● Selecciona las variables que quieres poner. Trucos:
Lesson 4 If, And, Or function
● IF: its in formulas (logical function).
- In logical test “F5>$H$2” = True. If cell 5 is larger than or equal to H2
- Value if true F5*$I$2 = 502.6. If yes, then F5*I2 needs to be locked
- Value if false 0. If not, 0
- ok
or just write down =if(D1=”RED”,”yes”,”No”)
IF=AND(A8=”apple”,A9=”green”),”yes”,”no”
● AND(A8=”apple”,A9=”green”)
● OR(A8”apple”,A9=”green”)
=IF(AND(F5<$I$2,”F5>=$I$3),F5*$J$3,0)
