To change the capitalisation of certain text, you can use a formula instead of wasting time deleting and retyping everything.

• Proper case refers to capitalising the first letter in a string and any letters that follow are lower case, like you might want to do for a title or name.
• To do this, first you have to create a column or row for the return value or information.
• Then input the formula as follows: =PROPER(reference value)
• This will change all of the text to proper case.

The CHAR function (CHAR is for character), returns the character specified by a number.

It helps people label letters with a number by selecting the equal sign and typing in Char,  by dragging it down it will label all numbers with letters.

This function will multiply corresponding components in the given arrays, and will then return the sum of the products.

Array1 is the only one that is required. This is the first argument (two columns of numeric data) whose components you want to multiply and then add together.

This function takes into account lower and upper case letters, but it ignores differences in formatting.

The text1 and text2 references usually refer to cells, though it isn't necessary that the cells be right next to each other, visible, or even on the same sheet.

AVERAGEA is very similar to the AVERAGE function, in that it returns the arithmetic mean, or average, of a list of numbers.

The difference between the AVERAGEA and the AVERAGE formula is that: logical values, within arrays or reference arguments are counted, and text is counted as zero.

AVERAGEA counts logical values and ignores text?

Where as AVERAGE includes everything 

The N function allows you to obtain a number that was converted from a non-interger.

e.g. of non interger= date/percentage/true/false etc

e.g. 23/sept/1978= 28756
e.g. TRUE=1
e.g. FALSE=0

he formula for the RANBETWEEN function is as follows: =RANDBETWEEN(bottom, top)

Another function (not discussed in the video) is =RAND(), which will return a random number between 0 and 1.

CLEAN FUNCTION-- import texts from other programs, the documents or worksheets contain characters that may not print 

FORMULA
=CLEAN(cell reference)

 Get rid of unwanted characters select cell and input formula =CLEAN()

8.1 code function

• the code function returns a numberic code for the first character used in a text string, or to one sole character 
• this refers to the character set used by the computer, in case you don't have access to the full character set
• e.g. =CODE("text")

CHAR and CODE are opposite functions?

CHAR- associate a number to a certain character

CODE: relate/ associate a character to a number which is stuck to it based on the operating system

if theres no repeated numbers the answer to the formula will say N/A

Formula--- =MODE(number1,number2)

18.1 MODE Function

The MODE function will return the most repeated value in an array or range of data.

Here, number 1 is required but any additional numbers are optional. They can also include ranges of data. Keep in mind that the data the cells are referring to could include names, arrays, or references that contain numbers. If an argument doesn't have any numbers, the MODE function will return 0.

=RANK(D6,D6:D14,0/1)

in the form of =RANK(number,ref[order]).

0=descending order (biggest---smallest)
1=ascending order (smallest---biggest)

if you add "$" sign it will be a fixed range 

=RANK(number,$D$6:$D$14,0/1)

the rank would change each time the number/cell reference changes

Rather than deleting them one by one you can remove duplicate values using an Excel function.

To do this, select a cell or a range of cells.

Then go to the Data tab, and find the Data Tools group. Here, click Remove Duplicates.

Then, under Columns you can select one or more columns where you would like data removed.

You can select all columns by clicking Select All. You can clear all columns by clicking Unselect All.

Then click OK.

ROUND FUNCTION UP/DOWN

decimal values are not significant to you, so you can use the Round a Number function in Excel.

This will allow you to: round a number up, round a number down, round a number to the nearest number or round a number to a near fraction.

FORMULA-- =ROUNDUP(argument1, argument 2)
FORMULA-- =ROUNDDOWN(argument1, argument 2)
FORMULA-- =ROUND(argument1, argument2)

**argument 1= number which you want to round
**argument 2= how many decimal places to round it to

e.g. 7.12 (aka B6)
      =ROUNDUP(B6,1)= 7.2 (rounding up by 1 d.p)

23.1 ROUND Function Up/Down

Sometimes decimal values are not significant to you, so you can use the Round a Number function in Excel.

This will allow you to: round a number up, round a number down, round a number to the nearest number or round a number to a near fraction.

The videos below discuss Rounding Up and Rounding Down.

• To do this, first you have to create a column or row for the return value or information.
• Then input the formula as follows: =LOWER(reference value)
• This will change all of the text to lower case.

• To do this, first you have to create a column or row for the return value or information.
• Then, input the formula as follows: =UPPER(reference value)
• This will change all of the text to upper case.

• (E.g.) copyright symbol (©)
  
• To insert a symbol on a worksheet, click the cell where you want the symbol inserted. 
• Then, on the Insert tab, find the Text group and click on Symbol (use the Symbol dialog box)
• A symbol dialog box will appear, and make sure you are in the Symbols tab. 
• Then, from the list of symbols, click the symbol you want to insert and click Insert.

AUTOFILL
will automatically fill in data that follows a pattern, which will save you time when inputting repetitive numbers, words, or strings of words.

One way is just by highlighting the table.

• Put something in the cell then extend it by highlighting the table
• Go to the Home Tab...Fill...Series
• Choose the Column/Rows
• Under type click on the linear
• Step Value =1

Shorten Long Sentences

• Highlight the very long sentence 
  
• Highlight up to the column you want it to end at
• Select the Fill...and click Justify

• A Treemap chart lets you view your data in a hierarchical manner
• Easy to identify patterns in terms of popularity, prices, volume, and others.
• Treemap charts divide up categories into different sized rectangles that are proportional to the amount or number (units) they represent.

METHOD

• First need to input your data where you organise larger (left) to smaller (right) categories.
• When you have finished inputting your data, highlight the table, click the Insert tab, then click Insert Hierarchy Chart and finally Treemap.
• Colours will automatically be selected for you, but you can format and personalise these in the Format.
• You can also format how the labels appear by clicking Format, Series Options, Label Options, then choosing the Options you would like.

• The Sunburst chart is useful when you want to display hierarchical data.
• The outer rings represent categorical divisions (contributing pieces of inner ring divisions).
• The innermost circle is the top of the hierarchy and it demonstrates how outer rings are related to inner rings.
  (e.g) Quarterly Sales
  The quarters would go in the middle, then the months, then the weeks on the outermost layer.
  

METHOD

• First you must select your data, which must be hierarchical.  
• After you have created your data, select it, click Insert, then Insert Hierarchy Chart, then Sunburst Chart.
• Use the Design and Format tabs after clicking anywhere in your chart to customise how your chart looks.

TABLES
Tables in Excel allow you to easily manage and analyse information in a group of related data.

TABLE TIPS

• Select Data -- click cell and CTRL+A
• Create Headers -- Home tab...Format as table (my table has headers)
• Data range -- already selected if you highlighted data (if not write it like this e.g. $B$4:$J$35).
• There is a filtering option by headers, to help find specific data.
• Click cell... table design tab... summarize with pivot table...new worksheet
• Click cell...table design tab...remove duplicates (CTRL+Z to undo)
• To get AVERAGES/TOTALS/PERCENTAGES etc we can right click on arrow by total... click quick analysis and choose options of other functions for all rows.

TOTAL ROW -- If the Total Row doesn't show up, bring up the Table Tools by clicking in the table, and click on the design tab.
You will then see check boxes in Table Style Options. Click Total Row so that it appears at the bottom of your table.

TEMPLATES

• Go to Files...Go to New... use whichever template you find the most useful.
• You can search for which template will help you.

 Common templates include: personal and business budget, cash flow, calendars, to-do list, travel expenses, loan comparison calculators, schedules, invoices, inventories and more

 AVERAGEIF

The AVERAGEIF function in Excel is similar to the AVERAGE function in that it calculates the arithmetic mean of a set of values, only of values that meet certain criteria.

For example, you can specify that it should only take into account values greater than 30 or 100.

The formula for the AVERAGEIF function is:

=AVERAGEIF(range, criteria, [average_range])

The range should refer to the range of cells you want to be included in the calculation.

Insert the relevant criteria needed to find the average.

average_range is optional, although you could specify even further what cells to take into account.

CELL FUNCTION (with 10 variations)

The CELL function is an informative formula that tells you about the contents, location, formatting, and properties of a specific cell.

The formula for CELL is:=CELL(“what you want to find out about the cell”, cell location)

The variations of this formula include the following:

=CELL(“address”, cell location), to find the location of the cell.

=CELL(“col”, cell location), to find the column number.

=CELL(“row”, cell location), to find the row number.

=CELL(“type”, cell location), where b is for blank, l is for text, and v is for value.

=CELL(“prefix”, cell location), where it will return a ' for left, ^ for centre, and “ for right. When there is nothing, it will be displayed as numeric entries.

=CELL(“width”, cell location), to find out the width of a cell.

=CELL(“format”, cell location), which will tell you the number format of the cell. It is necessary to download the number codes for format for future reference.

=CELL(“parentheses”, cell location), where 1 is yes and 0 is no.

=CELL(“color”, cell location,) will tell you if the cell is formatted for coloured negatives. It will return 1 for yes, 0 for no.

=CELL(“protect”), will tell you if the cell is locked (1) or unlocked (0). =CELL(Filename, D5), will tell you the name of the file. 

LARGE Function

This function will return the Kth largest value in a previously defined data set.

You can use this function to select a value based on its standing when compared to the other values, including the highest score, second-place or third-place score.

The formula for the LARGE function is the following:=LARGE(array,k)

The array refers to the range of data in which you want to determine the largest value.

K refers to the position in the array or cell range of data to return (for example, if you want to return the second largest number from an array of values, you would type 2 for K). The result will be the value that fits that description. 

MAX Function

The MAX function returns the largest value in a set of values.

The formula for the MAX function is as follows: =MAX(number1, number 2,…)

Here, number 1 is required but any additional numbers are optional. Ranges of data are also valid.

Keep in mind that the data cells are referring to could include names, arrays, or references that contain numbers. If an argument doesn't have any numbers, the MAX function will return 0.

MEDIAN function

The MEDIAN function returns the median number of a set of numbers.

The formula for the MEDIAN function is as follows: =MEDIAN(number1, number 2,…) 

Here, number 1 is required but any additional numbers are optional. Ranges of data can also be included. Keep in mind that the data the cells are referring to could include names, arrays, or references that contain numbers. If an argument doesn't have any numbers, the MEDIAN function will return 0. There is a maximum of 255 numbers that can be input.