Macros are disable by default in Excel 2010. To Enable Macros in Excel 2010 follow these steps.

Step 1– Select “File” located at the top left of the menu.

Step 2 – Select “Options” from the drop down menu.

1

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.

2

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).

3

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.

Step 1 – Open Excel workbook file.
Step 2 – Select “Tools” on the main toolbar.
Step 3 – Select “Macro”
Step 4 – Select “Security”.

1

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).

2

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.

The setting for enabling macros in Excel 2007 is hidden in the Developer tab. By default the Developer tab is not shown in the Office Ribbon.

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”.

1

Step 3 – Select “Popular” on the side and select the box next to “Show Developer tab in the Ribbon”, click OK.

2

Now the Developer tab will be showing in your Excel ribbon.

3

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).

4

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.

Excel offers many different charts to present data in many different ways, depending on what is needed.

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.

COUNT: Counts the number of numbers in a cell or a range of cells.

Syntax:
=COUNT (value1, value2, value3, value4…)
=COUNT (range)

1

COUNTA: Counts everything whether number or text in the range except blank cells.

Syntax:
COUNTA (value1, value2, value3, value4…)
COUNTA (range)

2

COUNTBLANK: Counts blank cells in a range.

Syntax:
COUNTBLANK (range)

3

What is a formula in Excel?

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.

HLOOKUP function is similar to the VLOOKUP function. The difference is the lookup table or range is arranged horizontally not vertically. HLOOKUP looks up the value in the first row of the range or table and returns the matching value in the identified range row.

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.

1

2

INDEX function is used to return a cell from a range or array.

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.

1

2

The Index function is frequently used together with the Match function as a substitute or companion to the VLOOKUP formula.

Excel’s indirect function is used to return the content of a specified reference and displays its content. This function allows an address of one cell to be put into another and retrieve data from the first cell by referencing the second.

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.

1

2

The lookup formula is used to return a value from a range or table by looking up another related value. In other words, Lookup formula returns a value from either a one-row or one-column range. Lookup formula works like VLOOKUP but is restricted to returning a value from the last column of a range.

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.

1

2

What you will learn in the section: What is a Macro? and how to record a simple Macro.

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.

1

Step 2 – Click on Record Macro. The window below will open.

2

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

3

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.

4

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.

5

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.

6

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.

Excel’s Match Function is used to get the relative position of a cell in an array or a range that matches a specified value.

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.

1

2

Offset function returns the address of a cell or a range of cells through the use of a reference cell.

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.

1

2

There is so much functionality with Excel, but you will notice that you will be doing some of the same things again and again. If you ever had to chance to watch an advance Excel user, you noticed their hands fly across the keyboard maneuvering all around the workbook performing various functions. Below are various shortcuts for you to practice to save you time and even impress co-workers, friends and family!

KeysDescription
CTRL +ASelects the entire worksheet. If other worksheets in the workbook contain data, CTRL+A selects the active worksheet only.
CTRL+BApplies or removes Bold formatting.
CTRL+CCopies the selected range.
CTRL+DFill down command copies the contents, format & formula of the topmost cell of a selected range to the cells below.
CTRL+FOpens the Find and Replace dialog box.
CTRL+GOpens the Find and Replace dialog box with Go To tab selected.
CTRL+HOpens the Find and Replace dialog box with Replace tab selected.
CTRL+IApplies or removes Italic formatting.
CTRL+KOpens the Insert Hyperlink dialog box for a new hyperlink.
CTRL+NCreates a blank new workbook.
CTRL+OOpens the Open dialog box to open or find a file.
CTRL+POpens the Print dialog box.
CTRL+RFill right command copies the contents, format & formula of leftmost cell of a selected range into the cells to the right.
CTRL+SSaves workbook or opens Save As dialog box.
CTRL+TOpens the Create Table dialog box.
CTRL+UApplies or removes Underline formatting.
CTRL+VPastes cut or copied object, text or cell contents at the insertion point and replaces any selection.
CTRL+WCloses the selected workbook.
CTRL+XCuts the selected range.
CTRL+YRepeats the last command or action.
CTRL+ZUndoes the last command or action.
SHFT+SPACESelects entire row.
CTRL+SPACESelects entire column.
CTRL+SHFT+DOWN ARROWSelects active cell to end of range in the column.
CTRL+ENDTakes you to the last row of range.
CTRL+PgUpSwitches between worksheet tabs, from left-to-right.
CTRL+PgDnSwitches 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+1Opens the Format Cells dialog box.
CTRL+2Applies or removes Bold formatting.
CTRL+3Applies or removes Italic formatting
CTRL+4Applies or removes Underline formatting.
CTRL+8Displays or hides the outline symbols.
CTRL+9Hides the selected rows.
CTRL+0Hides the selected columns.
CTRL+SHIFT+USwitches between expanding and collapsing of the formula bar.
CTRL+ALT+VOpens 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+ ARROWMoves you to the edge of the region in the direction of the arrow you chose.

Instead of having to have your spreadsheet written out by hand, Microsoft Excel provides a way to make an electronic spreadsheet. This program allows you to input your data into the rows and columns and execute mathematical calculations.
Typically the data in Excel is done in a list form. There may be times when the data will need to be sorted, summed, put into a chart or even filtered. With Excel tables, it makes it easier to perform those tasks. The data that is used to make a table in Excel typically comes from a series of a worksheet rows and columns.

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.

Pivot Table

1

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

2

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

3

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.

4

Next window

5

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

6

Now the report can be formatting any way you wish.

7

SUMIF is a formula that works as a conditional sum. All the values in the range that match the condition or criteria will be included in the sum. SUMIF is very useful for single criterion sum formulas.

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.

1

2

SUMIFS formula works as a conditional sum. All values in the range that meet the condition or criteria are included in the sum. SUMIFS is very useful for multiple criteria sum formulas.

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.

1

2

Microsoft Excel is an electronic spreadsheet that belongs to Microsoft Office. Microsoft Excel is used to store, organize and even manipulate data. In Excel you are able to keep track of data, perform difficult calculations, and also create charts from the data that is provided.