Change view to display formula or result - Excel

In excel toggle between viewing the result or the formula in a cell by pressing control + grave accent `

Ctr + ` (grave accent)

Forecast - Chart Trend Line - Excel

Forecast future values based on historic trends


Insert – Charts – Line Chart

        Chart tools - Layout - Trendline - Linear Trend line


Example Forecast Using a Linear Trend line



Example Trend Forecast Source Data



Trendlines

 Trend lines project future values using regression analysis (the relation ship between the A and Y axis)

Which trend line to use

The closer the R-squared value is to 1 the more reliable the trend line is, typically a trend line that is:
   - linear is best for data increasing/decreasing at a steady state i.e. resembles a line
   - logarithmic is best for data increasing/decreasing at a fast rate which subsequently slows i.e. resembles curve
   - Moving average is best for fluctuating data as it smooths out the trend

Forecast Function - Excel

Forecast future values based on historic trends
             =FORECAST(x,known Ys,known Xs)

E.G. = FORECAST(future date, known sales, known dates)
             =FORECAST(A12,$B$2:$B$11,$A$2:$A$11)

Calculate Largest Number(s) - Excel

Type =LARGE(range,#)

# = #th largest number in the range. E.G to determine the 2nd largest number enter 2:

Type =large(range,2)

Calculate Maximum Number - Excel

Type =MAX(range)

To calculate the maximum number from a range.

Calculate Smallest Number(s) - Excel

Type =SMALL(range,#)

# = #th smallest number in the range. E.G. to determine the 3rd smallest number enter 3:

Type =SMALL(range,3)
  

Calculate Minimum Number - Excel

Type =MIN(range)

To calculate the minimum number from a range.

Charts - Stock - Excel





 Insert – Charts – Other Options - Stock

   

Example Stock Chart

Example Stock Chart Data Source

Charts - Doughnut - Excel





 Insert – Charts – Other Options - Doughnut

Example Doughnut Chart

Example Doughnut Chart Source Data

ALT + ENTER (creates a carriage return within a cell) - Excel

ALT + ENTER creates a carriage return within a cell to display information on another line within that one cell

& [ampersand] - Excel

Concatenates cells contents

Type:   =A1&B1
e.g.      =firstname&lastname
                       JoeBlogs

 

Text can be inserted into the formula by using speach marks" - "

Type     =A1&" - "&B1

e.g        =firstname&" - "&lastname

                          Joe - Blogs

EDATE - Excel and PowerPivot

Calculates the date a number of months before/after a start date

Type:   =EDATE(date,6) 

Calculates the date 6 months from the start date. To calculate prior dates use -(number of months).
For example, could be used to calculate a maturity a certain number of months from a start date.