Syntax
MATCH (lookup_value, lookup_array, match_type)
Arguments
Lookup_value – The value you want to match in lookup_array.
Lookup_array – The range being searched.
Match_type – An integer: -1 (lookup_array has to be in descending order), 0 or 1 (lookup_array has to be in ascending order).
-1 – Match finds the position of the smallest value greater than or equal to the lookup_value.
0 – Match finds the position of the first value exactly the same as the lookup_value.
1 – Match finds the position of the largest value greater than or equal to the lookup_value.
Note: If the match_type argument is left out, it is assumed to be 1.
Step 2 – Select “Tools” on the main toolbar.
Step 3 – Select “Macro”
Step 4 – Select “Security”.
Step 5 – On the Security Level tab, select the “Low” option, then click OK.
Excel will inform you this is not recommended and potentially dangerous, however your virus program should be adequate to stop any Excel viruses(Excel virus are relatively rare these days).
Step 6: Close Excel and then reopen. Macros are now enabled in your workbook!
If you run into any problems opening or running file with macro’s, make sure you save the file to your hard drive then open it using Excel or navigate and click on it from My Computer instead of trying to run directly from your email.
How to show developer tab in Ribbon if it shown:
Step 1 – Click Office (four colored squares) icon at top left of Excel.
Step 2 – Select “Excel Options”.
Step 3 – Select “Popular” on the side and select the box next to “Show Developer tab in the Ribbon”, click OK.
Now the Developer tab will be showing in your Excel ribbon.
Now let’s go over how to enable macros.
Step 1 – Click on the Developer tab and select “Macro security”
Step 2 – Check the “Enable all macros” option and click OK.
Excel will inform you this is not recommended and potentially dangerous, but your virus program should be adequate to stop any Excel viruses (Excel virus are relatively rare these days).
Step 3 – Close Excel and reopen. Now macros are enabled in your workbook!
If you run into any problems opening or running file with macro’s, make sure you save the file to your hard drive then open it using Excel or navigate and click on it from My Computer instead of trying to run directly from your email.
Step 1– Select “File” located at the top left of the menu.
Step 2 – Select “Options” from the drop down menu.
Step 3– Select “Trust Center” on the left side at the bottom
Step 4 – Select the “Trust Center settings…” button on the far right in the window.

Step 5 – A pop-up will appear, as seen below.
Step 6 – Select “Macro Settings” from the left sidebar
Step 7 – Choose “Enable All macros” option and then click OK.
Excel will tell you this is not recommended and potentially dangerous, but your virus program should be adequate to stop any Excel viruses (which are relatively rare these days).
Step 8 – Close Excel and reopen. Macros will now be enabled in your workbook!
If you have any problems opening or running a file with macro’s, also make sure you save the file to your hard drive and open using Excel or navigate and click on it from My Computer, instead of trying to run directly from your email.
Syntax:
=COUNT (value1, value2, value3, value4…)
=COUNT (range)
COUNTA: Counts everything whether number or text in the range except blank cells.
Syntax:
COUNTA (value1, value2, value3, value4…)
COUNTA (range)
COUNTBLANK: Counts blank cells in a range.
Syntax:
COUNTBLANK (range)
It is a way for you to make calculations on data that is already written in the spreadsheet.
What’s best about formulas is that they are linked to the data in such a way that if the data changes, it will show you an updated result, reflecting the change.
The fill handle is especially useful, as often you need to repeatedly copy the same formula across the worksheet. In such cases using the handle can be a great time saver.
One of the important parts of learning formulas is understanding the concepts of absolute referencing and relative referencing. Take the time to watch the video dealing with it, and the next one with simple examples demonstrating how it is been practically implemented.
The data used in the formulas does not have to appear only in the same worksheet. You can use values from cells from other worksheets inside a single formula, and so integrate data from multiple worksheets.
Syntax: HLOOKUP (lookup_value,table_array,row_index_num,range_lookup)
Lookup_value – The value you are trying to find in a range or table.
Table_array – The range where you are looking for a value to match the lookup_value.
Row_index_num – The row number within the range from where the matching value would be returned.
Range_lookup – Optional. If TRUE or 1 or omitted, an approximate match is returned by the formula. If FALSE or 0, Hlookup will search for an exact match. If an exact match is not found, the formula would return error as #N/A.
Syntax
INDEX (array, row_num, column_num)
Arguments
Array – The range or table
Row_num – A row number within array
Col_num – A column number within array
Note that if array contains only one row or column the row_num or column_num argument is optional.
The Index function is frequently used together with the Match function as a substitute or companion to the VLOOKUP formula.
Syntax
INDIRECT (string_reference, ref_style)
Arguments
String_reference – The cell that Indirect will look at to find the value.
Ref_style – Optional. Either TRUE or FALSE: TRUE represents the string_reference is interpreted as an A1 style reference; FALSE represents that string_reference is interpreted as a R1C1 style reference.
Note: If the ref_style is left blank, the Indirect function will interpret string_reference as an A1 style.
Basic LOOKUP formula –
HLOOKUP
VLOOKUP
LOOKUP
MATCH and INDEX together
Basic difference between VLOOKUP and HLOOKUP:
VLookup and HLookup are both lookup formulas and used to find specific data located in the table or a range of data. The only difference between the two is if your data is organized in vertical order or in columns, VLOOKUP is used; and if the data is organized in horizontal order or in rows, HLOOKUP is used.
Syntax
LOOKUP (value, lookup_range, result_range)
Arguments
Value – The value to be looked up in the lookup_range.
Lookup_range – The single-column or single-row range that contains the values to be looked up.
Result_range – The single-column or single-row range that contains the values to be returned – has to be the same size as the lookup_range.

Brief introduction of Macro
Macro is a very powerful productivity tool. A macro provides sequence of instructions or set of tasks combined so to run or replay an entire sequence or a set with a single command. A Macro can be created, for example, to print or format a sales report. When a Macro is developed, one click will allow you to execute the Macro to perform the time-consuming procedure automatically.
VBA is the language that is used to create a Macro. In Excel, the Record Macro feature on the Developer toolbar is a helpful feature. Without knowing VBA language you can create simple and useful Macros. For complex programming you have to have an understanding of VBA language. In summary, this is one of the most powerful tools in MS Excel.
What can we do with Macro’s?
Macros can be used for anything and everything that is repetitive. For example, if you are preparing the same kinds of reports on a daily basis, Macros provides the ability to prepare the reports with just the click of your mouse.
How to record a simple Macro?
Step 1 – Open Excel workbook, select worksheet 1 and go to Developer tab.
Step 2 – Click on Record Macro. The window below will open.
Macro name – You can replace the name of the Macro to be recorded to fit your needs. Macro1, Macro2, etc. is the default name.
Shortcut Key – You can assign the macro to a shortcut key; for example, if you type A in the box, you can execute or run the macro by pressing Ctrl+A.
Store macro in – This is the name of the workbook where you want to keep the macro, e.g. This workbook, New workbook, etc.
Description – You can define the macro so it shows what will happen when the user runs the macro.
Click OK to record the macro.
Step 3 – write at Range A1 = Workbook, B1 = Worksheets, C1 = Range, then select worksheet select and select Range A1:D1.
Now click on Stop Recording
Step 4 – You have created a macro by the name of Macro1. To run this macro, click on Macros or press the shortcut key if you have assigned one.
Step 5 – Select the Macro you have created and click on Run. This macro will do the same task you did manually in worksheet one and worksheet two. In simple words, this recorder tool has recorded the steps you performed in the Excel workbook, and when you click on the Run button it will do the same thing.
Below is the code of the recorded macro. This is just example of basic steps for how to record a macro. Using the same process, you can record anything you do in Excel and get the valuable code.
Now you understand the basics of Excel Macro’s. For the next step, see the page on VBA, the programming language for Excel Macro’s.
Pie Charts: Are used to represent percentages.
Column Charts – aka Bar Graphs: Is used to evaluate data between different items.
Pictograph – Pictogram: A column chart that contains pictures instead of columns to represent the data.
Bar Charts: Column charts that run horizontally instead of vertical.
Line Charts – aka Line Graphs: is used to show how trends are over a period of time.
Combo Charts: A combination of two charts in one display. You will typically see a line graph and a column chart. Excel will make use of a third axis, named secondary Y axis, it will run up the right side of the chart.
Stock Market Charts: Just as it sounds. It shows information about stocks or shares.
How to Create a Chart in Excel 2013
Step 1 – Select the data that is to be used to create the chart.
Step 2 – Select the Insert Tab, located on the Ribbon
Step 3 – Click on Recommend Charts, just under the View tab.
Step 4 – Excel has selected different charts it recommends for your data. Select any chart you want to preview and it will show you how the chart will appear with the data provided. If you do not see any charts that are recommended go to All Charts and select an option from there.
Step 5 – Once you have found the chart, select “OK”
Now that the Chart is made, use the Chart Elements, Chart Styles, and Chart Filter buttons that can be found at the upper-right corner of the chart. To add elements to the chart like the axis title or the data labels, to customize the look of the chart, or to modify data that is in the chart.
For additional design and formatting features, select anywhere in the chart to add the Chart Tools to the Ribbon up top. You can now choose between the Design and Format tabs to make any changes to the chart.
Syntax
OFFSET (range, rows, columns, height, width)
Arguments
Range – The starting range from which we are going to use offset.
Rows – The number of rows we use in offset to the range. It can be a positive or negative number.
Columns – The number of columns we use in offset to the range. It can be a positive or negative number.
Height – The number of rows that you want to return as a range.
Width – The number of columns that you want to return as a range.
Keys | Description |
CTRL +A | Selects the entire worksheet. If other worksheets in the workbook contain data, CTRL+A selects the active worksheet only. |
CTRL+B | Applies or removes Bold formatting. |
CTRL+C | Copies the selected range. |
CTRL+D | Fill down command copies the contents, format & formula of the topmost cell of a selected range to the cells below. |
CTRL+F | Opens the Find and Replace dialog box. |
CTRL+G | Opens the Find and Replace dialog box with Go To tab selected. |
CTRL+H | Opens the Find and Replace dialog box with Replace tab selected. |
CTRL+I | Applies or removes Italic formatting. |
CTRL+K | Opens the Insert Hyperlink dialog box for a new hyperlink. |
CTRL+N | Creates a blank new workbook. |
CTRL+O | Opens the Open dialog box to open or find a file. |
CTRL+P | Opens the Print dialog box. |
CTRL+R | Fill right command copies the contents, format & formula of leftmost cell of a selected range into the cells to the right. |
CTRL+S | Saves workbook or opens Save As dialog box. |
CTRL+T | Opens the Create Table dialog box. |
CTRL+U | Applies or removes Underline formatting. |
CTRL+V | Pastes cut or copied object, text or cell contents at the insertion point and replaces any selection. |
CTRL+W | Closes the selected workbook. |
CTRL+X | Cuts the selected range. |
CTRL+Y | Repeats the last command or action. |
CTRL+Z | Undoes the last command or action. |
SHFT+SPACE | Selects entire row. |
CTRL+SPACE | Selects entire column. |
CTRL+SHFT+DOWN ARROW | Selects active cell to end of range in the column. |
CTRL+END | Takes you to the last row of range. |
CTRL+PgUp | Switches between worksheet tabs, from left-to-right. |
CTRL+PgDn | Switches between worksheet tabs, from right-to-left. |
CTRL+SHIFT+@ | Applies the Time format with the hour, minute and AM or PM. |
CTRL+SHIFT+: | Enters the current time. |
CTRL+SHIFT+” | Copies the value from the cell above the active cell into the active cell or the Formula Bar. |
CTRL+SHIFT+Plus (+) | Opens the Insert dialog box to insert blank cells. |
CTRL+Minus (-) | Opens the Delete dialog box to delete the selected cells. |
CTRL+; | Enters the current date. |
CTRL+` | Alternates between displaying cell values and displaying formulas in the worksheet. |
CTRL+1 | Opens the Format Cells dialog box. |
CTRL+2 | Applies or removes Bold formatting. |
CTRL+3 | Applies or removes Italic formatting |
CTRL+4 | Applies or removes Underline formatting. |
CTRL+8 | Displays or hides the outline symbols. |
CTRL+9 | Hides the selected rows. |
CTRL+0 | Hides the selected columns. |
CTRL+SHIFT+U | Switches between expanding and collapsing of the formula bar. |
CTRL+ALT+V | Opens the Paste Special dialog box. Available only after you have cut or copied an object, text, or cell contents on a worksheet or in another program. |
CTRL+ ARROW | Moves you to the edge of the region in the direction of the arrow you chose. |
Some Benefits of using Excel Tables
Simple sorting and filtering: At the top of the tables Excel adds filter controls. This features allows you to sort and filter the information quick and easy.
Add New Data Easily: Excel will extend the table to any data that is added just below the last row of the table.
Formula Autofill: When you add new data into a row, not only will the table extend to include that information, but all the formulas will Autofill so you do not have to.
Simple formatting: New tables in Excel will automatically receive the banded row format. There are other formats that are also available in the “Table Styles” gallery on the contextual “Design” tab if you do not like the one that is used.
Create an Excel Table
Step 1 – Select a cell in the data that you want to make into a table
Step 2 – On the Ribbon tab, select “Insert”
Step 3 – Click on “Tables”
Step 4 – A dialog box will appear with the range of data to make the table. If needed you can adjust the range of the table. Just below the range you will notice the option “My Table has Headers”. You can choose whether or not you want that selected.
Step 5 – Select “OK”
Now you have a table with your data.
A Pivot Table is a tool, perhaps the most technological component in Excel. It helps us summarize huge data in rows and columns into an appealing report. With only a few clicks of the mouse, and no complex formulas, data is summarized into thousands of rows to show sums, averages, counts and other calculations.
A Pivot Table is an effective and easy way to slice and dice a large amount of data. Using Pivot Table, we can manipulate the data inside out, upside down, sideways and backwards to see the business trends.
Benefits of Pivot Table are: Information can be arranged almost any way, the ability to insert complex formulas to perform new calculations, and the ability to format it to convert into a nice-looking report.
Below are common uses of a Pivot Table:
- Transposing data – i.e. changing rows to columns or columns to rows. Listing unique values in any column of a table.
- Sub-totals, custom formats and make dynamic pivot chart.
- Filtering, sorting and drilling-down data in the reports, no need for any formulas or macro.
- Linking data sources outside Excel to make a pivot table on that data.
How to create a simple pivot table using the data below
Assume the above table has thousands of rows and we want to get a report for a customer’s monthly saving account. Use the following steps to get the dynamic report:
Step 1 – Select the data
Step 2 – Go to Insert -> Select PivotTable
Step 3 – Create PivotTable Window is now open. Check Table/Range, the range should be all the data that was selected.
Step 4 – Select the location of the PivotTable, either New Worksheet or if you choose Existing worksheet make sure to include the range in the Location. Then select OK.
Next window
Step 4 – Select the report for Customers. Drag CustomerName into the Row Labels area. The Pivot will remove any duplicates from CustomerName. Next drag Month to the Column Labels and the Savings to the Values area. Other functions can be used in the Values areas, like Count, Average, etc
Syntax:
SUMIF (range, criteria, sum_range)
Range – The area containing values that will determine whether to include a particular cell in the sum.
Criteria – An expression that determines whether to include a particular cell in the sum.
Sum_range – The area containing the cells to sum.

SUMIF and SUMIFS differences:
- SUMIF is available in all versions of Excel. Whereas SUMIFS is only available in Excel 2007 and later versions.
- Using SUMIF, we can add a single continuous range based on one specified range with one criterion only. Using SUMIFS, we can use multiple continuous ranges of the same size with multiple specified ranges equal to the number of criteria applied.
Syntax
SUMIFS (sum_range, criteria_range1, criteria1, criteria_range2, criteria2,….)
Sum_range – This is the one or more cells to sum – including numbers, names, ranges, or cell references – that contain numbers.
Criteria_range1 – This is the first range where we will evaluate the criteria associated with it.
Criteria1 – This is the first criteria. It defines which cells in the criteria_range argument would be added, and can be in the form of expression, number, cell reference or text.
Criteria_range2, Criteria2,… – This is optional. These are additional ranges and their respective criteria. 127 (criteria_range#, criteria#) pairs are allowed.