2. ADD

4 ways to total numbers:

• use '+' and '=' with the cells (e.g., =(C6+D6+E6) ENTER)
• use '+' and '=' with the numbers (e.g., =(91+40+60) ENTER)
• type out 'SUM' and highlight the range of numbers to add.e.g. : =SUM(C8,E8) ENTER
• press the 'alt' key and the '=' key together (it automatically selects & sums the range)

You can also sum rows:

e.g. : =SUM(C6:C11,D6:D11,E6:E11) ENTER

You can also copy formula using 'Ctrl' key 'c' key and paste using 'Ctrl' key 'v' key

=sumif(range,criteria,sumrange)

e.g. =sumif(select coloumn of names,select name you want data on,select amounts coloumn)

if less than or more than etc must be in " " marks.

e.g. =sumif(range, "<criteria", sumrange) or

">=criteria",   etc.

if criteria is letter starts with then it is letter plus * between quotation marks e.g....  ,"M*",

if criteria is digits longfor example use first letter then ? or * for other digits

e.g. if the name was jack =sumif(range,"j???",sumrange)

 You can use a question mark (?) or an asterisk (*) for the criteria. The question mark matches any single character in the specified range, and an asterisk matches any sequence of characters in the specified range.

The SUMIF function is used to add up the values in a range that meet the criteria specified.

* is times x 

= minus sign, C7-D7, press enter

= sign, 5000-4000, press enter 

= sign, open bracket, C7-D7, closed bracket, press enter

= sign, open bracket, 33000-31000, closed bracket, press enter 

If the figure is smaller than the number being subtracted from e.g 25000-26000 the answer will be -1000

TRANSPOSE-- used to switch or rotate data cells from vertical to horizontal and vice versa.

highlight vertical cells you want to move and click copy, click paste special where you want to move them and a txt box will open, click transpose, data will be moved horizontally. Same process for horizontal to vertical.

If highlight multiple vertical columns, it will copy horizontally one underneath each other, so quarter 1 will be the top column and the sales underneath it.

10.1 paste special

highlight txt and copy, rightclick where you want to move it and pick paste special, choose how to paste it

• can paste the format, column width or other elements into the sheet 
• copy the data or formatting, click on the cell/cells you want and right click 
• click paste special under paste options and choose whether you want to paste only values, only formatting or other options
• once you click, the data or formatting will paste into the area 
• Sometimes, in addition to copying and pasting data, you want to paste the format, column width or other elements into the sheet.
   

=AVERAGEIF(range,criteria,average range)

e.g. =AVERAGEIF(range is names,criteria is box where you want to change input, average range is numbers sold)

If want the average to show a specific person (for example), name columns would be highlighted for the range, to have it show for a specific person, put in quotations the name (would have to type the name every time it changed or wanted to see someone else) or the more effective way is to type the persons name and select that cell to be used as the criteria in the formula. If the name is typed in a separate cell, you can change the name in the cell and it will automatically change the formula. Select the range and close the bracket.

runung totals

 =SUM($C$2:C2) $ means the value will not shift once you copy the formula

we can press F4

= must always use

sum

=(c6+d6+e6) enter

=sum (select the range.)

alt= together

=sum (select all 3 cell mindegyik koze vesszo.)

or copy the formula

Multiply

=cell1*cell2

divading

amount   qunty

1000/2

=b6/c6

prcentage 

100 nak  25 szazaleka

1.=25/100*100

2. =d5/c5apply%format

3. =25/100 %format

 Running Total

Type the following formula: =SUM (First value cell:Current value cell)

For example, =SUM($C$2:C2). The $ means that the value will not shift once you copy the formula; it will always be fixed and refer to that row or column.

Completed 6 modules

Key Points
=d5/c5*100
ORtarget percentage / total you wish to divide,* 100

=d5/c5 then apply % format
OR
=25/100 then apply % format

Home tab, Number Pane, % sign brings 0.25 to 25%

Go to the Home tab and the number group, and click on the % sign.

The data should show up in the form of a percentage.

Video

In this video, we will learn how to calculate percentages in Excel. 

To calculate percentages in Excel we can use 3 ways and all of those three ways are listed over here. 

What is 25% of 100

So what we will do is, we will divide 25 by 100.
100 in cell C5 and 25 in cell D5.

1st way:
=D5/C5*100      [so 0.25 becomes 25]

2nd way:
=D5/C5 then click on the cell and choose the % format (from the Home tab then in the Number pane, click on the % sign OR use the dropdown where it says General and choose Percentage)

=D5/C5 then apply % format

3rd way:
Also you can hard code the values
=25/100 (then press enter and get 0.25) and then apply the % format

=25/100 then apply % format

Very easy to find percentages in Excel. 

Highlight the headings, click the drop down icon and select the filter required. Text and numbers. Numbers can be filtered to show numbers greater or lesser than a specific number, as well as smallest to largest etc

to multipy use the * sign

 =Cell*Cell

=10*3

 you can also copy the formula by right click copy and paste.

 =SUM (CELL:CELL) ENTER ADD CELL NUMBER : IS TO IE A4:A6

Sorting data

Data must have a header in a table to be able to sort it.

Custom sort so you can arrange data in the order you want the whole table to show

ie alphabetically or prices from higher to lower or rows by colours or icons.

To sort Data by Text - select colum of the data with text, or make sure the active cell is in a a table colum with data with text that you want to sort.

• click data tab
• find sort and filter group
• click on button desired to either organise from A - Z  or Z - A

To sort numbers save as above, select column of the data, or make sure the active cell in a table is a column with the numbers you want sorted.

• in data tab
• sort and filter, click on A-Z button to organise from smallest to largest
• Z-A from largest to smallest

To sort data by text, select a column of the data with text, or make sure the active cell is in a table column with data with text that you want sorted. Click the Data tab, and find the Sort & Filter group. Click on the button desired to either organise from A to Z (A→Z) or Z to A (Z→A). 

Pie Chart Description

The formula is asking Excel to determine whether the actual amount spent is greater than the amount budgeted, and if it is, the result cell should say “Over budget”. Otherwise, it should say “Within budget.”

e.g. =když(B7=1 "ano", "ne") will show it the number in B7 is the number 1. Yes is the value for true and no is the value for false, true and false must be in that order.

True and false criteria can say what ever you want.

e.g. sam(B1) better than mike(C1)

=IF(B1>C1,"sam is better","mike is better")

The COUNT function simply counts the number of cells that contain numbers within the list of arguments.

If you have data that is arranged in columns or rows in your Excel worksheet, you can turn your data into a doughnut chart, which looks similar to a pie chart, but with a “hole” in the middle.

Doughnut Chart-- Turn data into doughnut chart (similar to pie chart but with a "hole")

• Select the data by highlighting it
• Insert...Doughnut Chart 
• You can change and alter different designs
  
  
• Newer versions of Excel allows you to add another layer of data to be able to compare proportions from two different data sets. 
  
  
• If percentages are used, each ring will total 100%. 

**Doughnut charts aren't easy to read for everyone as the proportions of inner and outer rings do not display data accurately. 

How to make doughnut chart: 

Select data you want to plot. 

Insert tab, find the Charts group, click on pie charts (rounds circle thing) drop down list. Click doughtnught chart. 

Click plot area of the doughnut to change design, layout and format to fit ur needs. 

when there are multiple things that lead to a larger sum. 

used when showing proportions of a whole 

Once you have your donghnut chart you can change the colour, size or go into layout

If you have data that is arranged in columns or rows in your Excel worksheet, you can turn your data into a doughnut chart, which looks similar to a pie chart, but with a “hole” in the middle.

0 = FALSE

1 = TRUE

=svyhledat

• Allows you to express the months of the year as integers
  E.g. Janurary would be expressed as 1, while July would be expressed as 7.

• Have a cell in date format
  **(see previous video if forgotten how to do this).

FORMULA-- =MONTH(serial_number). 
**serial number= the date you are referring to. 

For example, January would be expressed as 1, while July would be expressed as 7.

In order for the MONTH formula to work, you have to have a cell in date format, e.g. with a date entered as dd/mm/yyyy or similar. Alternatively it can reference a cell where the DATE function has been used, where the date is expressed as =DATE(year,month,day), all in numerical form.

Module 30:Line Charts

• Line Charts are a way to present information eg rainfall over different months
• Can only be text,time and dates
• Highlight your data you want to plot in the y axis
• Then on insert tab find chart group and click on line chart 
• Then choose the style line chart
• To modify the design layout and format you can click on the chart area and the chart design tab will appear 

Borders allow your data to appear clearer, more organised and more professional and they can also make tables of data appear easier to understand. 

One of the most common way to add borders is to use predefined border styles, though it is possible to create a custom border. 

To create a border:

Select the cell or range of cells to which you want to add a border.

Then, on the Home tab, find the Font group.

Can use drop down to do 1 border line at a time, or select two cells at a time

Look for the border icon n click the down arrow to select the style u want to apply.

To remove cell borders, click on the down arrow next to the border icon and click No Border.

To create custom border: Home tab, find the Styles and click the arrow next to the Cell Styles icon.(looks like a window) Click New Cell style and fill in a name for the cell style u will creat. Then click format and change the line, style and colours you want the border to be. Click OK and ur custom style should be available. 

most common: all borders n Think outside borders. 

All borders-theres a border round all the cells. Selec it again and select think border to make them thick. 

How to remove- save the data then select No border  

draw a border- click individual edges of a cell. if u don't want to, press escape. 

draw borer grid- works only on one edge as a grid. have to select at least two cells 4 it to work. Double click to delete. 

Line colour- click on the individual line borders to change the colours. 

 If you select draw border you can click on the line you want the border to appear and it will add it for you

Draw Grid - You have to at least select 2 cells for it to work and it will add 2 grid boxes - double click and it will go away

Press ESC to get out of a particular mode

add =(numb+numb) enter=

times =(numb*numb) enter

subtract =(numb-numb) enter

divide =(numb/numb) enter

=average(c6,c7,c8,c9)

Keep in mind that if a range or cell reference has text, logical values, or empty cells, they will be ignored, but cells with the value “0” will be included in the calculation.

Conditional formatting is a quick and effective way to highlight important information in a spreadsheet as you continue to update it. 

It allows to make own rules, so it can help visualise checklists (ex: a cell and say Done and turn green) and whether invitations were sent out, among other infinite possibilities. Have to create rules through conditional formatting one by one. 

In order to create the first rule:

How to create first rule: 

Highlight the cells that will be used as reference points. 

Click Home, conditional Formatting, New Rule. Dialog box will open up, here, u have to create a formula to determine which cells to format (which cells will be the output) and type it were it says Format Values Where this Formula is True. 

Click format and change the format to your liking. 

After, you may create another rule following same steps so that the cells not only change colour, they also say the words "yes", "no", "unsure", or others. 

 The reference number has to be a number in the column.

 Can change the colour of the boxes to colour code

Highlight range of txt first and then press home - conditional formatting- apply greater than or less than...- apply colour of formatting 

Ctrl P to print

or page layout to change margins, orientation, print area etc.

To print a selection of the spreadsheet mark what to print and select print area selection

print titles (on ribbon under page layout tab) enables you to sort headers, footers, gridlines etc.

 CONTROL P FOR PRINT

Merging cells

When you merge cells, the cells become larger and are displayed across mutiple rows or columns.

Useful to show full text written by merging cells. Only keeps text in upper left cell, deletes text in other cells merged with.

To merge

Select 2 or more adjacent cells you want to merge

one of the cells can have text but if it does the others CANNOT have text.

Comments

annotating or making comments on a spreadsheet. 

useful if revising a spreadsheet, want to suggest changes, or want to make an explanation regarding a specific calculation or value. 

How: select the relevant cell, right-click and choose New Comment. 

you will see a box showing your name

Type comment in box 

Click arrow button to post ur comment 

will show date n time it was posted 

When click away from box, it will disappear, will notice a purple Comment indicator in the top- right of the cell.

Hover over cell again to see comment. 

After u add a comment, can edit the comments, reply to comments, delete them and resolve them. 

if file is shared, ppl can reply to ur comment. can delete their comment. 

can reopen and close the option for ppl to reply 

press ctrl and z to bring comment back if u delete it.

This is useful if you are revising a spreadsheet, want to suggest changes, or want to make an explanation regarding a specific calculation or value.

To add a Comment, select the relevant cell, right-click and choose New Comment.

You will then see a box showing your name.

Type your comment in the box.

Click the arrow button to post your comment.

It will then show you the date and time your comment was posted.

When you click away from the box, it will disappear, but you will notice a purple Comment indicator in the top-right of the cell.

Hover over the cell again to see the comment.

After you add a comment, you can edit the comments, reply to comments, delete them and resolve them. Click on the comment box and has an option to edit or delete.Hover over comment and options are at the top, dots is to delete

Column Chart Definition

A column chart is useful to compare data points in one or more series.

BAR CHART

METHOD

• Select by highlighting data, even along with the headers. 
• Insert tab
• Click the bar chart
• Change and edit like other charts on Format Tab

Number Formatting

• many ways numbers can be displayed in excel
• this function is useful if you want to show numbers as currency, numbers with or without decimals, as a date, as a fraction, as a percentage etc.
• select the number(s) you want to format --> on the home tab, number group, dialog box launcher in the lower right hand corner --> in the category list, click the format you want to use and adjust the settings to meet your needs

* intro

Cells-  the blocks within the spreadsheet. Columns & rows

Formulas- a set of instructions for a calculation, and always starts with an equals sign = , and can include maths signs cell references and functions

Functions - built in formulas that perform specific calculations or tasks

(Parenthasis- an addition on end of a sentence to explain the meaning?)

Absolute and relative cell references

Relatice cell refs can adjust. Absolute cell refs do not adjust, they are fixed. A cell ref can be made absolute by putting a dollar sign ($) before the row letter or column number. (e.g., $A$1).

AutoFill - easy way to fill a series of cells with a pattern or sequence of values

Formatting - enhances a cell's appearance & improves readability

 Use Tab to open and close brackets

• Dates have different formats
• Always start with an [=] sign. 

FORMULA-- =DATE(year,month,date)
*default format

• You can change the format of the date.
  
  
• Right click cell... format cells... click date... change format

Can pick the format you want, multiple options.

To insert the current date: press Ctrl+; (semi-colon). To insert the current time: press Ctrl+Shift+; (semi-colon).

The formula for the DATE function is: =DATE(year,month,day)

Remember that all three need to be presented as numbers for it to return a date. For example, if you input =DATE(2015,4,6), Excel will return April 6th, 2015. Note that Excel doesn't accept years earlier than 1900.

A couple of extra exercises you can try (not in the video) are: To insert the current date: press Ctrl+; (semi-colon). To insert the current time: press Ctrl+Shift+; (semi-colon).