Excel Match

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.

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.