EXCEL NOTES

10 BEST EXCEL TIPS FOR

BEGINNERS
TIP 1: Quick Analysis
Quick analysis pops ups when you highlight/select 2. Then go to Data
all the items in the excel table

When you click the quick analysis icon, a list of

3. Click on Filter
analysis styles appears that you can use on your
table.

4. When you click the filter, you'll find that the

tables you have appear to have drop down
menus

Clicking on the quick analysis icon will let you

have access to different styles of formatting, charts, 5. For example, let’s say that we need to find a
totals, tables and sparklines. movie that was released in 2011. Go to the
drop-down menu that aligns with the listed
TIP 2: Filter Feature year and select the year 2011.
This is useful when you have a table with a lot of
information, and you need to find specific item/s.
Here’s how to do it:
1. Click inside the table

6. Once you click ok, it will show you the

specific movie that was released in 2011
 An example of this is when you are handling email.
Type in the name in the format (first name, Last
TIP 3: Control Key
name)
To move from one table to another in Excel, utilize
the standard arrows. Holding down the "ctrl" key
while using the arrow keys on your keyboard, on
the other hand, will allow you to navigate about
your table more quickly.
TIP 4: Adjusting column widths
When your columns aren't wide enough, this comes
in handy. You can double-click between the Do this for the following rows. You will notice that
columns instead of manually doing it. there will be choices of names that will pop up with
the same format to fill in the rest of the column.
When you hit enter, it will fill in the column based
on the pattern.

Another tip that is very useful is by selecting all

item starting from the upper left

Auto fill
This is useful if you want to fill in the columns
faster. This can be done with numbers and dates.
Then double clicking any column will result to the
below. This will make your table more organized
and cleaner.

To fill in the column, click on the lower right corner

then drag it to the column you want the number to
end.

TIP 5: Flash fill and auto fill

Flash fill and auto fill are excel features that are
useful as it is a time saver. It will look like the image below once done.
Flash fill
 This is a relative cell because as you click on each
amount you will notice that the formula for each is
the same when you copied it all down.

This can also be done with number patterns such as

1, 2, 3, 4, and so on. To do so, enter 1 in the first
column and 2 in the second column. Highlight both
columns and drag them all the way down to fill in
the remaining columns so it can recognize the
pattern you wish to do. RELATIVE REFERENCE

TIP 6: Absolute cell reference

Absolute reference cell vs. relative reference cell
This is a relative reference because as you can see,
There are two types of cell references: relative and the value under discount has an error. You need to
absolute. Relative and absolute references behave make this an absolute reference to get the correct
differently when copied and filled to other cells. computations.
Relative references change when a formula is
To make this an absolute reference you need to fix
copied to another cell. Absolute references, on the
the formula. Add a $ sign before the letter and
other hand, remain constant no matter where they
before the number of the value you are multiplying
are copied.
it to.
Reference:
http://sites.isdschools.org/hselectives_business/useruploads/office_pr
oductivity_suite/MicrosoftOfficeSuite_Becker_May13.pdf

Absolute references are noted by a dollar sign in

front of the cell reference. So, a relative cell
reference might look like =A1+A2 but an
absolute reference might look like =$A$1+$A$2.
RELATIVE REFERCE
After changing the formula of F4 cell, you can now
copy it down and will get the correct values. You
will also notice after copying it down that the
formula for each cell is now changed and has the
same formula with cell F4.
TIP 7: Transposing tables in Microsoft Excel
This is when you want to transpose your columns to Step 4: Right click and select “Page Special”
rows, and rows to columns.
Example:
Original table

Step 5: Tick on “Transpose” and click “ok”

Procedure for transposing:

Step 1: Select the whole table

Step 6: You will notice that the formatting is not

correct.

Step 2: Copy the table

To fix this, select the whole table by double clicking

the below

Then, double click the between the column K and L

Step 3: Choose an empty column where you want (this is where you pasted your transposed table)
the table to be transposed
As you can see below, the tables have now been
transposed.

NOTE: When copied into the spreadsheet

TIP 8: Text columns remember that the items will not be pasted into
separate columns.
Let’s say you have a document in a word type and
you want this to be transferred into an excel Step 3: Highlight the column where you pasted
spreadsheet. your items
Procedure:
Step 1: Copy the items from your word document

Step 4: Go to “data” and click on “text to column”

Step 5: Tick on “delimited”

Step 2: Paste it into excel worksheet
Step 6: Hit next then and make sure the comma is
ticked and click next then finish

Step 3: Select the document or image you want

excel to take a screenshot of. You can crop and
resize the image you chose.
NOTE: You can also choose the “screen
clipping” under screenshot where you can a
screenshot of an image from a web.
TIP 10: Show formulas
This tip is best to use when you want to look at the
formulas of your worksheet. To do this, click on
your worksheet then hold ctrl + ~

TIP 9: Inserting a screenshot

Step 1: Go to insert

Step 2: Click on screenshot

 Procedure:
Step 1: Click on the row you want to format then
click on the format painter. Let’s say you want to
copy the format of the cell “Movie Inventory”

Step 2: Then, move your mouse to the cell you

want to paste the format you copied.

Intermediate Excel Skills, Tips, and

Tricks Tutorial
TOPIC 1: FORMATTING OPTIONS
Format Painter Conditional Formatting
This will allow you to copy a format of a cell. This is located under the Home tab in the styles
This is located under Home tab under the clipboard group
group.
Procedure Select an option you prefer.

Step 1: Select a column or a row

For this example, we will be selecting column D

So, what this does is it gives color green to the

highest number then red to the lowest number. Then
everything between the highest and lowest number
has colors adjusted accordingly.
Step 2: Click on “Conditional formatting”. Once
clicked, it will give you formatting options ADDITIONAL FORMATTING
The image below shows you a general format.

For this example, let’s you chose color scales. Click

on it then you will be given other options you can
choose from.
On the image below, this is where you can look at Your column will now appear to be in an
additional kinds of formatting. accounting condition. If you merely want it to stay
as currency, select currency from the drop-down
arrow near accounting.

TOPIC 2: FORMULAS
Let’s say you want to change the format to
currency. Highlight the columns then click on the $ Formulas are the true power of excel.
sign.
For this example, we will be inputting a formula
find out the total amount how much the collection is
worth.
To get the sum of the whole collection, click on the
cell at the end of all the amounts then enter
=sum(D3:D22)
NOTE: D3 because the first listed amount is
under column 3 and the amount is listed on row
3. This is up to D22 because the listed amount
ends on that column and cell.

It will now look like this.

After hitting enter, the sum will be computed

 OTHER METHODS ON HOW TO GET THE AVERAGE FORMULA AND AUTO
SUM: AVERAGE
Highlight and enter The formula for getting the average is
=average(D3:D22)
Another method is to select the initial cell with the
amount, drag it to the end, and then press enter. You
will achieve the same outcome by doing so.
Auto sum
Procedure:
Step 1: Click on the cell

Auto Average
You can also click on the drop-down symbol for
sum and an option for average will be available.

Step 2: Within the home tab under the editing

group, you will find a symbol that represents sum.
Click on it a shortcut will appear Getting the highest and lowest value

Formula for highest value

NOTE: Sometimes auto-sum will give you
incorrect computations. It is always best to =max(D3:D22)
double check.
Formula for lowest value
Keyboard shortcut for getting the sum
=min(D3:D22)
Alt + then hit enter
Result Results:
Before:

Another way to look at formulas is under the

formula tab. It will show you different options and After:
formulas you can use.

TOPIC 3: SORTING
Sorting alphabetically
Procedure:
Step 1: Click on the first cell then on the home or
data tab, you will be able to find the “sort & filter” You will see that your list of movies is now sorted
button. But for this example we will be using sort and arranged alphabetically.
under the data tab. TOPIC 4: FILTERING
Filtering is a way to eliminate parts of your
spreadsheet temporarily so that you can see data
specifically.
Procedure:
Step 1: Highlight the heading of your worksheet

On the data tab

Step 2: We will be sorting from A to Z. Go to the

data tab. Look for the sort button and beside it there
is a option AZ and ZA. Click on AZ
 Step 5: Then scroll down and look for PG then tick
on it’s box then click ok We will tick on PG
Step 2: Under the data tab, click on filter. You will
because we want to eliminate PG-13 hence showing
notice that your header will appear to have drop-
only the PG movies.
down buttons.

Step 3: For this example, let’s eliminate all the PG- Results:
13 movies. Click on the drop-down button under
rating.

NOTE: This will only TEMPORARILY hide

PG-13 movies. PG-13 movies will not be
Step 4: Untick “select all” completely lost.
To reverse the filter and to let you see all the data
click on the funnel button on the lower right on
rating
Then tick the box for select all Step 4: Under the view tab click on freeze panes

Results: This will show you 3 options but just click on

“Freeze panes”

TOPIC 5: FREEZE PANES

The Excel Freeze Panes option allows you to lock
your columns and/or rows so that when you scroll Result:
down or over to view the rest of your sheet, the
column and/or row will remain on the screen. As you browse down through your worksheet, the
https://www.simplilearn.com/tutorials/excel-tutorial/how-to-freeze-panes-in-excel#:~:text=The
header will always be visible. That’s because we
%20Excel%20Freeze%20Panes%20option,will%20remain%20on%20the%20screen. froze everything above row 3 which is the header.
Let’s say you have a big spreadsheet and that you
are inputting data, but you want the headers to still
be visible so that you won’t be entering data on the
wrong column.
For this example, we will be freezing the header
which are the title, date purchased, rating, value,
genre and etc. follow the procedure below.
Step 1: The header is in row 2. So we need to click
below row 2 which is row 3.
The Ultimate Excel Tutorial - Beginner to
Advanced - 5 Hours
Agenda on this topic:
 What are formulas and functions
 How calculations work
 Understanding Operators and the BODMAS
rule
 Using COUND and COUNTA
 Using Named Ranges in formulas
 Useful Functions- Text Functions,
UNIQUE, CONCAT
 Logical Formulas (IF) and Nested IF
 VLOOKUP and Error Checking Formulas
 Useful keyboard shortcuts, tips and tricks

TOPIC 1: EXCEL FORMULAS FOR

BEGINNERS
There are more than 500 formulas in excel and you
can find this in the formula tab.
Operators

BODMAS /PIDMAS RULE

Just like in math class there are rules we follow.

Typically, in math class the rule is PEMDAS
whereas the order we follow is Parentheses,
exponents, multiplication, and division. In excel it’s
BODMAS or PIDMAS
 TOPIC 2: COUNT VS COUNTA
The COUNT function is generally used to count a
range of cells containing numbers or dates
excluding blanks.
Formula: =COUNT(B4:B9)
COUNTA, on the other hand will count
everything... numbers, dates, text or a range
containing a mixture of these items but does not
count blank cells. COUNTA stands for count all.
Formula: =COUNTA(B4:B9)
https://www.stl-training.co.uk/fg-17/what-is-difference-between-count-and-counta-in-
excel.html#:~:text=The%20COUNT%20function%20is%20generally,does%20not
%20count%20blank%20cells.

Formula:

C4:C20 is the range

*>=80* stands for greater than or equal to 80
Result:

The image above shows that COUNT is 17 because

it counted the test score which has 17 items.
COUNTA on the other hand is also 17 because it
counted how many students are listed.
ADDITIONAL TOPIC: COUTIF
COUNTIF is an Excel function to count cells in a
range that meet a single condition. COUNTIF can
be used to count cells that contain dates, numbers,
and text. The criteria used in COUNTIF supports
logical operators (>,<,<>,=) and wildcards (*,?) for
partial matching.
For this example, we will be finding how many
students have scored greater than or equal to 80.