Please enable JavaScript.
Coggle requires JavaScript to display documents.
Excel - Week 1 - Chapter 1 and 2 - Coggle Diagram
Excel - Week 1 - Chapter 1 and 2
1.1 Opening and 1.2 User interface
A3 => Cell address
Cell and formula bar won't necessarily have the same numbers
Active cell => Selected cell
Top row in excel where you see the home / insert button is called tabs
Column headings
Column
When you click on the tabs, it opens al the functions you can use it for, these functions as a collective is called a Ribbon
Row headings
Row
Dialogue box launcher
3.1 Managing workbooks
Delete a worksheet:
Right click on it in the bottom left corner
Delete
Copy a worksheet:
Right click
Move or copy (this opens the dialogue box)
To book (where you want to move it to)
If I want the same information in my current workbook and in my new workbook select "create a copy"
Ok
Renaming a sheet:
Right click on sheet 1 at the bottom of the page
Rename
Enter
If you want to move the worksheet DON'T select "create a copy"
3.3 Tables
Changing row height or column width:
Home tab
Cells group
Format
Cell size
Column width
Brings up dialogue box
Change to what you want
Ok
Merging and unmerging cells => Show a heading in the middle of a number of cells:
Select where you want to merge your heading to
Home tab
Alignment group
Merge and center
Hiding and unhiding rows and columns:
Select what you don't want to show
Home tab
Cells group
Format
Visibility
Hide and unhide
Hide columns / rows
2.1 Basic mathematical formulas
Formula is started with = or +
Difference between count and counta
Count shows you the number of cells containing numerical values
Counta shows you the number of cells containing both numerical and alphanumerical values
In counta => remember the a at the end stands for ABC's
Complete set of data => database
2.3 Inserting headers and footers
Page layout tab
Page setup group
Dialogue box
Header / footer tab
Custom header
Choose where to type => left section / center section / right section
2.4 Printing headings
If you want to show the headings at the top of every page
Page layout tab
Page setup group
Dialogue box
Sheet tab
Rows to repeat at the top
Click on the arrow in this line
Select rows that I want to repeat
Click on the arrow in the box on the RHS
Ok
3.4 Copying and moving cells
Paste shortcut
Ctrl + V
Cut shortcut
Ctrl + X
Copy shortcut
Ctrl + C
Selecting non-neighboring cells:
Hold Ctrl
Select first cell
Select second cell
To get to the bottom of the sheet => hold in:
Ctrl + Shift + down arrow
3.5 Freezing panes
Click on the cell below the row you want to freeze
Click on the cell to the right of the column you want to freeze
View tab
Windows group
Freeze panes tool
2.3 Text functions
=concatenate(B2;"";A2)
Left function
Only show ABC instead of ABC001
=left(B3;3)
The second three in this formula shows the number of characters you want to display
Joins two or more text strings to make one
Proper function
Capitalize the first letter and make the rest small
Use when you receive a database and everything was typed in capslock
=proper(B3)
Instead of showing ABC001, we will now see Abc001
Use the concatenate function
Put names and surnames of people in the dataset next to each other => First the name and then the surname
2.2 Basic functions
= D3*C3 => Relative reference
Click in between the D and the 3 and then press F4, now we have an absolute reference
Absolute reference:
If I want to copy an answer to the formula from one cell to another, we need to use absolute references.
In absolute references we keep the cell fixed for using the formula
If I have a list of numbers (1; 2; 3) underneath each other, highlight the last three and go to the right bottom corner until it makes a + sign, hold in left mouse button, and drag down
Month formula:
When we need a numerical value for the month we have typed in.
E.g. 1 for January, 2 for February, etc.
=month(A1) => cell with the date
3.2 Editing and formatting cell content
Wrapping text does NOT increase the size of the column, but the information in the cell will be displayed fully underneath one another
Wrapping text:
Home tab
Alignment group
Wrap text tool
Copy existing format to another cell:
Home tab
Clipboard group
Format painter tool
Click on the cell