Quick Reference Study Notes for Microsoft Excel (Advanced)

Microsoft Excel

 

 

1) Introduction: Microsoft Excel is a tabulated sheet consisting of rows and columns. 

  • Rows are in a horizontal order and Columns are in a vertical order.

  • Rows are on the left side of the sheet and columns are on the top of the sheet.

  • Row numbers range from 1 to 1048576 and column number ranges from A to XFD i.e total of 16384 columns.

 

General Uses for Excel:

  • Budgets for household or business finances.

  • Invoices and receipts.

  • Tracking for projects, clients and customers, and health records.

  • Planners and calendars.

  • Checklists and task lists.

  • Financial, loan, debt, and mortgage calculations.

  • Inventory management.

 

a) Window Features: It enables you to perform routine tasks related to Microsoft applications. Here are some basic features of Microsoft Excel:

 


 

Term 

Description

Quick Access Toolbar

Displays quick access to commonly used commands like Save, Undo, Redo, etc.

Title Bar

Displays the name of the document.

File Tab


 

The File tab has replaced the Office button. It helps you manage the Microsoft application and access its options such as Open, New, Save, As Print, etc.

Name Box

Displays the active cell location.

Cell

The combination of a row and column; cells are always named with the column letter followed by the row number (e.g. A1 and AB209); cells may contain text, numbers, and formulas.

Range

One or more adjacent cells. A range is identified by its first and last cell address, separated by a colon. Example ranges are B6:B9, A2:B2, and A1:G245.

Status Bar

Displays information about the current worksheet.

New Sheet

Add a new sheet.

Ribbon

Displays groups of related commands within tabs. Each tab provides buttons for commands.

Formula Bar

Input formulas and perform calculations.

Worksheet

A grid of cells that are more than 16,000 columns wide (A-Z, AA-AZ, BA-BZ…IV) and more than 1,000,000 rows long.

View Option

Display worksheet view mode.


 

b) Create Worksheet: A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets. When you open an Excel workbook, Excel automatically selects Sheet1 for you. The name of the worksheet appears on its sheet tab at the bottom of the document window.

 

Steps to create a Worksheet:

  • Open Microsoft Excel.

  • Create a table of appropriate Rows and Columns.

  • Click on the calculation Cell, enter your formula.

  •   Press enter and you will get your result in that cell.

  • In this way, you can quickly create a Worksheet.

  • Save the file with .xlsx.

 

c) Commands on Cells: To alter the layout of a sheet, sometimes some commands need to be applied to the cells of a worksheet. 

 

i) Changing Column Width: 

I.    Move the mouse pointer onto the line between cells A and B, the cursor becomes a 

      double-headed arrow.

II.  Hold down the mouse button and drag the column divider to the right.

III. Release the mouse button and the column is resized.

IV.  Move the mouse pointer to the dividing line between the column headings as before.

V.   Double click on the mouse button.

Note: AutoFit Column Width is equivalent to double-clicking on the column border.  

          When using autofit, the column must first be selected or the width of the current    

          cell is used instead.

 

ii) Add Rows and Columns:  You can add rows and columns to the existing rows and columns.

 

Procedure

Description

Add Row

a) Select any cell of the row where you desire to add a new row above. 

b) On the Home ribbon in the Cell group, click on the Insert button, and then select Insert Sheet Rows. 

c) A new roll will appear above your selected cell row.

Add Column

a) Select any cell of the column letter where you desire to add a new column to the left.

 b) On the Home ribbon in the Cell group, click on the Insert button, and then select Insert Sheet Columns. 

c) A new column will appear to the left of your selected column.

Delete Row or Column

a) Select any cell where you desire to delete a row or column. 

b) On the Home ribbon in the Cell group, click on the Delete button, and then select Delete Sheet Rows or Delete Sheet Columns. 

c) The row or column where the cell was selected will be deleted.

 

2nd method to add rows and columns:

Add Columns: 

  • Select any cell in the column.

  • On the Home ribbon in the Cell group, click on the Insert drop-down arrow, and then select Insert Sheet Columns. A new column will appear to the left of your selected column.

Add rows:

  • Select any cell in a row.

  • On the Home ribbon in the Cell group, click on the Insert drop-down arrow, and then select Insert Sheet Rows. A new roll will appear above your selected cell row.

 

iii) Text and Number Alignment: 

  • Microsoft Excel aligns the data in three ways: center, left, and right. The default text alignment is left, and the default number alignment is right. You can change the alignment by selecting the alignment icons from the Home ribbon in the Paragraph group. Firstly select the cell range on which you want to apply the alignment.

 

iv) Format Fonts: 

  • Select the cell range on which you want to apply the format commands.

  • After Selecting the cell range from the Home ribbon you can change the font color, font size, font styles, etc. 

 

v) Format Numbers:

  • Excel provides many different types of numeric formats including currency, percent, comma, scientific, fraction, time, short date, long date, etc. 

  • On the Home ribbon, the numeric formats are located in the Number group. 

  • Select the drop-down arrow next to General to view all format types. Select a range of cell/s before choosing the format. This range can include cell/s that do not yet contain data.

 

2) Adding formulas in Excel: Microsoft Excel allows you to calculate results from entered data. To perform the calculations you need to add formulas in the formula bar. Excel provides many mathematical formulas, and these can be simple arithmetical formulas or complicated formulas involving conditional statements and nested functions. Steps to add formulas in Excel:

  • Every formula starts with an equal to (=) sign.

  • Arithmetic operators to perform basic calculations. 

I. Negation: A minus sign (-). This operation returns the additive inverse of the number represented by the numeric constant or cell reference following the minus sign. (The additive inverse is the value added to a number to produce a value of zero; it's the same as multiplying the number by -1.)

II. Percentage: The percent sign (%). This operation returns the decimal equivalent of the percentage of the numeric constant in front of the number.

III. Exponentiation: A caret (^). This operation raises the number represented by the cell reference or constant in front of the caret to the power of the number after the caret.

IV. Multiplication: An asterisk (*). An asterisk is used for multiplication.

V. Division: A forward-slash (/) is used for division. Multiplication and division have equal precedence and are performed from left to right.

VI. Addition: A plus sign (+).

VII. Subtraction: A minus sign (-). Addition and subtraction have equal precedence and are performed from left to right.

    

Function

Formula

Example

To add up the total

=SUM(Cell range)

=SUM(B2+B9)

To add individual items

=Value1 + Value2

=B2+C2

Subtract

=Value1 - Value2

=B2-C2

Multiply

=Value1 * Value2

=B2*C2

Divide

=Value1 / Value2

=B2/C2

Exponents

=Value1^Value2

=B2^C2

Average

=AVERAGE(cell range)

=AVERAGE(B2:B9)

Median

=MEDIAN(cell range)

=MEDIAN(B2:B9

Max

=MAX(cell range)

=MAX(B2:B9)

Min

=MIN(cell range)

=MIN(B2:B9)

 

  • Comparison Operators: In most cases, the comparison operators are used with the IF function. The comparison operators are listed below:

I.   Equals: An equal sign (=).

II.   Is not equal to (<>).

III.  Less than (<).

IV.  Less than or equal to (<=).

V.   Greater than (>).

VI. Greater than or equal to (>=).

 

  • Reference Operators: 

 

Operator

Description 

Formula 

: (Colon)

It’s a range operator. Which gives a range of cells within start point and endpoint reference cells

= SUM(A2:B6) #Provides all cells as a range starting from A2 to cell B6.

, (Comma)

Union Operator. This operator combines (as the word union suggests) two or more than two references into a single reference.

= SUM(A2:A6, A2:B3)

(space)

Intersection Operator. This operator only gives reference to those cells which are common in two range arguments.

= SUM(A2:A6 A2:B3)


 

3) Microsoft Excel basic functions:

 

a) SUM:

Formula: =SUM(5, 5) or =SUM(A1, B1) or =SUM(A1:B5)

It allows you to add 2 or more numbers together. You can use cell references as well in this formula.

b) AVERAGE: 

Formula: AVERAGE(A1:A10)

It helps you to find the arithmetic mean of a range of numbers. You can simply select that inbuilt formula and it will provide you the results.

c) MIN & MAX: 

Formula: 

=MAX(B2:B6

=MIN(B2:B6)

The MAX and MIN formulas in Excel get the largest and smallest value in a set of numbers, respectively. 

d) COUNT:

Formula: =COUNT(A1:A10)

The count formula counts the number of cells in a range that have numbers in them. It only counts the cells where there are numbers.

e) COUNTA

Formula: =COUNTA(A1:A10)

Counts the number of non-empty cells in a range. It will count cells that have numbers and/or any other characters in them.

f) LEN

Formula: =LEN(A1)

The LEN formula counts the number of characters in a cell. It includes spaces also.

g) TRIM

Formula: =TRIM(A1)

Gets rid of any space in a cell, except for single spaces between words. This formula is extremely useful where you pull data from a database and for some reason, extra spaces are put in behind or in front of legitimate data.

h) RIGHT, LEFT, MID

Formulas: = RIGHT(text, number of characters), =LEFT(text, number of characters), =MID(text, start number, number of characters).

These formulas return the specified number of characters from a text string. RIGHT gives you the number of characters from the right of the text string, LEFT gives you the number of characters from the left, and MID gives you the specified number of characters from the middle of the word.

i) VLOOKUP

Formula: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. You define a value (the lookup_value) for the formula to look for. It looks for this value in the leftmost column of a table (the table_array).

For Example,

VLOOKUP(F2, B1:C9,2,FALSE)

lookup_value: The cell where I would like to enter my formula.

table_array: Where the lookup value is to be searched in the column.

col_index_num: the column index is the argument where the index number of the column we are looking for is given.

range_lookup: We need to give Boolean character true or false. true is for the appropriate match, and False is for the exact match. For an appropriate match, it will look only for the first word and in the exact match, all the characters of the word should be matched.

j) if statement: 

Formula: 

=IF(logical_statement, return this if logical statement is true, return this if logical statement is false)

if(C2>D2, “Yes it is”,  “No it is Not”)

k) SUMIF, COUNTIF, AVERAGEIF: 

Formulas: =SUMIF(range, criteria, sum_range), =COUNTIF(range, criteria),       =AVERAGEIF(range, criteria, average_range)

l) Concatenation: 

Formula: 

=(A1&B1)

Ampersand (&): Use an ampersand (&) to join text strings together. Concatenation is the process to join two strings together. The ampersand is known as a text operator to join strings together in Excel formulas. 

m) AND & OR:

Formula: 

=IF(AND(B2>60, B2>60), "Pass", "Fail")

=IF(OR(B2>60, B2>60), "Pass", "Fail")

These are the two most popular logical functions to check multiple criteria. The difference is how they do this:

AND returns TRUE if all conditions are met, FALSE otherwise.

OR returns TRUE if any condition is met, FALSE otherwise.

n) TODAY & NOW:

Formula: 

To see the current date and time whenever you open your worksheet without having to manually update it daily, use either:

=TODAY() to insert today's date in a cell.

=NOW() to insert the current date and time in a cell.

 

4) Charts in MS Excel: A chart is a visual representation of data in both columns and rows. Charts are usually used to analyze trends and patterns in data sets. 

 

a) Types of Charts: Excel provides the different types of charts. Depends upon your data visualization you can select any kind of chart. The following table shows some of the most commonly used charts:

 

Chart Type

When to use? 

Example

Pie Chart

You can use this type of graph in which a circle is divided into sectors that each represent a proportion of the whole.

Bar Chart

Numerical values of variables can be represented by the height or length of lines or rectangles of equal width.

Column Chart

When you want to display the comparisons among subjects on an overall chart. They are often used to show data comparisons in a visual way.

Line Chart

You will use a line chart when you want to emphasize changes in values for one variable for continuous values of a second variable 

Combo Chart

Combination Charts allow the reader to study data to determine if there is a relationship between the two data sets.


 

i) Steps to create a Pie Chart: A pie chart is a circular graph that shows the proportions of categories based on the size of each slice. The entire pie represents 100 percent, and each slice represents a portion of a whole. 

  • Select the range (You can select only one row with its labels).

  • On the Insert tab, in the Charts group, click the Pie symbol.

  • Click Pie.





 

You can perform some changes in the chart:

  • Select the pie chart.

  •  Click the + button on the right side of the chart and click the check box next to Data Labels.

  • Click the paintbrush icon on the right side of the chart and change the color scheme of the pie chart.

  • Right-click the pie chart and click Format Data Labels.

  • Check Category Name, uncheck Value, check Percentage and click Center.

  • Right-click the data labels and click Font to change the font size and font color of the data labels.

 

 

Bear

Dolphin

Whale

2018

54

77

54


 

 

ii) Steps to create a Bar Chart: 

  • Select the range (Example, A1:D6)

  • On the Insert tab, in the Charts group, click the Column symbol.

  • Click clustered bar. 


 

 

Bear

Dolphin

Whale

2017

68

70

72

2018

84

89

90

2019

67

70

73

2020

76

78

79


 

 

iii) Steps to create a Column Chart: 

  • Select the range.

  • On the Insert tab, in the Charts group, click the Column symbol.

  • Click clustered bar. 


 

 

Bear

Dolphin

Whale

2017

68

70

72

2018

84

89

90

2019

67

70

73

2020

76

78

79

 


 

Difference between Bar Chart and Column Chart: Both the Bar and the Column charts display data using rectangular bars where the length of the bar is proportional to the data value. Both the charts are used to compare two or more values. However, the difference lies in their orientation. A bar chart is oriented horizontally whereas the column chart is oriented vertically.

 

iii) Steps to create a Line Chart: 

  • Select the range.

  • On the Insert tab, in the Charts group, click the Line symbol.

  • Click Line with Markers.

 

 

Bear

Dolphin

Whale

2017

68

70

72

2018

84

89

90

2019

67

70

73

2020

76

78

79

 

iii) Steps to create a Combo Chart: 

  • Select the range.

  • On the Insert tab, in the Charts group, click the Combo symbol.

  • Click Create Custom Combo Chart.


 

 

Bear

Dolphin

Whale

2017

68

70

72

2018

84

89

90

2019

67

70

73

2020

76

78

79

 


 

5) Microsoft Excel Keyboard shortcuts:


 

General Shortcuts:

Ctrl+N

Create a new workbook

Ctrl+O

Open an existing workbook

Ctrl+S

Save a workbook

Ctrl+S

Save a workbook

F12

Open the Save As dialog box

Ctrl+W

Close a workbook

Ctrl+F4 

Close Excel

F4

Repeat the last command or action. For example, if the last thing you typed in a cell is “hello,” or if you change the font color, clicking another cell and pressing F4 repeats that action in the new cell.

Shift+F1 

Insert a new worksheet

Ctrl+Z

Undo an action

Ctrl+Y

Redo an action

Ctrl+F2 

Switch to Print Preview

F1

Open the Help pane

F7

Check Spelling

Shift+F9

Calculate active worksheets

Ctrl+F1

Show or hide the ribbon

Ctrl+Shift+U

Expand or collapse the formula bar

Ctrl+F9

Minimize the workbook window

F11

Create a bar chart based on selected data (on a separate sheet)

Alt+F1

Create an embedded bar chart based on select data (same sheet)

Ctrl+F

Search in a spreadsheet, or use Find and Replace

Alt+F

Open the File tab menu

Alt+H 

Go to the Home tab

Alt+N 

Open the Insert tab

Alt+P

Go to the Page Layout tab

Alt+M

Go to the Formulas tab

Alt+A

Go to the Data tab

Alt+R

Go to the Review tab

Alt+W

Go to the View tab

Alt+X

Go to the Add-ins tab

Alt+Y

Go to the Help tab

Ctrl+Tab

Switch between open workbooks

Shift+F3

Insert a function

Alt+F8

Create, run, edit, or delete a macro

Alt+F11

Open the Microsoft Visual Basic For Applications Editor

Moving around in a Worksheet

Left/Right Arrow

Move one cell to the left or right

Up/Down Arrow

Move one cell up or down

Ctrl+Up/Down Arrow

Move to the top or bottom cell in the column

Tab

Go to the next cell

Shift+Tab 

Go to the previous cell

Ctrl+Home

Move to the beginning of a worksheet

Page Up/Down 

Move one screen up or down in a worksheet

Alt+Page Up/Down

Move one screen to the right or left in a worksheet

Ctrl+Page Up/Down 

Move to the previous or next worksheet

Selecting Cells

Shift+Left/Right Arrow 

Extend the cell selection to the left or right

Shift+Space

Select the entire row

Ctrl+Space 

Select the entire column

Ctrl+Shift+Space 

Select the entire worksheet

Editing Cells

F2

Edit a cell

Shift+F2 

Add or edit a cell comment

Ctrl+X 

Cut contents of a cell, selected data, or selected cell range

Ctrl+C or Ctrl+Insert 

Copy contents of a cell, selected data, or selected cell range

Ctrl+V or Shift+Insert 

Paste contents of a cell, selected data, or selected cell range

Ctrl+Alt+V

Open the Paste Special dialog box

Delete 

Remove the contents of a cell, selected data, or selected cell range

Esc 

Cancel an entry in a cell or the formula bar

Enter

Complete an entry in a cell or the formula bar

Formatting Cells

Ctrl+B 

Add or remove bold to the contents of a cell, selected data, or selected cell range

Ctrl+I

Add or remove italics to the contents of a cell, selected data, or selected cell range

Ctrl+U

Add or remove underline to the contents of a cell, selected data, or selected cell range

Ctrl+Shift+& 

Apply outline border

Ctrl+Shift+_ (Underline) 

Remove outline border

Ctrl+9 

Hide the selected rows

Ctrl+0 

Hide the selected columns

Ctrl+1

Open the Format Cells dialog box

Ctrl+5 

Apply or remove strikethrough

Ctrl+Shift+$ 

Apply currency format

Ctrl+Shift+% 

Apply percent format

 

This website uses cookies to improve user experience. By using our website you consent to all cookies in accordance with our Cookie Policy. More info. I Agree