0801.
The Sum Function
-(00:36) Sum function syntax =sum(b2:b8).
-(01:19) Auto sum function on home ribbon is the Greek sigma.
-(01:40) The auto sum works as the sum function.
-(02:13) Using auto sum after selecting multiple columns including their row of empty cells.
-(02:33) Similarly the above applies to rows.
-(03:20) The sum function should be used to add non-consecutive cells.
-(04:55) While selecting non-consecutive cells to be added up in the sum function, you can use the
CTRL key instead of the comma.
-(05:56) To sum a range / an area of rows and columns at the same time, highlight/select all of them
PLUS the empty rows and columns cells at the end.
-(06:22) If both of them ain't highlighted, excel assumes that you only want to add down the
columns.
[Link] Formulas And Functions
-(00:00) Copy and paste tools in excel can only copy functions not results.
-(02:13) Excel functions are all relative functions.
-(02:35) Pasting a formula with its relative address in multiple cells.
-(03:50) Copying a formula with its relative address to multiple cells using the Fill handle.
-(05:36) Even when using non-consecutive cells, excel picks up the right cells to produce the right
sum, as the relative address of everything is in the right place.
[Link] Mathematical Functions: Average, Min, And Max.
-0.12 The Average function. That function counts the sum of the total no. of values within a set of
cells and then divides by how many cells are in that same set.
-0.20 Working out the average without the average function (Summing and dividing manually).
-0.48 It works just fine, except when one of the 8 shops / cells doesn't have a value (0). Their total's
cell changes, but they are still divided by the same number of cells (8), when in theory we should be
dividing by 7, as there are only 7 shops returning sales.
-1.05 That's where the average function does that work for us.
-1.14 The average function syntax (=average(b2:i2)
-1.15 But notice that the in order for the average function to discard counting a cell while dividing, it
should be empty, as a value of zero in a cell counts.-3.27 The max. function works out the highest
value in the range. Its syntax is (=max(b2:i2)).
-3.59 The min. function works out the minimum value in a range. Its syntax is (=min(b2:i2)).
[Link] Count Functions
-(00:42) The Count function counts Cells' values with numbers only. Its syntax: =count(b2:f11).
-(01:27) To count cells that contain numbers or text or even space, use the counta function. Its
syntax =counta(b2:f11).
-(01:56) Typing a space in a cell would make the cell counted as filled cell not an empty one, and
would be counted with us.
-(02:08) Countblank function is used to count empty cells. Its syntax =countblank(b2:f11).
-(02:54) Using countblank + counta functions to calculate the total number of cells in a range
including the empty cells/blanks and the filled ones. Its syntax: =countblank(b2:f11)+counta(b2:f11).
[Link] Referencing Explained
-(01:26) Cells relative reference problem (looking at the profit margin cell has moved relatively).
-(02:16) Adding a $ sign to make a cell absolute. Its syntax =b9*$l$2. A $ sign before a column fixates
the column, typically before a row fixates it.
-(04:29) F4 adds automatically a $ sign to any selection. Pressing F4 again adds A $sign to the entire
the row/column. Pressing it once more removes it.
[Link] Across Worksheets
-(01:05) To create a cross sheet formula, after the equal sign choose the desired worksheet and the
desired cell within it AND DON'T CLICK BACK. Just press Enter (=jan!b7).
-(02:58) Using Paste link as an alternative for cross sheet formulas (Select worksheet then cell &
copy/ get back paste as link).
-(03:20) Excel automatically places $ marks when pasting a linked formula.
-(03:49) Linked formulas are relative as long as their source worksheets’ layout is arranged as their
destination one.
(05:41) The apostrophes in the formula are because there's a space in the sheet name (=b9*'default
values'!b4)
0807.3D Referencing
-(00:40) Ordinary function/formula before 3d referencing (=jan!b3+feb!b3+mar!b3).
-(01:13) 3d referencing is making the above formula shorter, but that is only viable if the destination
sheet's layout is identically created like the source worksheets' layout (The cell that will be added up
should be in the same place, like in the video same cell b3 was used).
-(01:47) 3d referencing formula =sum(jan:mar!b3), where jan is the 1st sheet's name and march is
the last. Only 1 last exclamation mark is needed.
-(03:11) 3d referencing is relative.
-(03:51) filling without formatting (Dragging with right mouse button - fill without formatting).
-(05:34) Excel places the apostrophes in the formula because there 're spaces in the sheet name.
-(06:03) Logically everything is in its same relative position, the result is not.
-(06:52) Pasting the resultant formula in other relative resultant cells (Select cells - Paste special -
Formulas and number formats).
-In 3d referencing remember that the cells you are selecting must be identical on each sheet.
[Link] Use Of Named Cells And Ranges
Naming a range makes a formula shorter.
-(01:20) To name a range (Select it - Type new name in white box - press enter).
-Named range names can't contain spaces.
-(02:18) To highlight a range by selecting its name.
-(02:31) Another way to name a range is (select range - formulas ribbon - defined names- define
name - new name dialogue box).
-(02:44) In the new name dialogue box the scope is used to restrict that range name to a particular
sheet or use it in the whole workbook.
-(04:02) Shorter formulas ex. =sum(bob).
-(04:46) Name manager to delete ranges or change their range area , just make sure to click the tick
not the close button.
-(06:33) Creating multiple range names for multiple columns using one range selection (select -
create from selection).
-(07:28) Typing the 1st letter of any named range in a formula, reveals it directly.
-Vid 0901 : Enhancing worksheets using themes.
-00.00 A Theme : Background color (choose a theme color)
: Font color (Choose a theme color)
: Typeface (Choose a theme font)
-00.37 Idea behind the theme & on-offer colors is that you can switch themes & all colors will change
automatically.
-01.10 Page layout - themes to switch themes. Notice that when you switch themes & get back to
the home ribbon, you'll find new color choices in the theme slots.
-02.28 Choosing a subset of fonts & colors within the existing themes.
-03.30 The effects dropdown affects objects only.
-03.41 Inserting an object (Insert-illustrations-shapes) -03.01 Saving an edited theme.
Vid 902 : Adding, editing & deleting comments.
-00.00 Adding comments to cells (Select desired cell- review - new comment - it will show you a box
with your logging name to type in)
-00.18 The red triangle indicates the comment's presence.
-01.41 Click on the cell - click on edit to edit the comment.
-(02.05) SHow/hide comment to make the comment visible all the time with a resizable textbox.
Click delete to delete a comment.
-Vid. 0903: Creating your own autofill list.
-00.50 Creating your own custom lists of sensible words for autofilling.
-01.15 File - options - advanced - edit column lists - add your list entries.
-03.11 Another way of creating custom lists from existing data ( Highlight desired cells - file - options
- advanced - edit custom lists - you'll find your highlighted cells in "import list from cells", just click
import)
-Vid.0904: Converting data to a table for formatting.
-00.15 Table formatting increases formatting capability (Background color, font size, ... etc.)
-00.27 Selecting an area to format as a table, or simply click into an area. From the home ribbon
click on format as table & select ur table style.
-01.10 Excel shows u an assumption for the size of ur tables' cells. If the assumption ain't correct,
use the browse buttons to manually select the cells. Tick whether ur table should have headers or
not.
-02.05 Activating the total row.
-02.57 Banded rows or columns mean that everyother column or row will have a different
background color.
-03.44 Converting unformatted data as a table is way easier to control its format from scratch.
-05.08 Changing the table's name.
[Link] And Deactivating Worksheet Protection
-00.41 To protect a worksheet - review ribbon - protect sheet - add password option (if file is shared
with untrusted people) - Tick what you allow to be edited.
-00.45 Notice the protection is applied to one worksheet not the whole workbook.
-01.58 Notice the error message u get when trying to edited protected data.
-02.18 Notice "insert row" is greyed out as it's been protected.
-02.32 Clicking on unprotect sheet
[Link] Part Of A Worksheet To Aid Data Entry
-00.36 Excluding some cells from worksheet protection - Select desired cells - home ribbon/expand
any section's settings to access format cells dialouge box - protection tab/untick locked.
-When the worksheet is protected these cells won't be affected.
[Link] Protecting The Whole Workbook
-00.16 Protecting workbook - review ribbon/protect workbook - ticking structure prevents the
creation of new worksheets.