• Author / Uploaded
• mohamad

Citation preview

Applied Technology High School

Programming Excel using VBA G 10 ATHS Regular & ASP CSC 50

Basics

Functions

Data Analysis

VBA

Applied Technology High School

2015-2016

Contents 1. Microsoft Excel fundamentals 1.1. Setting up workbook 1.1.1 Modifying the Display of the Ribbon 1.1.1.1

Minimize the Ribbon

1.1.1.2

Customize the Ribbon

1.1.2 Create and modify workbooks 1.1.2.1

Open an Existing Workbook

1.1.2.2

Close a Workbook

1.1.2.3

Create a New Workbook

1.1.3 Modifying Worksheets 1.1.3.1

Select a Worksheet

1.1.3.2

Rename a Worksheet

1.1.3.3

Insert a Worksheet

1.1.3.4

Move a Worksheet

1.1.3.5

Delete a Worksheet

1.1.3.6

Copy a Worksheet

1.1.4 Create a workbook based on a template. 1.1.4.1

Create a workbook based on an existing template

1.1.4.2

Create your own template

1.1.5 Change the appearance of a data 1.1.5.1 Apply a number format 1.1.5.2 Change the cell Alignment 1.1.5.3 Change the Font and Font Color 1.1.5.4 Apply Cell Border 1.2. Working with Data and Excel Tables 1.2.1 Use Excel's Find and Replace feature to quickly find specific text and replace and use Excel's Go To Special feature. 1.2.1.1 Find specific text

Page | 1

1.2.1.2 Find specific text and replace it 1.2.1.3 Go To Special feature 1.2.2 Use data validation in Excel to make ensure entering certain values into a cell. 1.2.2.1 Define Data Validation 1.2.2.2 Create Data Validation Rule 1.2.2.3 Use Input Message 1.2.2.4 Show Error Alert 1.2.2.5 Remove Data Validation 1.3. Maintain the output file 1.3.1 Print a worksheet and change some important print settings in Excel. 1.3.1.1

Print a Worksheet

1.3.1.2

Print the current selection

1.3.1.3

Print multiple copies

1.3.1.4

Change the Orientation

1.3.1.5

Change the Page Margins

1.3.2 Share Excel data with Word documents and other files. 1.3.2.1 Paste static Excel data in a Word document. 1.3.2.2 Link the source data in Excel with the destination data in Word 1.3.3 Protect the Excel file 1.3.3.1 Encrypt an Excel file with a password 1.3.3.2 Protect Workbook 1.3.3.3 Protect Sheet

Page | 2

2. Employ Excel functions and the set of preset formulas to perform various operations on the data. 2.1. Perform Math functions to reference mathematical calculations 2.1.1 Use the SUM function to sum a range of cells,. 2.1.2 Use the SUMIF function to sum cells based on one criteria. 2.1.3 Use the SUMIFS function to sum cells based on multiple criteria. 2.1.4 Use the Round function to round a number to a specific number of decimal places. 2.1.5 Use ROUNDDOWN function to rounds a number down. 2.1.6 Utilize the Array formula 2.2. Use the Statistical functions to facilitate the calculation of statistical studies and models. 2.2.1 Use the Count function to count the number of cells that contain number 2.2.2 Use the Countif function to count cells based on one criteria. 2.2.3 Use the Countifs function to count cells based on multiple criteria 2.2.4 Use the AVERAGE function to calculate the average of a range of cells. 2.2.5 Use the AVERAGEIF function to calculate the average of a range of cells based on one criteria, 2.2.6 Use the MEDIAN function to find the median (or middle number). 2.2.7 Use the MODE function to find the most frequently occurring number. 2.2.8 Use the MIN function to find the minimum value. 2.2.9 Use the MAX function to find the maximum value. 2.2.10 Use the LARGE function to find the X largest number. 2.2.11 Use the SMALL function to find the X smallest number. 2.3. Use the Logical Excel functions to establish conditions for calculations.

Page | 3

2.3.1 Use the IF function to checks whether a condition is met, and returns one value if TRUE and another value if FALSE. 2.3.2 Use the AND Function to returns TRUE if all conditions are true and returns FALSE if any of the conditions are false. 2.3.3 Use the OR function to returns TRUE if any of the conditions are TRUE and returns FALSE if all conditions are false. 2.4. Use the Date and time functions for managing and calculating dates in Excel spreadsheets. 2.4.1 Enter a date in Excel. 2.4.2 Use the YEAR function to get the year of a date. 2.4.3 Add a number of days to a date. 2.4.4 Get the current date and time by use the NOW function. 2.4.5 Use the HOUR, Minute or Second function to return the hours, minutes or seconds of a specific time 2.4.6 Add a number of hours, minutes and/or seconds, by using the TIME function. 2.5. Use the Text functions to manipulate, convert and calculate strings. 2.5.1 Join strings by using the & operator. 2.5.2 Use the LEFT function to extract the leftmost characters from a string. 2.5.3 Use the RIGHT function to extract the rightmost characters from a string. 2.5.4 Extract a substring, starting in the middle of a string by using the MID function. 2.5.5 Get the length of a string by using the LEN function 2.5.6 Find the position of a substring in a string by using the FIND function. 2.5.7 Replace existing text with new text in a string by using the SUBSTITUTE function. 2.6. Demonstrate understanding to the difference between relative,

Page | 4

absolute and mixed reference. 2.6.1 Define and uses relative reference. 2.6.2 Define and create absolute reference. 2.6.3 Use the mixed reference to make combination of relative and absolute reference 2.7. Manipulate and find information within Excel spreadsheets by using Lookup & Reference function 2.7.1 Use the VLOOKUP (Vertical lookup) function to looks for a value in a column of a table, and then returns a value in the same row from another column you specify. 2.7.2 Use the HLOOKUP (Horizontal lookup) function to looks for a value in a row of a table, and then returns a value in the same column from another row you specify. 2.7.3 Use the MATCH function to return the position of a value in a given range. 2.7.4 Use the INDEX function to return a specific value in a twodimensional or one-dimensional range. 2.7.5 Use the CHOOSE function to return a value from a list of values, based on a position number. 2.8. Recognize and deal with some common formula errors in Excel. 2.8.1 ##### error 2.8.2 #NAME? error 2.8.3 #VALUE! error 2.8.4 #DIV/0! error 2.8.5 #REF! error

Page | 5

3. Utilize the Excel data analysis tools to perform processes of inspecting, cleaning, transforming, and modeling data. 3.1. Reordering and filtering data in the Excel sheet 3.1.1 Execute sorting on one column. 3.1.2 Execute sorting on multiple columns. 3.1.3 Use the Filter tool to display records that meet certain criteria. 3.2. Highlight cells with a certain color, depending on the cell's value by using the Conditional Formatting tool 3.2.1 Highlight Cells Rules 3.2.2 Clear Rules 3.2.3 Use the Top/Bottom Rules 3.3. Represent the data graphically 3.3.1 Create a Chart 3.3.2 Change Chart Type 3.3.3 Switch Row/Column 3.3.4 Add a chart title 3.3.5 Change the Legend Position 3.3.6 Use data labels to focus your readers' attention on a single data series or data point. 3.4. Use the pivot table to extract the significance from a large, detailed data set. 3.4.1 Insert a Pivot Table 3.4.2 Drag fields 3.4.3 Sort the pivot table. 3.4.4 Filter the data 3.4.5 Change Summary Calculation 3.4.6 Create Two-dimensional Pivot Table 3.5. Utilize Tables to analyze your data in Excel quickly and easily 3.5.1 Insert a Table 3.5.2 Sort a Table

Page | 6

3.5.3 Filter a Table 3.5.4 Display a total row 3.6. Use the What-If Analysis in Excel to try out different values (scenarios) for formulas. 3.6.1 Create Different Scenarios 3.6.2 Compare the results of these scenarios 3.6.3 Perform Goal Seek 4. Make an integration of the Microsoft's event-driven programming language Visual Basic with Microsoft Excel to build customized solutions and programs to enhance the capabilities of Excel. 4.1. Create macros. 4.1.1 Turn on the Developer tab 4.1.2 Place a command button 4.1.3 Assign a Macro 4.1.4 Open the Visual Basic Editor 4.1.5 Create MsgBox 4.1.6 Workbook and Worksheet Object 4.1.7 Record Macro 4.2. Use the Range object & variables 4.2.1 Assign value to a cell 4.2.2 Assign value to a range of cells 4.2.3 Use the Cell to assign value 4.2.4 Declare a Range Object 4.2.5 Use the Select method to selects a range 4.2.6 Use the Rows and Column property to access to a specific row or column of a range. 4.2.7 Use the Copy and Paste method 4.2.8 Use the ClearContents method. 4.2.9 Use the Count property

Page | 7

4.2.10 Declare, initialize and display a variable in Excel VBA 4.3. Use the If Then Statement & Loops to execute code lines if a specific condition is met and loop through a range of cells 4.3.1 Use the If Then statement in Excel VBA to execute code lines if a specific condition is met 4.3.2 Use a single For - Nest loop to loop through a one-dimensional range of cells. 4.3.3 Use a double For- Nest loop to loop through a two-dimensional range of cells. 4.3.4 Use a triple loop to loop through two-dimensional ranges on multiple Excel worksheets. 4.3.5 Use the Do While Loop 4.4. Manipulate strings and Date & Time in Excel VBA. 4.4.1 Join Strings 4.4.2 Extract the leftmost and rightmost characters from a string by using Left and Right commands 4.4.3 Use the Mid, Len, Instr 4.4.4 Gets the year of a date. 4.4.5 Add a number of days to a date 4.4.6 Get the current date and time 4.4.7 Get the Hour, Minute, Second of a time 4.4.8 Use the TimeValue function to converts a string to a time serial number. 4.4.9 Compare Dates and Times 4.5. Work with Events and Array 4.5.1 Define and create Workbook Open Event 4.5.2 Define and create Workbook Change Event 4.5.3 Create a one-dimensional array 4.5.4 Create Two-dimensional Array 4.6. Create Functions ,Sub and Application object

Page | 8

4.6.1 Differentiate between a function and a sub 4.6.2 Create Function 4.6.3 Create Sub 4.6.4 Define the use of the Application object 4.6.5 Use the WorksheetFunction property in to access Excel functions. 4.6.6 Disable screen updating 4.6.7 Display alerts while executing code. 4.6.8 Turn On and Off the automatic calculation option 4.7. Create and use Active X control and UserForm 4.7.1 Create ActiveX controls 4.7.2 Create an Excel VBA Userform. 4.7.3 Add the Controls to the Userform 4.7.4 Show the Userform 4.7.5 Assign the Macros 4.7.6 Test the Userform

Page | 9

1. Microsoft Excel Fundamentals

1.1.

Setting up workbook

1.2.

Working with Data and Excel Tables

1.3.

Maintain the output file

Page | 10

1.1

Setting up Workbook

1.1.1 Modifying the Display of the Ribbon 1.1.2 Create and modify workbooks 1.1.3 Modifying Worksheets 1.1.4 Create a workbook based on a template. 1.1.5 Change the appearance of a data

Page | 11

1.1.1 Modifying the Display of the Ribbon Excel selects the ribbon's Home tab when you open it. Learn how to minimize and customize the ribbon.

Tabs The tabs on the ribbon are: File, Home, Insert, Page layout, Formulas, Data, Review and View. The Home tab contains the most frequently used commands in Excel.

Note: the File tab in Excel 2010 replaces the Office Button in Excel 2007.

1.1.1.1

Minimize the Ribbon

The Ribbon, which is part of the Microsoft Office Fluent user interface, is designed to help you quickly find the commands that you need to complete a task. Commands are organized in logical groups that are collected together under tabs. Each tab relates to a type of activity, such as writing or laying out a page. To reduce screen clutter, some tabs are shown only when they are needed. When the Ribbon is minimized, you see only the tabs You can minimize the ribbon to get extra space on the screen. Right click anywhere on the ribbon, and then click Minimize the Ribbon (or press CTRL + F1).

Page | 12

Result.

1.1.1.2

Customize the Ribbon

You can customize the Ribbon by creating your own tabs with whichever commands you want. Commands are always housed within a group, and you can create as many groups as you want in order to keep your tab organized. If you want, you can even add commands to any of the default tabs, as long as you create a custom group in the tab. Excel 2010 makes it possible to easily create your own tab and add commands to it. 1. Right click anywhere on the ribbon, and then click Customize the Ribbon.

2. Click New Tab. Page | 13

3. Add the commands you like.

4. Rename the tab and group. Page | 14

Note: you can also add new groups to existing tabs. To hide a tab, uncheck the corresponding check box. Click Reset, Reset all customizations, to delete all ribbon and Quick Access Toolbar customizations. Result.

Page | 15

1.1.2 Create and modify workbooks

Excel workbooks can have several pages or worksheets. Each page is identified using a worksheet tab. This lesson will review how to add and remove worksheets, how to rename worksheets and how to change the color of the tab fill.

Workbook vs. Worksheet Just like in Microsoft Word or Microsoft PowerPoint, you can have multiple pages in Microsoft Excel. Sometimes the two terms, workbook and worksheet, can be confusing. A workbook is the entire Excel file. Some refer to their Excel file as a spreadsheet, but technically, a spreadsheet is one page in a workbook. And, did you know you can have multiple spreadsheets within a workbook? A worksheet is a single page or sheet within the workbook. A workbook is another word for your Excel file. Excel automatically creates a blank workbook when you open it.

1.1.2.1

Open an Existing Workbook

To open a workbook you've created in the past, execute the following steps. 1. Click on the green File tab.

Page | 16

What you see next is called the backstage view and it contains all the workbook related commands. 2. Recent shows you a list of your recently used workbooks. You can quickly open a workbook from here. 3. Click Open to open a workbook that is not on the list.

1.1.2.2

Close a Workbook

If you are new to Excel, it's good to know the difference between closing a workbook and closing Excel. This can be confusing in the beginning. 1. To close an Excel workbook, click the lower X.

2. If you have multiple workbooks open, clicking the upper right X closes the active workbook. If you have one workbook open, clicking the upper right X closes Excel.

Page | 17

1.1.2.3

Create a New Workbook

Although Excel creates a blank workbook when you open it, sometimes you want to start all over again. 1. To create a new workbook, click New and then click Create.

Page | 18

1.1.3 Modifying Worksheets Excel Worksheets The worksheet is also referred to as the spreadsheet. Think of a workbook as a regular book, in that one book is the entire story, but each page contains a part of the story. Another example would be your personal budget. The entire workbook contains your budget, but you might have one page or worksheet for each month of the year.The worksheet tabs appear at the bottom of the page. Clicking on the tab takes you directly to that page of your workbook. You can add more worksheets, delete them, change the worksheet title and add a color fill to the tab. A worksheet is a collection of cells where you keep and manipulate the data. By default, each Excel workbook contains three worksheets.

1.1.3.1

Select a Worksheet

When you open Excel, Excel automatically selects Sheet1 for you. The name of the worksheet appears on its sheet tab at the bottom of the document window.

To select one of the other two worksheets, simply click on the sheet tab of Sheet2 or Sheet3. Page | 19

1.1.3.2

Rename a Worksheet

By default, the worksheets are named Sheet1, Sheet2 and Sheet3. To give a worksheet a more specific name, execute the following steps. 1. Right click on the sheet tab of Sheet1. 2. Choose Rename.

3. For example, type Sales 2010.

1.1.3.3

Insert a Worksheet

You can insert as many worksheets as you want. To quickly insert a new worksheet, click the Insert Worksheet tab at the bottom of the document window.

Result:

Page | 20

1.1.3.4

Move a Worksheet

To move a worksheet, click on the sheet tab of the worksheet you want to move and drag it into the new position. 1. For example, click on the sheet tab of Sheet4 and drag it before Sheet2.

Result:

1.1.3.5

Delete a Worksheet

To delete a worksheet, right click on a sheet tab and choose Delete. For example, delete Sheet4, Sheet2 and Sheet3.

Result:

Page | 21

1.1.3.6

Copy a Worksheet

Imagine, you have got the sales for 2010 ready and want to create the exact same sheet for 2011, but with different data. You can recreate the worksheet, but this is time-consuming. It's a lot easier to copy the entire worksheet and only change the numbers. 1. Right click on the sheet tab of Sales 2010. 2. Choose Move or Copy...

The 'Move or Copy' dialog box appears. 3. Select (move to end) and check Create a copy.

4. Click OK. Page | 22

Result:

Note: you can even copy a worksheet to another Excel workbook by selecting the specific workbook from the drop-down list (see the dialog box shown earlier).

1.1.4 Create a workbook based on a template. Instead of creating an Excel workbook from scratch, you can create a workbook based on a template. There are many free templates available, waiting to be used

1.1.4.1

Create a workbook based on an existing template

To create a workbook based on an existing template, execute the following steps. 1. On the green File tab, click New. 2. To choose a template from one of the sample templates (these are already installed on your computer), click on Sample templates.

Page | 23

3. To choose a template from the Office.com Templates, click a category. For example, click Calendars.

4. To download a template, select a template and then click Download.

Page | 24

Excel creates a workbook (UniversalCalendar1.xlsx) based on this template. Excel also stores the template (UniversalCalendar.xltx) in the Templates folder. You can access this folder by clicking on My templates (see first picture). Read on for more information about the Templates folder. Tips: To obtain more workbook templates, you can download them from Microsoft Office Online. In the New Workbook dialog box, under Microsoft Office Online, click a specific template category, click the template that you want to download, and then click Download.

1.1.4.2

Create your own template

If you create your own template, you can safely store it in the Templates folder. As a result, you can create new workbooks based on this template without worrying that you overwrite the original file. To create a template, execute the following steps. 1. Create a workbook. 2. On the green File tab, click Save As. 3. Enter a file name. 4. Select Excel Template (*.xltx) from the drop-down list.

Page | 25

Excel automatically activates the Templates folder. Notice the location of the Templates folder on your computer. It's usually located here: C:\Users\\AppData\Roaming\Microsoft\Templates 5. Click Save.

To create a workbook based on this template, execute the following steps. 1. On the green File tab, click New. 2. Click My Templates.

Page | 26

3. Select WeddingBudget. 4. Click OK.

Excel creates a workbook (WeddingBudget1.xlsx) based on this template. Note: to edit a template, on the green File tab, click Open to open the template. Edit the file and save the file to its original location.

1.1.5 Change the appearance of a data

1.1.5.1

Apply a number format

Excel provides many options for displaying numbers as percentages, currency, dates, and so on. If these built-in formats do not meet your needs, you can customize a built-in number format to create your own. When we format cells in Excel, we change the appearance of a number without changing the number itself. We can apply a number format (0.8, $0.80, 80%, etc) or other formatting (alignment, font, border, etc). 1. Enter the value 0.8 into cell B2.

Page | 27

By default, Excel uses the General format (no specific number format) for numbers. To apply a number format, use the 'Format Cells' dialog box. 2. Select cell B2. 3. Right click, and then click Format Cells (or press CTRL + 1).

Page | 28

The 'Format Cells' dialog box appears. 4. For example, on the Number tab, select Currency.

Note: Excel gives you a life preview of how the number will be formatted (under Sample). 5. Click OK.

Page | 29

Cell B2 still contains the number 0.8. We only changed the appearance of this number, not the number itself. The most frequently used formatting commands are available on the Home tab. 6. On the Home tab, in the Number group, click the Percentage symbol to apply a Percentage format.

Navigate the different number formats

1.1.5.2

Change the cell Alignment

For the optimal display of the data on your worksheet, you might want to reposition the data in a cell. You can change the alignment of the cell contents, use indentation for better spacing, or display the data at a different angle by rotating it On the Home tab, in the Alignment group, center the number.

Navigate the other alignment options Page | 30

1.1.5.3

Change the Font and Font Color

You can change the color of the text in cells and the cell's background color. For the background color, you can use a solid color, or you can apply special effects, such as gradients, textures, and pictures. On the Home tab, in the Font group, change the Font color.

Try changing the background color

1.1.5.4

Apply Cell Border

By using predefined border styles, you can quickly add a border around cells or ranges (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells. If predefined cell borders do not meet your needs, you can create a custom border. Note

Cell borders that you apply appear on printed pages. If you do not use cell borders

but want worksheet gridline borders for all cells to be visible on printed pages, you can display the gridlines.

Page | 31

1. On the Home tab, in the Font group, add borders.

Page | 32

1.2

1.2.1

Working with Data and Excel Tables

Use Excel's Find and Replace feature to quickly find specific text and replace and use

Excel's Go To Special feature. 1.2.2

Use data validation in Excel to make ensure entering certain values into a cell.

Page | 33

1.2.1 Use Excel's Find and Replace feature to quickly find specific text and replace and use Excel's Go To Special feature. You can use Excel's Find and Replace feature to quickly find specific text and replace it with other text. You can use Excel's Go To Special feature to quickly select all cells with formulas, comments, conditional formatting, constants, data validation, etc

1.2.1.1

Find specific text

To quickly find specific text, execute the following steps. 1. On the Home tab, click Find & Select, Find...

The 'Find and Replace' dialog box appears. 2. Type the text you want to find. For example, type Ferrari. 3. Click 'Find Next'.

Page | 34

Excel selects the first occurrence.

4. Click 'Find Next' to select the second occurrence.

Page | 35

5. To get a list of all the occurrences, click 'Find All'.

Page | 36

1.2.1.2 Find specific text and replace it To quickly find specific text and replace it with other text, execute the following steps. 1. On the Home tab, click Find & Select, Replace...

The 'Find and Replace' dialog box appears (with the Replace tab selected). 2. Type the text you want to find (Veneno) and replace it with (Diablo). 3. Click 'Find Next'.

Excel selects the first occurrence. No replacement has been made yet. Page | 37

4. Click 'Replace' to make a single replacement.

Note: use 'Replace All' to replace all occurrences.

Page | 38

1.2.1.3 Go To Special feature Go To Special is a tool within Microsoft Excel that enables you to quickly select cells of a specified type within your Excel worksheet. Once you get to grips with this function and what it can be used for you will wonder how you ever lived without it. You can use Excel's Go To Special feature to quickly select all cells with formulas, comments, conditional formatting, constants, data validation, etc. For example, to select all cells with formulas, execute the following steps. 1. Select a single cell. 2. On the Home tab, click Find & Select, Go To Special...

Note: Formulas, Comments, Conditional formatting, Constants and Data Validation are shortcuts. They can also be found under Go To Special. 3. Select Formulas and click OK.

Page | 39

Note: you can search for cells with formulas that return Numbers, Text, Logicals (TRUE and FALSE) and Errors. These check boxes are also available if you select Constants. Excel selects all cells with formulas.

General note: if you select a single cell before you click Find, Replace or Go To Special, Excel searches the entire worksheet. To search a range of cells, first select a range of cells.

Page | 40

1.2.2 Use data validation in Excel to make ensure entering certain values into a cell. 1.2.2.1 Define Data Validation You use data validation to control the type of data or the values that users enter into a cell. For example, you may want to restrict data entry to a certain range of dates, limit choices by using a list, or make sure that only positive whole numbers are entered. Use data validation in Excel to make sure that users enter certain values into a cell. In this example, we restrict users to enter a whole number between 0 and 10.

1.2.2.2 Create Data Validation Rule To create the data validation rule, execute the following steps. 1. Select cell C2. 2. On the Data tab, click Data Validation.

On the Settings tab: 3. In the Allow list, click Whole number. 4. In the Data list, click between. 5. Enter the Minimum and Maximum values. Page | 41

1.2.2.3 Use Input Message To help people know what data should be entered in a cell, you can set up an Input Message that appears when the user selects the cell and tell the user what to enter. On the Input Message tab: 1. Check 'Show input message when cell is selected'. 2. Enter a title. 3. Enter an input message.

Page | 42

1.2.2.4

Show Error Alert

When you add data validation to a cell, the Error Alert feature is automatically turned on. It blocks the users from entering invalid data in the cell. If users ignore the input message and enter a number that is not valid, you can show them an error alert. On the Error Alert tab: 1. Check 'Show error alert after invalid data is entered'. 2. Enter a title. 3. Enter an error message.

5. Click OK.

Page | 43

1.2.2.5

Remove Data Validation.

You can remove validation on a cell in Excel so that users are no longer required to enter information or information formatted in specific way 1. Select cell C2.

2. Try to enter a number higher than 10. Result:

Note: to remove data validation from a cell, select the cell, on the Data tab, click Data Validation, and then click Clear All. You can use Excel's Go To Special feature to quickly select all cells with data validation.

Page | 44

1.3

Maintain the output file

1.3.1 Print a worksheet and change some important print settings in Excel. 1.3.2 Share Excel data with Word documents and other files. 1.3.2 Protect the Excel file

Page | 45

1.3.1 Print a worksheet and change some important print settings in Excel. You can print entire or partial worksheets and workbooks, one at a time, or several at once. And if the data that you want to print is in a Microsoft Excel table, you can print just the Excel table. You can also print a workbook to a file instead of to a printer. This is useful when you need to print the workbook on a different type of printer from the one that you originally used to print it. This chapter teaches you how to print a worksheet and how to change some important print settings in Excel.

1.3.1.1

Print a Worksheet

Excel prints all the information in active worksheets in your workbook. Normally, this means printing just the data in the current worksheet. To print additional worksheets in the workbook, hold down the Ctrl key while you click the sheets' tabs. To print a worksheet in Excel 2010, execute the following steps. 1. On the File tab, click Print. 2. To preview the other pages that will be printed, click 'Next Page' or 'Previous Page' at the bottom of the window.

Page | 46

3. To print the worksheet, click the big Print button.

1.3.1.2

Print the current selection

Select this option to have Excel print just the cells that are currently selected in your workbook. (Remember to select these cells before opening the Print panel and choosing this printing option.)

Page | 47

Depending on what is selected in the worksheet when you display the Print panel, you may see additional options in this menu, including Print Selected Table or Print Selected Chart. Choose the desired option based on what you want to print. Instead of printing the entire worksheet, you can also only print the current selection. 1. First, select the range of cells you want to print. 2. Next, under Settings, select Print S election.

3. To print the selection, click the big Print button.

Page | 48

Note: you can also print the active sheets (first select the sheets by holding down CTRL and clicking the sheet tabs) or print the entire workbook. Use the boxes next to Pages (see first screenshot) to only print a few pages of your document. For example, 2 to 2 only prints the second page.

1.3.1.3

Print multiple copies

To print multiple copies, execute the following steps. 1. Use the arrows next to the Copies box. 2. If one copy contains multiple pages, you can switch between Collated and Uncollated. For example, if you print 6 copies, Collated prints the entire first copy, then the entire second copy, etc. Uncollated prints 6 copies of page 1, 6 copies of page 2, etc.

Page | 49

1.3.1.4

Change the Orientation

By default, Excel prints worksheets in portrait orientation (taller than wide). You can change the page orientation to landscape (wider than tall) on a worksheet-by-worksheet basis. If you always want to print worksheets in landscape orientation, you can create a template, change the page orientation from portrait to landscape, and then use that template as the basis for all future workbooks. You can switch between Portrait Orientation (more rows but fewer columns) and Landscape Orientation (more columns but fewer rows).

Page | 50

1.3.1.5

Change the Page Margins and scaling

To better align a Microsoft Excel worksheet on a printed page, you can change margins, specify custom margins, or center the worksheet horizontally or vertically on the page. Page margins are the blank spaces between the worksheet data and the edges of the printed page. Top and bottom page margins can be used for some items, such as headers, footers, and page numbers. To adjust the page margins, execute the following steps. 1. Select one of the predefined margins (Normal, Wide or Narrow) from the Margins dropdown list. 2. Or click the 'Show Margins' icon at the bottom right of the window. Now you can drag the lines to manually change the page margins.

Page | 51

If you want to fit more data on one page, you can fit the sheet on one page. To achieve this, execute the following steps. 1. Select 'Fit Sheet on One Page' from the Scaling drop-down list.

Note: you can also shrink the printout to one page wide or one page high. Click Custom Scaling Options to manually enter a scaling percentage or to fit the printout to a specific number of pages wide and tall. Be careful, Excel doesn't warn you when your printout becomes unreadable. Page | 52

1.3.2 Share Excel data with Word documents and other files. Learn how to share Excel data with Word documents and other files.

1.3.2.1

Paste static Excel data in a Word document.

There may be times when you want to present your Microsoft Office Excel worksheet data, or a chart that you created in Excel, in a Microsoft Office Word document. You can simply copy selected data or charts in Excel and then use the Paste or Paste Special commands in Word to insert the data or charts into a document. Most of the time, you'll simply need to paste static Excel data in a Word document. 1. Select the Excel data.

2. Right click, and then click Copy (or press CTRL + c). 3. Open a Word document. 4. On the Home tab, click Paste Special...

Page | 53

5. Click Paste, HTML Format. 6. Click OK.

Note: instead of executing steps 4 to 6, simply press CTRL + v. 7. Click the icon in the upper left corner of the table and add borders. Result.

Note: To delete the table, right click the icon in the upper left corner, and then click Delete Table.

Page | 54

1.3.2.2

Link the source data in Excel with the

destination data in Word You can also link the source data in Excel with the destination data in Word. If you change the data in Excel, the data in Word is updated automatically. 1. Repeat steps 1 to 4 above. 2. Click Paste link, HTML Format. 3. Click OK.

4. Click the icon in the upper left corner of the table and add borders. Result.

Page | 55

5. Change the Excel data.

Result.

Note: In Word, on the File tab, click Info, and then click Edit Links to files (in the lower right corner) to launch the Links dialog box. Here, you can break a link, change the location of the Excel file, etc. Page | 56

1.3.3 Protect the Excel file 1.3.3.1

Encrypt an Excel file with a password

In Microsoft Office system, you can use passwords to help prevent other people from opening or modifying your documents, workbooks, and presentations. Keep in mind that Microsoft cannot retrieve forgotten passwords. Encrypt an Excel file with a password so that it requires a password to open it. 1. Open a workbook. 2. On the green File tab, click Save As.

3. Click on the Tools button and click General Options.

Page | 57

4. In the Password to open box, enter a password and click OK.

5. Reenter the password and click OK.

Note: this feature also encrypts your Excel file. If you lose or forget the password, it cannot be recovered. 6. Enter a file name and click Save.

It requires a password to open this Excel file now. The password for the downloadable Excel file is "easy". Page | 58

1.3.3.2

Protect Workbook

Excel gives you several ways to protect a workbook. You can require a password to open it, a password to change data, and a password for changing the file's structure—adding, deleting, or hiding worksheets. Remember, though, that this type of protection doesn't encrypt your files. Users can still use third-party tools to read your data. This example teaches you how to protect the workbook structure and windows in Excel. If you protect the workbook structure, users cannot insert, delete, rename, move, copy, hide or unhide worksheets anymore. 1. Open a workbook. 2. On the Review tab, click Protect Workbook.

3. Check Structure, enter a password and click OK.

4. Reenter the password and click on OK.

Page | 59

Users cannot insert, delete, rename, move, copy, hide or unhide worksheets anymore.

If you protect the workbook windows, users cannot move, change the size and close windows anymore. 1. Open a workbook. 2. On the Review tab, click Protect Workbook.

3. Check Windows, enter a password and click OK.

Page | 60

4. Reenter the password and click on OK.

Users cannot move, change the size and close windows anymore.

Page | 61

To unprotect the workbook, click Protect Workbook and enter the password. The password for the downloadable Excel file is "easy". The structure and window of this workbook are protected.

1.3.3.3

Protect Sheet

When you share a file with other users, you may want to protect a worksheet to help prevent it from being changed. 1. Right click a worksheet tab. 2. Click Protect Sheet.

3. Enter a password. 4. Check the actions you allow the users of your worksheet to perform. 5. Click OK.

Page | 62

Note: if you don't check any action, users can only view the Excel file! 6. Confirm the password and click OK.

Your worksheet is protected now. To unprotect a worksheet, right click on the worksheet tab and click Unprotect Sheet. The password for the downloadable Excel file is "easy".

Page | 63

2. Employ Excel functions and the set of preset formulas to perform various operations on the data.

2.1 Perform Math functions to reference mathematical calculations 2.2 Use the Statistical functions to facilitate the calculation of statistical studies and models. 2.3 Use the Logical Excel functions to establish conditions for calculations. 2.4 Use the Date and time functions for managing and calculating dates in Excel spreadsheets. 2.5 Use the Text functions to manipulate, convert and calculate strings. 2.6 Demonstrate understanding to the difference between relative, absolute and mixed reference. 2.7 Manipulate and find information within Excel spreadsheets by using Lookup & Reference function 2.8 Recognize and deal with some common formula errors in Excel.

Page | 64

2.1

Perform Math functions to reference mathematical calculations

2.1.1 Use the SUM function to sum a range of cells,. 2.1.2 Use the SUMIF function to sum cells based on one criteria. 2.1.3 Use the SUMIFS function to sum cells based on multiple criteria. 2.1.4 Use the Round function to round a number to a specific number of decimal places. 2.1.5 Use ROUNDDOWN function to rounds a number down. 2.1.6 Utilize the Array formula

Page | 65

2.1.1 Use the SUM function to sum a range of cells,. To sum a range of cells, use the SUM function.

Excel Sum Function Examples The following spreadsheets show the Excel Sum function used to calculate the sum of the numbers 5, 6, 7, 8 & 9. In each of the five examples, the numbers are supplied to the Sum function in a different way. Formulas:

A

Results:

B

A

B

1

5 =SUM( 5, 6, 7, 8, 9 )

1

5

35

2

6 =SUM( {5,6,7}, 8, 9 )

2

6

35

3

7 =SUM( A1, A2, A3, A4, A5 )

3

7

35

4

8 =SUM( A1, A2, A3, "8", "9" )

4

8

35

5

9 =SUM( A1:A5 )

5

9

35

2.1.2 Use the SUMIF function to sum cells based on one criteria. You use the SUMIF function to sum the values in a range that meet criteria that you specify. To sum cells based on one criteria (for example, higher than 9), use the following SUMIF function (two arguments). Page | 66

To sum cells based on one criteria (for example, green), use the following SUMIF function (three arguments, last argument is the range to sum).

Excel Sumif Function Examples Example 1 The spreadsheet below shows three examples of the Excel Sumif function used with text based criteria. For each call to the Excel Sumif function the range argument (to be tested against the criteria) is either the cell range A2 - A9 or the cell range B2 - B9, and the [sum_range] argument (containing the values to be summed) is the cell range C2 - C9. Formulas:

1 2 3 4

A Month Jan Jan Jan

B Team North 1 North 2 South 1

Results:

C Sales $36,693 $22,100 $53,321

1 2 3 4

A Month Jan Jan Jan

B Team North 1 North 2 South 1

C Sales $36,693 $22,100 $53,321 Page | 67

5 Jan South 2 $34,440 6 Feb North 1 $29,889 7 Feb North 2 $50,090 8 Feb South 1 $32,080 9 Feb South 2 $45,500 1 0 1 1 =SUMIF( A2:A9, "Feb", C2:C9 ) 1 =SUMIF( B2:B9, "North 1", C2:C9 2 ) 1 3 =SUMIF( B2:B9, "North*", C2:C9 )

5 6 7 8 9 1 0 1 1 1 2 1 3

Jan Feb Feb Feb Feb

South 2 North 1 North 2 South 1 South 2

$34,440 $29,889 $50,090 $32,080 $45,500

$157,559

- sum of cells C6-C9

$66,582

- sum of cells C2 & C6

$138,772 - sum of cells C2, C3, C6 & C7

Note that, in the example above: The function in cell A13 uses the wildcard * and so finds cells in the range B2-B9 that begin with the text string "North". This is satisfied by the values "North 1" and "North 2". In all three examples, the text based criteria (including the wildcard) are encased in quotes.

Example 2 The following example shows the Excel Sumif function using criteria based on numeric values. Formulas:

A 1 2 1 2 1

B 200 45 550 450 20

Results:

C

1 2 3 4 5 6 7 =SUMIF( A1:A5, 1, B1:B5 ) 8 =SUMIF(B1:B5, ">100" )

1 2 3 4 5 6 7 8

A 1 2 1 2 1

B 200 45 550 450 20

C

770 - sum of cells B1, B3 & B5 1200 - sum of cells B1, B3 & B4

Note that, in the above spreadsheet: The criteria in cell A8 is an expression, and so, is enclosed in quotes. The [sum_array] argument is omitted from the function in cell A8 and so the range array (i.e. cells B2-B6) is used as the [sum_range]. Page | 68

2.1.3 Use the SUMIFS function to sum cells based on multiple criteria. The Microsoft Excel SUMIFS function adds all numbers in a range of cells, based on a single or multiple criteria. To sum cells based on multiple criteria (for example, blue and green), use the following SUMIFS function (first argument is the range to sum).

General note: in a similar way, you can use the AVERAGEIF and AVERAGEIFS function to average cells based on one or multiple criteria.

Excel Sumifs Function Examples The spreadsheet below shows the quarterly sales figures for 3 sales representatives. The Sumifs function can be used to find total sales figures for any combination of quarter, area and sales rep. This is shown in the examples below. A 1 Quarter 2 1 3 1

B Area North North

C Sales Rep. Jeff Chris

D Sales $223,000 $125,000 Page | 69

4 5 6 7 8 9 10 11 12 13

1 2 2 2 3 3 3 4 4 4

South North North South North North South North North South

Carol Jeff Chris Carol Jeff Chris Carol Jeff Chris Carol

$456,000 $322,000 $340,000 $198,000 $310,000 $250,000 $460,000 $261,000 $389,000 $305,000

Example 1 To find the sum of sales in the North area during quarter 1: =SUMIFS( D2:D13, A2:A13, 1, B2:B13, "North" )

which gives the result $348,000. In this example, the Excel Sumifs function identifies rows where: The value in column A is equal to 1 and The entry in column B is equal to "North" and calculates the sum of the corresponding values from column D. I.e. this formula finds the sum of the values $223,000 and $125,000 (from cells D2 and D3).

2.1.4 Use the Round function to round a number to a specific number of decimal places. This chapter illustrates three functions to round numbers in Excel. The ROUND, ROUNDUP and ROUNDDOWN function. Before your start: if you round a number, you lose precision. If you don't want this, show fewer decimal places without changing the number itself.

Page | 70

Round The ROUND function rounds a number to a specified number of digits.

1. Round a number to two decimal places.

Note: 1, 2, 3, and 4 get rounded down. 5, 6, 7, 8, and 9 get rounded up. In this example, 114.7211, 114.7221, 114.7231 and 114.7241 get rounded down to 114.72 and 114.7251, 114.7261, 114.7271, 114.7281 and 114.7291 get rounded up to 114.73. 2. Round a number to one decimal place.

3. Round a number to the nearest integer.

4. Round a number to the nearest 10.

Page | 71

5. Round a number to the nearest 100.

RoundUp The ROUNDUP function always rounds a number up (away from zero). For example, round a number up to one decimal place.

2.1.5 Use ROUNDDOWN function to rounds a number down. The ROUNDDOWN function always rounds a number down (toward zero). For example, round a number down to the nearest integer.

Page | 72

2.1.6 Utilize the Array formula This chapter helps you understand array formulas in Excel. Single cell array formulas perform multiple calculations in one cell.

Without Array Formula Without using an array formula, we would execute the following steps to find the greatest progress. 1. First, we would calculate the progress of each student.

2. Next, we would use the MAX function to find the greatest progress.

Page | 73

With Array Formula We don't need to store the range in column D. Excel can store this range in its memory. A range stored in Excel's memory is called an array constant. 1. We already know that we can find the progress of the first student by using the formula below.

2. To find the greatest progress (don't be overwhelmed), we add the MAX function, replace C2 with C2:C6 and B2 with B2:B6.

Page | 74

3. Finish by pressing CTRL + SHIFT + ENTER.

Note: The formula bar indicates that this is an array formula by enclosing it in curly braces {}. Do not type these yourself. They will disappear when you edit the formula. Explanation: The range (array constant) is stored in Excel's memory, not in an range. The array constant looks as follows: {19;33;63;48;13} This array constant is used as an argument for the MAX function, giving a result of 63.

F9 Key When working with array formulas, you can have a look at these array constants yourself. 1. Select C2:C6-B2:B6 in the formula.

Page | 75

2. Press F9.

That looks good. Elements in a vertical array constant are separated by semicolons. Elements in a horizontal array constant are separated by commas.

Page | 76

2.2

Use the Statistical functions to facilitate the calculation of statistical studies and models.

2.2.1

Use the Count function to count the number of cells that contain number

2.2.2

Use the Countif function to count cells based on one criteria.

2.2.3

Use the Countifs function to count cells based on multiple criteria

2.2.4

Use the AVERAGE function to calculate the average of a range of cells.

2.2.5

Use the AVERAGEIF function to calculate the average of a range of cells based on one criteria,

2.2.6

Use the MEDIAN function to find the median (or middle number).

2.2.7

Use the MODE function to find the most frequently occurring number.

2.2.8

Use the MIN function to find the minimum value.

2.2.9

Use the MAX function to find the maximum value.

2.2.10 Use the LARGE function to find the X largest number. 2.2.11 Use the SMALL function to find the X smallest number.

Page | 77

2.2.1 Use the Count function to count the number of cells that contain number The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers. The most used functions in Excel are the functions that count and sum. You can count and sum based on one criteria or multiple criteria. To count the number of cells that contain numbers, use the COUNT function.

Which Values are Counted as Numeric Values? Numbers and dates are always counted as numeric values by the Excel Count function. However, text representations and logical values are counted differently, depending on whether they are supplied as a value in a range of cells, or if they are supplied directly to the function. The table below summarises which values are and which are not treated as numeric values by the Excel Count function: Value Within a

Value Supplied

Range of Cells

Directly to Function

Numbers

ARE counted

ARE counted

Dates

ARE counted

ARE counted

Page | 78

Logical Values

NOT counted

ARE counted

Text Representations of

NOT counted

ARE counted

Other Text

NOT counted

NOT counted

Errors

NOT counted

NOT counted

Numbers & Dates

Count Function Examples Example 1 - Values Supplied from a Range of Worksheet Cells In the following spreadsheet, the Count function is used to return the number of numeric values in one or more supplied ranges of cells. Formulas:

Results:

A 1 5 2 text 3 FALSE 4 01/01/2015 5 #N/A

B

C 0 =COUNT( A1:A5 ) =COUNT( A1:A5, B1 ) =COUNT( A1:B5 )

10

A 1 5 2 text 3 FALSE 4 01/01/2015 5 #N/A

B

C 0 2 3 4

10

Note that, in the above example: The numbers and the date 01/01/2015 are counted by the function. The text value "text", the logical value FALSE, and the error value #N/A are not counted by the function. The empty cells are not counted by the function. Example 2 - Values Supplied Directly to the Excel Count Function In the following spreadsheet, the Excel Count function is used to count the number of numeric values in sets of values supplied directly to the function.

Page | 79

Formulas:

Results:

A 1 =COUNT( 100, DATE(2015,1,1) ) 2 =COUNT( "100", "01/01/2015", FALSE ) 3 =COUNT( "text", #N/A )

A 1 2 2 3 3 0

Note, in the above example: The number 100 and the date 01/01/2015 are counted by the function. The text representations of the number "100" & the date, "01/01/2015", and the logical value FALSE, are counted by the function. The text string "text" and the error #N/A are not counted by the function.

2.2.2 Use the Countif function to count cells based on one criteria. To count cells based on one criteria (for example, higher than 9), use the following COUNTIF function.

2.2.3 Use the Countifs function to count cells based on multiple criteria To count cells based on multiple criteria (for example, green and higher than 9), use the following COUNTIFS function.

Page | 80

2.2.4 Use the AVERAGE function to calculate the average of a range of cells. Returns the average (arithmetic mean) of the arguments. For example, if the range A1:A20 contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers. To calculate the average of a range of cells, use the AVERAGE function.

2.2.5 Use the AVERAGEIF function to calculate the average of a range of cells based on one criteria, The Excel AVERAGEIF function finds the values in a supplied array that satisfy a specified criteria, and returns the average (ie. the statistical mean) of the corresponding values in a second supplied array.

Page | 81

To average cells based on one criteria, use the AVERAGEIF function. For example, to calculate the average excluding zeros.

Note: means not equal to. The AVERAGEIF function is similar to the SUMIF function.

2.2.6 Use the MEDIAN function to find the median (or middle number). To find the median (or middle number), use the MEDIAN function.

Check:

2.2.7 Use the MODE function to find the most frequently occurring number. The Excel MODE function returns the statistical mode (the most frequently occurring value) of a list of supplied numbers. If there are 2 or more most frequently occurring values in the supplied data, the function returns the lowest of these values. To find the most frequently occurring number, use the MODE function. Page | 82

2.2.8 Use the MIN function to find the minimum value. The Excel MIN function returns the smallest value from a supplied set of numeric values. To find the minimum value, use the MIN function.

2.2.9 Use the MAX function to find the maximum value. The Excel MAX function returns the largest value from a supplied set of numeric values. To find the maximum value, use the MAX function.

2.2.10

Use the LARGE function to find the X largest number.

The Excel LARGE function returns the largest value from an array of numeric values. To find the third largest number, use the following LARGE function.

Page | 83

Check:

2.2.11

Use the SMALL function to find the X smallest number.

The Excel SMALL function returns the smallest value from an array of numeric values. To find the second smallest number, use the following SMALL function.

Check:

Tip: Excel can generate most of these results with the click of a button.

Page | 84

2.3

Use the Logical Excel functions to establish conditions for calculations.

2.3.1

Use the IF function to checks whether a condition is met, and returns one value if TRUE and another value if FALSE.

2.3.2

Use the AND Function to returns TRUE if all conditions are true and returns FALSE if any of the conditions are false.

2.3.3

Use the OR function to returns TRUE if any of the conditions are TRUE and returns FALSE if all conditions are false.

Page | 85

2.3.1 Use the IF function The IF function checks whether a condition is met, and returns one value if TRUE and another value if FALSE. 1. Select cell C2 and enter the following function.

The IF function returns Correct because the value in cell A1 is higher than 10.

2.3.2 Use the AND Function. The AND Function returns TRUE if all conditions are true and returns FALSE if any of the conditions are false. 1. Select cell D2 and enter the following formula.

The AND function returns FALSE because the value in cell B2 is not higher than 5. As a result the IF function returns Incorrect.

2.3.3 Use the OR function. The OR function returns TRUE if any of the conditions are TRUE and returns FALSE if all conditions are false. 1. Select cell E2 and enter the following formula. Page | 86

The OR function returns TRUE because the value in cell A1 is higher than 10. As a result the IF function returns Correct. General note: the AND and OR function can check up to 255 conditions.

Page | 87

2.4

Use the Date and time functions for managing and calculating dates in Excel spreadsheets.

2.4.1

Enter a date in Excel.

2.4.2

Use the YEAR function to get the year of a date.

2.4.3

Add a number of days to a date.

2.4.4

Get the current date and time by use the NOW function.

2.4.5

Use the HOUR, Minute or Second function to return the hours, minutes or seconds of a specific time

2.4.6

Add a number of hours, minutes and/or seconds, by using the TIME function.

Page | 88

2.4.1 Enter a date in Excel. he DATE function returns the sequential serial number that represents a particular date. To enter a date in Excel, use the "/" or "-" characters. To enter a time, use the ":" (colon). You can also enter a date and a time in one cell.

Note: Dates are in US Format. Months first, Days second. This type of format depends on your windows regional settings.

2.4.2 Use the YEAR function to get the year of a date.

Year, Month, Day The Excel Year function returns an integer representing the year of a supplied date.

To get the year of a date, use the YEAR function.

Note: use the MONTH and DAY function to get the month and day of a date.

2.4.3 Add a number of days to a date. 1. To add a number of days to a date, use the following simple formula.

Page | 89

2. To add a number of years, months and/or days, use the DATE function.

Note: the DATE function accepts three arguments: year, month and day. Excel knows that 6 + 2 = 8 = August has 31 days and rolls over to the next month (23 August + 9 days = 1 September).

2.4.4 Get the current date and time by use the NOW function.

Current Date & Time The Excel NOW function returns the current date and time. The function receives no arguments.

To get the current date and time, use the NOW function.

Note: use the TODAY function to get the current date only. Use NOW()-TODAY() to get the current time only.

2.4.5 Use the HOUR, Minute or Second function to return the hours, minutes or seconds of a specific time

Page | 90

Hour, Min, Sec The Excel HOUR function returns an integer representing the hour component of a supplied Excel time.

To return the hour, use the HOUR function.

Note: use the MINUTE and SECOND function to return the minute and second.

2.4.6 Add a number of hours, minutes and/or seconds, by using the TIME function.

Time Function To add a number of hours, minutes and/or seconds, use the TIME function.

Note: Excel adds 2 hours, 10 + 1 = 11 minutes and 70 - 60 = 10 seconds.

Page | 91

2.5

Use the Text functions to manipulate, convert and calculate strings.

2.5.1

Join strings by using the & operator.

2.5.2

Use the LEFT function to extract the leftmost characters from a string.

2.5.3

Use the RIGHT function to extract the rightmost characters from a string.

2.5.4

Extract a substring, starting in the middle of a string by using the MID function.

2.5.5

Get the length of a string by using the LEN function

2.5.6

Find the position of a substring in a string by using the FIND function.

2.5.7

Replace existing text with new text in a string by using the SUBSTITUTE function.

Page | 92

2.5.1 Join strings by using the & operator. In Excel 2010, you can use the ampersand (&) operator to concatenate (or join) separate text strings together.

To join strings, use the & operator.

Note: to insert a space, use " "

2.5.2 Use the LEFT function to extract the leftmost characters from a string. To extract the leftmost characters from a string, use the LEFT function.

Left Function Examples The spreadsheet below shows three examples of the Excel Left function. Formulas:

A 1 Original Text 2 Original Text 3 Original Text

Results:

B =LEFT( A1 ) =LEFT( A2, 4 ) =LEFT( A3, FIND( " ", A3 ) - 1 )

A 1 Original Text 2 Original Text 3 Original Text

B O Orig Original

Page | 93

2.5.3 Use the RIGHT function to extract the rightmost characters from a string. To extract the rightmost characters from a string, use the RIGHT function.

Right Function Examples The spreadsheet below shows three examples of the Excel Right function. Formulas:

Results:

A

B

A

B

1 Original Text

=RIGHT( A1 )

1 Original Text

t

2 Original Text

=RIGHT( A2, 4 )

2 Original Text

Text

3 The Number 5

=RIGHT( A3, 1 )

3 The Number 5

5

2.5.4 Extract a substring, starting in the middle of a string by using the MID function. The Excel Mid function returns a specified number of characters from the middle of a supplied text string. To extract a substring, starting in the middle of a string, use the MID function.

Note: started at position 5 (p) with length 3. Page | 94

Mid Function Examples The spreadsheet below shows three examples of the Excel Mid function. Formulas:

Results:

A

B

A

B

1 Original Text

=MID( A1, 7, 1 )

1 Original Text

a

2 Original Text

=MID( A2, 4, 7 )

2 Original Text

ginal T

3 255 years

=MID( A3, 3, 1 )

3 255 years

5

Note that the example in cell B3 returns the text value "5" (not the numeric value).

2.5.5 Get the length of a string by using the LEN function The Excel LEN function returns the length of a supplied text string. Example:

To get the length of a string, use the LEN function.

Note: space (position 8) included!

Page | 95

2.5.6 Find the position of a substring in a string by using the FIND function. The Excel FIND function returns the position of a specified character or sub-string within a supplied text string. The function is case-sensitive. If you want to perform a non-case-sensitive search, use the Excel Search function instead. To find the position of a substring in a string, use the FIND function.

Note: string "am" found at position 3.

Find Function Examples The following spreadsheet shows examples of the Excel Find function used to find various characters in the text string "Original Text". Formulas:

1 2 3 4

A Original Text Original Text Original Text Original Text

Results:

B =FIND( "T", A1 ) =FIND( "t", A2 ) =FIND( "i", A3 ) =FIND( "i", A4, 4 )

1 2 3 4

A Original Text Original Text Original Text Original Text

B 10 13 3 5

Note that, in the above spreadsheet: Due to the case-sensitivity of the Find function, the upper- and lower-case find_text values, "T" and "t", return different results (see the examples in cells B1 & B2). In cell B4, the [start_num] argument is set to 4. Therefore the search begins at the fourth character of the within_text string and so the function finds the second occurrence of "i".

Page | 96

2.5.7 Replace existing text with new text in a string by using the SUBSTITUTE function. The Excel Substitute function replaces one or more instances of a given text string, within an original text string. To replace existing text with new text in a string, use the SUBSTITUTE function.

Substitute Function Examples The spreadsheets below provide four examples of the Excel Substitute Function. Formulas:

Results:

A 1 abab 2 abab John is 5 years 3 old John is 5 years 4 old

B =SUBSTITUTE( A1, "a", "X" ) =SUBSTITUTE( A2, "a", "X", 2 ) =SUBSTITUTE( A3, "John", "Jack" )

A 1 abab 2 abab John is 5 years 3 old John is 5 years 4 old

B XbXb abXb Jack is 5 years old John is 6 years old

=SUBSTITUTE( A4, "5", "6" )

Page | 97

2.6

Demonstrate understanding to the difference between relative, absolute and mixed reference.

2.6.1

Define and uses relative reference.

2.6.2

Define and create absolute reference.

2.6.3

Use the mixed reference to make combination of relative and absolute reference

Page | 98

2.6.1 Define and uses relative reference. By default, Excel uses relative reference. See the formula in cell D2 below. Cell D2 references (points to) cell B2 and cell C2. Both references are relative.

1. Select cell D2, click on the lower right corner of cell D2 and drag it down to cell D5.

Cell D3 references cell B3 and cell C3. Cell D4 references cell B4 and cell C4. Cell D5 references cell B5 and cell C5. In other words: each cell references its two neighbors on the left.

2.6.2 Define and create absolute reference. See the formula in cell E3 below. 1. To create an absolute reference to cell H3, place a $ symbol in front of the column letter and row number of cell H3 ($H$3) in the formula of cell E3.

Page | 99

2. Now we can quickly drag this formula to the other cells.

The reference to cell H3 is fixed (when we drag the formula down and across). As a result, the correct lengths and widths in inches are calculated.

2.6.3 Use the mixed reference to make combination of relative and absolute reference Sometimes we need a combination of relative and absolute reference (mixed reference). 1. See the formula in cell F2 below.

Page | 100

2. We want to copy this formula to the other cells quickly. Drag cell F2 across one cell, and look at the formula in cell G2.

Do you see what happens? The reference to the price should be a fixed reference to column B. Solution: place a $ symbol in front of the column letter of cell B2 ($B2) in the formula of cell F2. In a similar way, when we drag cell F2 down, the reference to the reduction should be a fixed reference to row 6. Solution: place a $ symbol in front of the row number of cell B6 (B$6) in the formula of cell F2. Result:

Note: we don't place a $ symbol in front of the row number of B2 (this way we allow the reference to change from B2 (Jeans) to B3 (Shirts) when we drag the formula down). In a similar way, we don't place a $ symbol in front of the column letter of B6 (this way we allow the reference to change from B6 (Jan) to C6 (Feb) and D6 (Mar) when we drag the formula across).

Page | 101

3. Now we can quickly drag this formula to the other cells.

The references to column B and row 6 are fixed.

Page | 102

2.7

Manipulate and find information within Excel spreadsheets by using Lookup & Reference function

Learn all about Excel's lookup & reference functions such as the VLOOKUP, HLOOKUP, MATCH, INDEX and CHOOSE function

2.7.1

Use the VLOOKUP (Vertical lookup) function to looks for a value in a column of a table, and then returns a value in the same row from another column you specify.

2.7.2

Use the HLOOKUP (Horizontal lookup) function to looks for a value in a row of a table, and then returns a value in the same column from another row you specify.

2.7.3

Use the MATCH function to return the position of a value in a given range.

2.7.4

Use the INDEX function to return a specific value in a two-dimensional or onedimensional range.

2.7.5

Use the CHOOSE function to return a value from a list of values, based on a position number.

Page | 103

2.7.1 Use the VLOOKUP (Vertical lookup) function. The Excel VLOOKUP function 'looks up' a given value in the left-hand column of a data array (or table), and returns the corresponding value from another column of the array. The VLOOKUP (Vertical lookup) function looks for a value in the leftmost column of a table, and then returns a value in the same row from another column you specify. 1. Insert the VLOOKUP function shown below.

Explanation: the VLOOKUP function looks for the ID (104) in the leftmost column of the range $E$4:$G$7 and returns the value in the same row from the third column (third argument is set to 3). The fourth argument is set to FALSE to return an exact match or a #N/A error if not found. 2. Drag the VLOOKUP function in cell B2 down to cell B11.

Page | 104

Note: when we drag the VLOOKUP function down, the absolute reference ($E$4:$G$7) stays the same, while the relative reference (A2) changes to A3, A4, A5, etc.

Vlookup Examples Example 1 - Vlookup Requiring an Exact Match In the spreadsheet below, columns A and B list an inventory of grocery items, and their prices, and cell E2 of the spreadsheet shows a simple example of the Vlookup function being used to look up the price of an item from the inventory. A 1 Item Description

B

C

Cost ($)

2 Tinned Tomatoes

$0.90

3 Tinned Tuna

$1.50

4 Cornflakes

$3.50

5 Shortcake Biscuits

$1.00

6 Toothpaste

$4.10

Tinned Baked 7 Beans

$0.99

White Sliced 8 Bread

$0.80

9

. . .

D

E

Current Item:

Current Item Cost ($)

Cornflakes

=VLOOKUP( D2, A:B, 2, FALSE )

- returns the value $3.50

. . .

The above Vlookup function returns the price for "Cornflakes", which is $3.50. In this example: The lookup_value is the text string "Cornflakes", which is located in cell D2; The table_array is columns A-B of the spreadsheet; The col_index_num is set to 2, to denote that the value returned should be taken from column 2 of thetable_array; The [range_lookup] argument is set to FALSE, to indicate that we only want a result to be returned if an exact match to the lookup_value is found. Page | 105

Example 2 - Vlookup Requiring the Closest Match In the spreadsheet below, columns A-C list the grades that are assigned to examination marks lying within the ranges 0-44%, 45%-54%, etc. Cell F2 shows the score of 52% that was achieved by the student "Anne" in an examination. The Vlookup function in cell G2 looks up this score in column A of the spreadsheet and returns the associated grade from column C. Note that, in this example, if the exact score of 52% is not found in column A, we want, instead, to use the nearest valuebelow this score. A

B

Low 1 er

Upp er

C Grad e

2

0%

44% F

3

45%

54% E

4

55%

64% D

5

65%

74% C

6

75%

84% B

85%

100 %A

7

D

E Nam e Ann e

F Scor e

G Grade

52 =VLOOKUP( F2, A2:C7, 3, % TRUE )

- returns the value "E"

The above Vlookup function returns the grade for the score 52%, which is E. In this example: the lookup_value is the value 52%, which is located in cell F2; the table_array is the range A2-C7 of the spreadsheet; the col_index_num is set to 3, to denote that the value returned should be taken from column 3 of thetable_array; the [range_lookup] argument is set to TRUE, to indicate that, if an exact match to the lookup_value is not found, we want to use the closest value below the lookup_value.

Page | 106

2.7.2 Use the HLOOKUP (Horizontal lookup) function to looks for a value in a row of a table, and then returns a value in the same column from another row you specify. In a similar way, you can use the HLOOKUP (Horizontal lookup) function. The Excel Hlookup function 'looks up' a given value in the top row of a data array (or table), and returns the corresponding value from another row of the array.

Hlookup Function Examples Hlookup Example 1 Cells A2-F6 of the spreadsheet below, show the exam scores for 5 students in 4 different subjects. If you want to look up a specific score (eg. Biology) for one of the students (eg. Ed), this can be done using the Hlookup function, as shown in cell B10 of the spreadsheet. Formulas:

Results:

In the above example, the Hlookup function searches through the top row of the table_array (the range A2-F2), to find a match for the lookup_value (the name "Ed"). Page | 107

When the the name 'Ed' is found, the function returns the corresponding value from the 5th row of the lookup_table.

This is illustrated in the spreadsheet on the right. The function finds the name 'Ed' in the top row of the table_array and then returns the value from the 5th row of the table_array. If we change the name in cell A10 of the spreadsheet from 'Ed' to 'Cara', the Hlookup functions would automatically recalculate the function to display the exam results for Cara.

Hlookup Example 2 Cells A1-F3 of the spreadsheet below, show body types relating to body mass index (BMI), for the ranges 0 - 18.4, 18.5 - 24.9, 25.0 - 29.9 and over 30. Cell C6 shows the user's current BMI, which is 23.5, and cell C7 shows the Hlookup function that is used to look up the body type that relates to this BMI.

The Hlookup function in the above spreadsheet returns the result "Normal Weight", which is the correct body type for a BMI of 23.5. Note that, in this example, the [range_lookup] argument is set to TRUE, to tell that function that, if it cannot find an exact match to the supplied lookup_value, it should use the closest match below this value. Therefore, for all BMIs up to and including 18.4 the function would return "Underweight", for all BMIs between 18.5 and 24.9, the function would return "Normal Weight", etc. Page | 108

2.7.3 Use the MATCH function. The Excel Match function looks up a value in an array, and returns the position of the value within the array. The user can specify that the function should only return a result if an exact match is found, or that the function should return the position of the closest match (above or below), if an exact match is not found.

Note: Yellow found at position 3 in the range E4:E7. The third argument is optional. Set this argument to 0 to return the position of the value that is exactly equal to lookup_value (A2) or a #N/A error if not found.

Excel Match Function Example 1 The following spreadsheet shows the Excel Match function used with the [match_type] argument set to 0. Therefore, in these examples, the function only returns a result if an exact match to the lookup_value is found. Otherwise, the function returns an error. In each case, the lookup_array is the range of cells A1 - A5. Formulas:

A 1 cccc

Results:

B =MATCH( "aaaa", A1:A5,

A 1 cccc

B C 3 - matches "aaaa" so returns Page | 109

2 3 4 5

dddd aaaa bbbb eeee

0) =MATCH( "?eee", A1:A5, 0 ) =MATCH( "*b", A1:A5, 0 )

position 3 - matches "eeee" so returns position 55 - matches "bbbb" so returns 4 position 4

ddd 2d 3 aaaa bbb 4b 5 eeee

Note that, in the above examples, as the [match_type] argument is set to 0, the text strings in the lookup_array (cells A1-A5) do not need to be ordered.

Match Function Example 2 The following spreadsheet also shows the Excel MATCH function used with the [match_type] argument set to 0, but in this case the function is used to look up numeric values. In each of these examples, the lookup_array is the range of cells A1 - A6. Formulas:

Results:

A 1 2 3 4 5 6

B 7 =MATCH( 4, A1:A6, 0 ) 2 =MATCH( 8, A1:A6, 0 ) 4 =MATCH( 10, A1:A6, 0 ) 1 8 11

A 1 2 3 4 5 6

7 2 4 1 8 11

B

C 3 - returns position 3 5 - returns position 5 #N/A - no exact match - returns error

As in the previous examples, as the [match_type] argument is set to 0, the values in the lookup_array (cells A1-A6) do not need to be ordered.

2.7.4 Use the INDEX function to return a specific value in a twodimensional or one-dimensional range. The Excel Index function returns a reference to a cell that lies in a specified row and column of a range of cells. The INDEX function returns a specific value in a two-dimensional or one-dimensional range. Page | 110

Note: 92 found at the intersection of row 3 and column 2 in the range E4:F7.

Note: 97 found at position 3 in the range E4:E7.

Excel Index Function (Array Format) Examples Example 1 In the following example, the Index function returns a reference to row 5 of the range C1:C5, which is cell C5. This evaluates to the value 8. Formula

Result

Page | 111

Example 2 In the following example, the Index function returns a reference to row 5 and column 2 of the range C1:D5, which is cell D5. This evaluates to the value 3. Formula

Result

2.7.5 Use the CHOOSE function. The Excel Choose function returns a value from an array, that corresponds to a supplied index number (position). It may help to think of the Choose function as a function that returns the nth entry in a given list. The CHOOSE function returns a value from a list of values, based on a position number.

Note: Boat found at position 3.

Excel Choose Function Examples Example 1 Imagine you are working on the spreadsheet below and you want to set the cells in Column B, to have the following values, depending on the value of the corresponding cell in Column A.

Page | 112

1 - red; 2 - blue; 3 - green; 4 - brown

The Excel Choose function can be used to assign the correct value to the cells of Column B, as shown below: Formulas:

1 2 3 4

A 4 2 3 1

B =CHOOSE( A1, "red", "blue", "green", "brown" ) =CHOOSE( A2, "red", "blue", "green", "brown" ) =CHOOSE( A3, "red", "blue", "green", "brown" ) =CHOOSE( A4, "red", "blue", "green", "brown" )

Results:

1 2 3 4

A 4 2 3 1

B brown blue green red

Example 2 The Excel Choose function can also return cell references, as shown in the following example. In this case, the reference that is returned from the Choose function is then provided to the Excel SUM Function. Formulas:

1 2 3 4

A B C 10 3 =SUM( CHOOSE( B1, A1, A1:A2, A1:A3, A1:A4 ) ) 11 12 13

Results:

1 2 3 4

A B 10 3 11 12 13

C 33

In the example above, the Choose function returns the cell reference A1:A3. This is then passed to the SUM function which calculates the sum of the values in the cell range A1:A3 and returns the value 33.

Page | 113

2.8

Recognize and deal with some common formula errors in Excel.

his chapter teaches you how to deal with some common formula errors in Excel.

2.8.1

##### error

2.8.2

#NAME? error

2.8.3

#VALUE! error

2.8.4

#DIV/0! error

2.8.5

#REF! error

Page | 114

2.8.1 ##### error When your cell contains this error code, the column isn't wide enough to display the value.

1. Click on the right border of the column A header and increase the column width.

Tip: double click the right border of the column A header to automatically fit the widest cell in column A.

2.8.2 #NAME? error The #NAME? error occurs when Excel does not recognize text in a formula.

Simply correct SU to SUM. Page | 115

2.8.3 #VALUE! Error Excel displays the #VALUE! error when a formula has the wrong type of argument.

a. Change the value of cell A3 to a number. b. Use a function to ignore cells that contain text.

Page | 116

2.8.4 #DIV/0! Error Excel displays the #DIV/0! error when a formula tries to divide a number by 0 or an empty cell.

a. Change the value of cell A2 to a value that is not equal to 0. b. Prevent the error from being displayed by using the logical function IF.

Explanation: if cell A2 equals 0, an empty string is displayed. If not, the result of the formula A1/A2 is displayed.

2.8.5 #REF! error Excel displays the #REF! error when a formula refers to a cell that is not valid. 1. Cell C1 references cell A1 and cell B1.

Page | 117

2. Delete column B. To achieve this, right click the column B header and click Delete.

3. Select cell B1. The reference to cell B1 is not valid anymore.

4. To fix this error, you can either delete +#REF! in the formula of cell B1 or you can undo your action by clicking Undo in the Quick Access Toolbar (or press CTRL + z).

Page | 118

3 Utilize the Excel data analysis tools to perform processes of inspecting, cleaning, transforming, and modeling data. This section illustrates the powerful features Excel has to offer to analyze data.

3.1 Highlight cells with a certain color, depending on the cell's value by using the Conditional Formatting tool 3.2 Reordering and filtering data in the Excel sheet 3.3 Represent the data graphically 3.4 Use the pivot table to extract the significance from a large, detailed data set. 3.5 Utilize Tables to analyze your data in Excel quickly and easily 3.6 Use the What-If Analysis in Excel to try out different values (scenarios) for formulas.

Page | 119

3.1

Reordering and filtering data in the Excel sheet

3.1.1

Execute sorting on one column.

3.1.2

Execute sorting on multiple columns.

3.1.3

Use the Filter tool to display records that meet certain criteria.

Page | 120

3.1.1 Execute sorting on one column. Sorting data is an integral part of data analysis. You might want to put a list of names in alphabetical order, compile a list of product inventory levels from highest to lowest, or order rows by colors or icons. Sorting data helps you quickly visualize and understand your data better, organize and find the data that you want, and ultimately make more effective decisions. You can sort your Excel data on one column or multiple columns. You can sort in ascending or descending order. To sort on one column, execute the following steps. 1. Click any cell in the column you want to sort.

2. To sort in ascending order, on the Data tab, click AZ.

Page | 121

Result:

Note: to sort in descending order, click ZA.

3.1.2 Execute sorting on multiple columns. To sort on multiple columns, execute the following steps. 1. On the Data tab, click Sort.

The Sort dialog box appears. 2. Select Last Name from the 'Sort by' drop-down list. Page | 122

3. Click on Add Level. 4. Select Sales from the 'Then by' drop-down list.

5. Click OK. Result. Records are sorted by Last Name first and Sales second.

Page | 123

3.1.3 Use the Filter tool. Filter your Excel data if you only want to display records that meet certain criteria. 1. Click any single cell inside a data set. 2. On the Data tab, click Filter.

Arrows in the column headers appear.

3. Click the arrow next to Country. 4. Click on Select All to clear all the check boxes, and click the check box next to USA.

Page | 124

5. Click OK. Result. Excel only displays the sales in the USA.

6. Click the arrow next to Quarter. 7. Click on Select All to clear all the check boxes, and click the check box next to Qtr 4.

Page | 125

8. Click OK. Result. Excel only displays the sales in the USA in Qtr 4.

9. To remove the filter, on the Data tab, click Clear. To remove the filter and the arrows, click Filter.

Page | 126

3.2

Highlight cells with a certain color, depending on the cell's value by using the Conditional Formatting tool

3.2.1

Highlight Cells Rules

3.2.2

Clear Rules

3.2.3

Use the Top/Bottom Rules

Page | 127

3.2.1 Highlight Cells Rules Conditional formatting quickly highlights important information in a spreadsheet. But sometimes the built-in formatting rules don’t go quite far enough. Adding your own formula to a conditional formatting rule gives it a power boost to help you do things the built-in rules can’t do. Conditional formatting in Excel enables you to highlight cells with a certain color, depending on the cell's value

To highlight cells that are greater than a value, execute the following steps. 1. Select the range A1:A10.

2. On the Home tab, click Conditional Formatting, Highlight Cells Rules, Greater Than...

Page | 128

3. Enter the value 80 and select a formatting style.

4. Click OK. Result. Excel highlights the cells that are greater than 80.

5. Change the value of cell A1 to 81. Result. Excel changes the format of cell A1 automatically.

Note: you can also highlight cells that are less than a value, between a low and high value, etc. Page | 129

3.2.2 Clear Rules To clear a conditional formatting rule, execute the following steps. 1. Select the range A1:A10.

2. On the Home tab, click Conditional Formatting, Clear Rules, Clear Rules from Selected Cells.

Page | 130

3.2.3 Use the Top/Bottom Rules To highlight cells that are above the average of the cells, execute the following steps. 1. Select the range A1:A10.

2. On the Home tab, click Conditional Formatting, Top/Bottom Rules, Above Average...

Page | 131

3. Select a formatting style.

4. Click OK. Result. Excel calculates the average (42.5) and formats the cells that are above this average.

Note: you can also highlight the top 10 items, the top 10 %, etc. The sky is the limit!

Page | 132

3.3

Represent the data graphically

A simple chart in Excel can say more than a sheet full of numbers. As you'll see, creating charts is very easy.

3.3.1

Create a Chart

3.3.2

Change Chart Type

3.3.3

Switch Row/Column

3.3.4

Add a chart title

3.3.5

Change the Legend Position

3.3.6

Use data labels to focus your readers' attention on a single data series or data point.

Page | 133

3.3.1 Create a Chart Charts are used to display series of numeric data in a graphical format to make it easier to understand large quantities of data and the relationship between different series of data. To create a chart in Excel, you start by entering the numeric data for the chart on a worksheet. Then you can plot that data into a chart by selecting the chart type that you want to use on the Office Fluent Ribbon (Insert tab, Charts group). To create a line chart, execute the following steps. 1. Select the range A1:D7. 2. On the Insert tab, in the Charts group, choose Line, and select Line with Markers.

Page | 134

Result:

3.3.2 Change Chart Type For most 2-D charts, you can change the chart type of the whole chart to give the chart a different look, or you can select a different chart type for any single data series, which turns the chart into a combination chart. You can easily change to a different type of chart at any time. 1. Select the chart. 2. On the Insert tab, in the Charts group, choose Column, and select Clustered Column.

Page | 135

Result:

3.3.3 Switch Row/Column If you want the animals, displayed on the vertical axis, to be displayed on the horizontal axis instead, execute the following steps. 1. Select the chart. The Chart Tools contextual tab activates. 2. On the Design tab, click Switch Row/Column.

Result:

Page | 136

3.3.4 Add a chart title. You can add titles to an Excel 2010 chart to help describe its purpose. By default, titles are not added when you create a basic chart, but you can add them later manually. In addition to a main chart title that is generally displayed above a chart, you can add descriptive titles to the x-axis (category axis) and the y-axis (value axis). To add a chart title, execute the following steps. 1. Select the chart. The Chart Tools contextual tab activates. 2. On the Layout tab, click Chart Title, Above Chart.

3. Enter a title. For example, Population. Result:

Page | 137

3.3.5 Change the Legend Position. By default, the legend appears to the right of the chart. To move the legend to the bottom of the chart, execute the following steps. 1. Select the chart. The Chart Tools contextual tab activates. 2. On the Layout tab, click Legend, Show Legend at Bottom.

Result:

Page | 138

3.3.6 Use data labels. You can use data labels to focus your readers' attention on a single data series or data point. 1. Select the chart. The Chart Tools contextual tab activates. 2. Click an orange bar to select the Jun data series. Click again on an orange bar to select a single data point. 3. On the Layout tab, click Data Labels, Outside End.

Result:

Page | 139

3.4

Use the pivot table to extract the significance from a large, detailed data set.

3.4.1

Insert a Pivot Table

3.4.2

Drag fields

3.4.3

Sort the pivot table.

3.4.4

Filter the data

3.4.5

Change Summary Calculation

3.4.6

Create Two-dimensional Pivot Table

Page | 140

Pivot tables are one of Excel's most powerful features. A pivot table allows you to extract the significance from a large, detailed data set. Our data set consists of 214 rows and 6 fields. Order ID, Product, Category, Amount, Date and Country.

3.4.1 Insert a Pivot Table. To insert a pivot table, execute the following steps. 1. Click any single cell inside the data set. 2. On the Insert tab, click PivotTable.

The following dialog box appears. Excel automatically selects the data for you. The default location for a new pivot table is New Worksheet. 3. Click OK.

Page | 141

3.4.2 Drag fields. The PivotTable field list appears. To get the total amount exported of each product, drag the following fields to the different areas. 1. Product Field to the Row Labels area. 2. Amount Field to the Values area. 3. Country Field to the Report Filter area.

Page | 142

Below you can find the pivot table. Bananas are our main export product. That's how easy pivot tables can be!

3.4.3 Sort the pivot table. To get Banana at the top of the list, sort the pivot table. 1. Click any cell inside the Total column. 2. The PivotTable Tools contextual tab activates. On the Options tab, click the Sort Largest to Smallest button (ZA).

Page | 143

Result.

3.4.4 Filter the data. Because we added the Country field to the Report Filter area, we can filter this pivot table by Country. For example, which products do we export the most to France? 1. Click the filter drop-down and select France. Result. Apples are our main export product to France.

Note: you can use the standard filter (triangle next to Product) to only show the totals of specific products.

Page | 144

3.4.5 Change Summary Calculation. By default, Excel summarizes your data by either summing or counting the items. To change the type of calculation that you want to use, execute the following steps. 1. Click any cell inside the Total column. 2. Right click and click on Value Field Settings...

3. Choose the type of calculation you want to use. For example, click Count.

4. Click OK. Result. 16 out of the 28 orders to France were 'Apple' orders.

Page | 145

3.4.6 Create Two-dimensional Pivot Table. If you drag a field to the Row Labels area and Column Labels area, you can create a twodimensional pivot table. For example, to get the total amount exported to each country, of each product, drag the following fields to the different areas. 1. Country Field to the Row Labels area. 2. Product Field to the Column Labels area. 3. Amount Field to the Values area. 4. Category Field to the Report Filter area.

Page | 146

Below you can find the two-dimensional pivot table.

To easily compare these numbers, create a pivot chart and apply a filter. Maybe this is one step too far for you at this stage, but it shows you one of the many other powerful pivot table features Excel has to offer.

Page | 147

3.5

Utilize Tables to analyze your data in Excel quickly and easily

3.5.1

Insert a Table

3.5.2

Sort a Table

3.5.3

Filter a Table

3.5.4

Display a total row

Page | 148

Tables allow you to analyze your data in Excel quickly and easily. Learn how to insert, sort and filter a table, and how to display a total row at the end of a table.

3.5.1 Insert a Table. To insert a table, execute the following steps. 1. Click any single cell inside the data set.

2. On the Insert tab, click Table.

3. Excel automatically selects the data for you. Check 'My table has headers' and click on OK.

Page | 149

Result. Excel creates a nicely formatted table for you. This may still seem like a normal data range to you but many powerful features are now just a click of a button away.

Note: the Table Tools contextual tab (with the underlying Design tab selected) is the starting point for working with tables. If at any time you lose this tab, simply click any cell within the table and it will activate again. Choose a table style you like. Hover over a table style and Excel gives you a life preview.

Page | 150

3.5.2 Sort a Table. To sort by Last Name first and Sales second, first sort by Sales, next sort by Last Name (the exact opposite). 1. Click the arrow next to Sales and click Sort Smallest to Largest. 2. Click the arrow next to Last Name and click Sort A to Z. Result.

3.5.3 Filter a Table. To filter a table, execute the following steps. 1. Click the arrow next to Country and only check USA. Result.

Page | 151

3.5.4 Display a total row. To display a total row at the end of the table, execute the following steps. 1. On the Design tab, in the Table Style Options group, check Total Row.

Page | 152

2. Click any cell in the last row to calculate the Total (Average, Count, Max, Min, Sum etc.) of a column. For example, calculate the sum of the Sales column.

Note: in the formula bar see how Excel uses the SUBTOTAL function to calculate the sum. 109 is the argument for Sum if you use the SUBTOTAL function. Excel uses this function (and not the standard SUM function) to correctly calculate table totals of filtered tables.

Page | 153

3.6

Use the What-If Analysis in Excel to try out different values (scenarios) for formulas.

3.6.1

Create Different Scenarios

3.6.2

Compare the results of these scenarios

3.6.3

Perform Goal Seek

Page | 154

What-If Analysis in Excel allows you to try out different values (scenarios) for formulas. The following example helps you master what-if analysis quickly and easily. Assume you own a book store and have 100 books in storage. You sell a certain % for the highest price of $50 and a certain % for the lower price of $20.

If you sell 60% for the highest price, cell D10 calculates a total profit of 60 * $50 + 40 * $20 = $3800.

3.6.1 Create Different Scenarios. But what if you sell 70% for the highest price? And what if you sell 80% for the highest price? Or 90%, or even 100%? Each different percentage is a different scenario. You can use the Scenario Manager to create these scenarios. Note: You can simply type in a different percentage into cell C4 to see the corresponding result of a scenario in cell D10. However, what-if analysis enables you to easily compare the results of different scenarios. Read on. 1. On the Data tab, click What-If Analysis and select Scenario Manager from the list.

Page | 155

The Scenario Manager dialog box appears. 2. Add a scenario by clicking on Add.

3. Type a name (60% highest), select cell C4 (% sold for the highest price) for the Changing cells and click on OK.

4. Enter the corresponding value 0.6 and click on OK again. Page | 156

5. Next, add 4 other scenarios (70%, 80%, 90% and 100%). Finally, your Scenario Manager should be consistent with the picture below:

Note: to see the result of a scenario, select the scenario and click on the Show button. Excel will change the value of cell C4 accordingly for you to see the corresponding result on the sheet.

3.6.2 Compare the results of these scenarios. Scenario Summary To easily compare the results of these scenarios, execute the following steps. 1. Click the Summary button in the Scenario Manager. 2. Next, select cell D10 (total profit) for the result cell and click on OK. Page | 157

Result:

Conclusion: if you sell 70% for the highest price, you obtain a total profit of $4100, if you sell 80% for the highest price, you obtain a total profit of $4400, etc. That's how easy whatif analysis in Excel can be.

3.6.3 Perform Goal Seek What if you want to know how many books you need to sell for the highest price, to obtain a total profit of exactly $4700? You can use Excel's Goal Seek feature to find the answer. 1. On the Data tab, click What-If Analysis, Goal Seek.

Page | 158

The Goal Seek dialog box appears. 2. Select cell D10. 3. Click in the 'To value' box and type 4700. 4. Click in the 'By changing cell' box and select cell C4. 5. Click OK.

Result. You need to sell 90% of the books for the highest price to obtain a total profit of exactly $4700.

Page | 159

4 Make an integration of the Microsoft's event-driven programming language Visual Basic with Microsoft Excel to build customized solutions and programs to enhance the capabilities of Excel.

5.1. Create macros. 5.2. Use the Range object & variables 5.3. Use the If Then Statement & Loops to execute code lines if a specific condition is met and loop through a range of cells 5.4. Manipulate strings and Date & Time in Excel VBA. 5.5. Work with Events and Array 5.6. Create Functions ,Sub and Application object 5.7. Create and use Active X control and UserForm

Page | 160

4.1

Create macros.

4.1.1

Turn on the Developer tab

4.1.2

Place a command button

4.1.3

Assign a Macro

4.1.4

Open the Visual Basic Editor

4.1.5

Create MsgBox

4.1.6

Workbook and Worksheet Object

4.1.7

Record Macro

Page | 161

With Excel VBA you can automate tasks in Excel by writing so called macros. In this chapter, learn how to create a simple macro which will be executed after clicking on a command button. First, turn on the Developer tab.

4.1.1 Turn on the Developer tab To turn on the Developer tab, execute the following steps. 1. Right click anywhere on the ribbon, and then click Customize the Ribbon.

2. Under Customize the Ribbon, on the right side of the dialog box, select Main tabs (if necessary). 3. Check the Developer check box.

Page | 162

4. Click OK. 5. You can find the Developer tab next to the View tab.

4.1.2 Place a command button To place a command button on your worksheet, execute the following steps. 1. On the Developer tab, click Insert. 2. In the ActiveX Controls group, click Command Button.

3. Drag a command button on your worksheet.

Page | 163

4.1.3 Assign a Macro To assign a macro (one or more code lines) to the command button, execute the following steps. 1. Right click CommandButton1 (make sure Design Mode is selected). 2. Click View Code.

The Visual Basic Editor appears. 3. Place your cursor between Private Sub CommandButton1_Click() and End Sub. 4. Add the code line shown below.

Page | 164

Note: the window on the left with the names Sheet1, Sheet2 and Sheet3 is called the Project Explorer. If the Project Explorer is not visible, click View, Project Explorer. To add the Code window for the first sheet, click Sheet1 (Sheet1). 5. Close the Visual Basic Editor. 6. Click the command button on the sheet (make sure Design Mode is deselected). Result:

Congratulations. You've just created a macro in Excel!

Page | 165

4.1.4 Open the Visual Basic Editor To open the Visual Basic Editor, on the Developer tab, click Visual Basic.

The Visual Basic Editor appears.

4.1.5 Create MsgBox In VBA, MsgBox function is used for displaying a dialog box with a predefined message. It returns an integer value based on the button clicked by the user, this helps to keep a track of the option selected by the user. Page | 166

The MsgBox is a dialog box in Excel VBA you can use to inform the users of your program. Place a command button on your worksheet and add the following code lines: 1. A simple message. MsgBox "This is fun" Result when you click the command button on the sheet:

2. A little more advanced message. First, enter a number into cell A1. MsgBox "Entered value is " & Range("A1").Value Result when you click the command button on the sheet:

Note: we used the & operator to concatenate (join) two strings. Although Range("A1").value is not a string, it works here. 3. To start a new line in a message, use vbNewLine. MsgBox "Line 1" & vbNewLine & "Line 2" Result when you click the command button on the sheet:

Page | 167

4.1.6 Workbook and Worksheet Object. Learn more about the Workbook and Worksheet object in Excel VBA.

Object Hierarchy In Excel VBA, an object can contain another object, and that object can contain another object, etc. In other words, Excel VBA programming involves working with an object hierarchy. This probably sounds quite confusing, but we will make it clear. The mother of all objects is Excel itself. We call it the Application object. The application object contains other objects. For example, the Workbook object (Excel file). This can be any workbook you have created. The Workbook object contains other objects, such as the Worksheet object. The Worksheet object contains other objects, such as the Range object. The Create a Macro chapter illustrates how to run code by clicking on a command button. We used the following code line: Range("A1").Value = "Hello" but what we really meant was: Application.Workbooks("create-a-macro").Worksheets(1).Range("A1").Value = "Hello" Note: the objects are connected with a dot. Fortunately, we do not have to add a code line this way. That is because we placed our command button in create-a-macro.xls, on the first worksheet. Be aware that if you want to change things on different worksheets, you have to include the Worksheet object. Read on.

Page | 168

Collections You may have noticed that Workbooks and Worksheets are both plural. That is because they are collections. The Workbooks collection contains all the Workbook objects that are currently open. The Worksheets collection contains all the Worksheet objects in a workbook.

You can refer to a member of the collection, for example, a single Worksheet object, in three ways. 1. Using the worksheet name. Worksheets("Sales").Range("A1").Value = "Hello" 2. Using the index number (1 is the first worksheet starting from the left). Worksheets(1).Range("A1").Value = "Hello" 3. Using the CodeName. Sheet1.Range("A1").Value = "Hello" To see the CodeName of a worksheet, open the Visual Basic Editor. In the Project Explorer, the first name is the CodeName. The second name is the worksheet name (Sales).

Page | 169

Note: the CodeName remains the same if you change the worksheet name or the order of your worksheets so this is the safest way to reference a worksheet. Click View, Properties Window to change the CodeName of a worksheet. There is one disadvantage, you cannot use the CodeName if you reference a worksheet in a different workbook.

Properties and Methods Now let's take a look at some properties and methods of the Workbooks and Worksheets collection. Properties are something which an collection has (they describe the collection), while methods do something (they perform an action with an collection). Place a command button on your worksheet and add the code lines: 1. The Add method of the Workbooks collection creates a new workbook. Workbooks.Add

Note: the Add method of the Worksheets collection creates a new worksheet. 2. The Count property of the Worksheets collection counts the number of worksheets in a workbook. MsgBox Worksheets.Count

Page | 170

Result when you click the command button on the sheet:

Note: the Count property of the Workbooks collection counts the number of active workbooks.

4.1.7 Record Macro. The Macro Recorder, a very useful tool included in Excel VBA, records every task you perform with Excel. All you have to do is record a specific task once. Next, you can execute the task over and over with the click of a button. The Macro Recorder is also a great help when you don't know how to program a specific task in Excel VBA. Simply open the Visual Basic Editor after recording the task to see how it can be programmed. Unfortunately, there are a lot of things you cannot do with the Macro Recorder. For example, you cannot loop through a range of data with the Macro Recorder. Moreover, the Macro Recorder uses a lot more code than is required, which can slow your process down.

Record a Macro 1. On the Developer tab, click Record Macro.

Page | 171

2. Enter a name. 3. Select This Workbook from the drop-down list. As a result, the macro will only be available in the current workbook.

Note: if you store your macro in Personal Macro Workbook, the macro will be available to all your workbooks (Excel files). This is possible because Excel stores your macro in a hidden workbook that opens automatically when Excel starts. If you store your macro in New Workbook, the macro will only be available in an automatically new opened workbook. 4. Click OK. 5. Right mouse click on the active cell (selected cell). Be sure not to select any other cell! Next, click Format Cells.

Page | 172

6. Select Percentage.

Page | 173

7. Click OK. 8. Finally, click Stop Recording.

Congratulations. You've just recorded a macro with the Macro Recorder!

Run a Recorded Macro Now we'll test the macro to see if it can change the number format to Percentage. 1. Enter some numbers between 0 and 1. 2. Select the numbers.

3. On the Developer tab, click Macros.

Page | 174

4. Click Run.

Page | 175

Result:

See the Macro To take a look at the macro, open the Visual Basic Editor.

Note: the macro has been placed into a module called Module1. Code placed into a module is available to the whole workbook. That means, you can select Sheet2 or Sheet3 and change the number format of cells on these sheets as well. Remember, code placed on a sheet (assigned to a command button) is only available for that particular sheet.

Page | 176

4.2

Use the Range object & variables

4.2.1

Assign value to a cell

4.2.2

Assign value to a range of cells

4.2.3

Use the Cell to assign value

4.2.4

Declare a Range Object

4.2.5

Use the Select method to selects a range

4.2.6

Use the Rows and Column property to access to a specific row or column of a range.

4.2.7

Use the Copy and Paste method

4.2.8

Use the ClearContents method.

4.2.9

Use the Count property

4.2.10 Declare, initialize and display a variable in Excel VBA

Page | 177

The Range object, which is the representation of a cell (or cells) on your worksheet, is the most important object of Excel VBA. This chapter gives an overview of the properties and methods of the Range object. Properties are something which an object has (they describe the object), while methods do something (they perform an action with an object).

4.2.1 Assign value to a cell.

Range Examples Place a command button on your worksheet and add the following code line: Range("B3").Value = 2 Result when you click the command button on the sheet:

4.2.2 Assign value to a range of cells. Code: Range("A1:A4").Value = 5 Result:

Page | 178

Code: Range("A1:A2,B3:C4").Value = 10 Result:

Note: to refer to a named range in your Excel VBA code, use a code line like this: Range("Prices").Value = 15

4.2.3 Use the Cell to assign value.

Cells Instead of Range, you can also use Cells. Using Cells is particularly useful when you want to loop through ranges. Code: Cells(3, 2).Value = 2 Result:

Explanation: Excel VBA enters the value 2 into the cell at the intersection of row 3 and column 2. Page | 179

Code: Range(Cells(1, 1), Cells(4, 1)).Value = 5 Result:

4.2.4 Declare a Range Object. You can declare a Range object by using the keywords Dim and Set. Code: Dim example As Range Set example = Range("A1:C4") example.Value = 8 Result:

Page | 180

4.2.5 Use the Select method to selects a range. An important method of the Range object is the Select method. The Select method simply selects a range. Code: Dim example As Range Set example = Range("A1:C4") example.Select Result:

4.2.6 Use the Rows and Column property to access to a specific row or column of a range. The Rows property gives access to a specific row of a range. Code: Dim example As Range Set example = Range("A1:C4") example.Rows(3).Select Result:

Page | 181

Note: border for illustration only. The Columns property gives access to a specific column of a range. Code: Dim example As Range Set example = Range("A1:C4") example.Columns(2).Select Result:

Note: border for illustration only.

4.2.7 Use the Copy and Paste method. The Copy and Paste method are used to copy a range and to paste it somewhere else on the worksheet. Code: Range("A1:A2").Select Selection.Copy Page | 182

Range("C3").Select ActiveSheet.Paste Result:

Although this is allowed in Excel VBA, it is much better to use the code line below which does exactly the same. Range("C3:C4").Value = Range("A1:A2").Value

4.2.8 Use the ClearContents method. To clear the content of an Excel range, you can use the ClearContents method. Range("A1").ClearContents or simply use: Range("A1").Value = ""

Note: use the Clear method to clear the content and format of a range. Use the ClearFormats method to clear the format only.

Page | 183

4.2.9 Use the Count property. With the Count property, you can count the number of cells, rows and columns of a range.

Note: border for illustration only. Code: Dim example As Range Set example = Range("A1:C4") MsgBox example.Count Result:

Code: Dim example As Range Set example = Range("A1:C4") MsgBox example.Rows.Count Result:

Page | 184

Note: in a similar way, you can count the number of columns of a range.

4.2.10

Declare, initialize and display a variable in Excel VBA.

This chapter teaches you how to declare, initialize and display a variable in Excel VBA. Letting Excel VBA know you are using a variable is called declaring a variable. Initializing simply means assigning a beginning (initial) value to a variable. Place a command button on your worksheet and add the code lines below. To execute the code lines, click the command button on the sheet.

Integer Integer variables are used to store whole numbers. Dim x As Integer x=6 Range("A1").Value = x Result:

Explanation: the first code line declares a variable with name x of type Integer. Next, we initialize x with value 6. Finally, we write the value of x to cell A1.

String String variables are used to store text. Code:

Page | 185

Dim book As String book = "bible" Range("A1").Value = book Result:

Explanation: the first code line declares a variable with name book of type String. Next, we initialize book with the text bible. Always use apostrophes to initialize String variables. Finally, we write the text of the variable book to cell A1.

Double A variable of type Double is more accurate than a variable of type Integer and can also store numbers after the comma. Code: Dim x As Integer x = 5.5 MsgBox "value is " & x Result:

Page | 186

But that is not the right value! We initialized the variable with value 5.5 and we get the value 6. What we need is a variable of type Double. Code: Dim x As Double x = 5.5 MsgBox "value is " & x Result:

Note: Long variables have even larger capacity. Always use variables of the right type. As a result, errors are easier to find and your code will run faster.

Boolean Use a Boolean variable to hold the value True or False. Code: Dim continue As Boolean continue = True If continue = True Then MsgBox "Boolean variables are cool" Result:

Page | 187

Explanation: the first code line declares a variable with name continue of type Boolean. Next, we initialize continue with the value True. Finally, we use the Boolean variable to only display a MsgBox if the variable holds the value True.

4.3

Use the If Then Statement & Loops to execute code lines if a specific condition is met and loop through a range of cells

4.3.1

Use the If Then statement in Excel VBA to execute code lines if a specific condition is met

4.3.2

Use a single For - Nest loop to loop through a one-dimensional range of cells.

4.3.3

Use a double For- Nest loop to loop through a two-dimensional range of cells.

4.3.4

Use a triple loop to loop through two-dimensional ranges on multiple Excel worksheets.

4.3.5

Use the Do While Loop

Page | 188

4.3.1 Use the If Then statement in Excel VBA. Use the If Then statement in Excel VBA to execute code lines if a specific condition is met. Place a command button on your worksheet and add the following code lines: Dim score As Integer, result As String score = Range("A1").Value If score >= 60 Then result = "pass" Range("B1").Value = result Explanation: if score is greater than or equal to 60, Excel VBA returns pass. Result when you click the command button on the sheet:

Note: if score is less than 60, Excel VBA places the value of the empty variable result into cell B1.

Else Statement Place a command button on your worksheet and add the following code lines: Dim score As Integer, result As String score = Range("A1").Value If score >= 60 Then result = "pass" Else result = "fail" Page | 189

End If Range("B1").Value = result Explanation: if score is greater than or equal to 60, Excel VBA returns pass, else Excel VBA returns fail. Result when you click the command button on the sheet:

Note: only if you have one code line after Then and no Else statement, it is allowed to place a code line directly after Then and to omit (leave out) End If (first example). Otherwise start a new line after the words Then and Else and end with End If (second example).

4.3.2 Use a single For - Next loop. Looping is one of the most powerful programming techniques. A loop in Excel VBA enables you to loop through a range of cells with just a few codes lines. You can use a single loop to loop through a one-dimensional range of cells. Place a command button on your worksheet and add the following code lines: Dim i As Integer For i = 1 To 6 Cells(i, 1).Value = 100 Next i Result when you click the command button on the sheet:

Page | 190

Explanation: The code lines between For and Next will be executed six times. For i = 1, Excel VBA enters the value 100 into the cell at the intersection of row 1 and column 1. When Excel VBA reaches Next i, it increases i with 1 and jumps back to the For statement. For i = 2, Excel VBA enters the value 100 into the cell at the intersection of row 2 and column 1, etc. Note: it is good practice to always indent (tab) the code between the words For and Next. This makes your code easier to read.

4.3.3 Use a double For- Next loop. You can use a double loop to loop through a two-dimensional range of cells. Place a command button on your worksheet and add the following code lines: Dim i As Integer, j As Integer For i = 1 To 6 For j = 1 To 2 Cells(i, j).Value = 100 Next j Next i Result when you click the command button on the sheet:

Page | 191

Explanation: For i = 1 and j = 1, Excel VBA enters the value 100 into the cell at the intersection of row 1 and column 1. When Excel VBA reaches Next j, it increases j with 1 and jumps back to the For j statement. For i = 1 and j = 2, Excel VBA enters the value 100 into the cell at the intersection of row 1 and column 2. Next, Excel VBA ignores Next j because j only runs from 1 to 2. When Excel VBA reaches Next i, it increases i with 1 and jumps back to the For i statement. For i = 2 and j = 1, Excel VBA enters the value 100 into the cell at the intersection of row 2 and column 1, etc.

4.3.4 Use a triple loop . You can use a triple loop to loop through two-dimensional ranges on multiple Excel worksheets. Place a command button on your worksheet and add the following code lines: Dim c As Integer, i As Integer, j As Integer For c = 1 To 3 For i = 1 To 6 For j = 1 To 2 Worksheets(c).Cells(i, j).Value = 100 Next j Next i Next c

Page | 192

Explanation: The only change made compared to the code for the double loop is that we have added one more loop and added Worksheets(c). in front of Cells to get the twodimensional range on the first sheet for c = 1, the second sheet for c = 2 and the third sheet for c = 3. Download the Excel file to see this result.

4.3.5 Use the Do While Loop. Besides the For Next loop, there are other loops in Excel VBA. For example, the Do While Loop. Code placed between Do While and Loop will be repeated as long as the part after Do While is true. 1. Place a command button on your worksheet and add the following code lines: Dim i As Integer i=1 Do While i < 6 Cells(i, 1).Value = 20 i=i+1 Loop Result when you click the command button on the sheet:

Explanation: as long as i is lower than 6, Excel VBA enters the value 20 into the cell at the intersection of row i and column 1 and increments i by 1. In Excel VBA (and in other programming languages), the symbol '=' means becomes. It does not mean equal. So i = i + Page | 193

1 means i becomes i + 1. In other words: take the present value of i and add 1 to it. For example, if i = 1, i becomes 1 + 1 = 2. As a result, the value 20 will be placed into column A five times (not six because Excel VBA stops when i equals 6). 2. Enter some numbers in column A.

3. Place a command button on your worksheet and add the following code lines: Dim i As Integer i=1 Do While Cells(i, 1).Value "" Cells(i, 2).Value = Cells(i, 1).Value + 10 i=i+1 Loop Result when you click the command button on the sheet:

Page | 194

Explanation: as long as Cells(i, 1).Value is not empty ( means not equal to), Excel VBA enters the value into the cell at the intersection of row i and column 2, that is 10 higher than the value in the cell at the intersection of row i and column 1. Excel VBA stops when i equals 7 because Cells(7, 1).Value is empty. This is a great way to loop through any number of rows on a worksheet.

Page | 195

4.4

Manipulate strings and Date & Time in Excel VBA.

4.4.1

Join Strings

4.4.2

Extract the leftmost and rightmost characters from a string by using Left and Right commands

4.4.3

Use the Mid, Len, Instr

4.4.4

Gets the year of a date.

4.4.5

Add a number of days to a date

4.4.6

Get the current date and time

4.4.7

Get the Hour, Minute, Second of a time

4.4.8

Use the TimeValue function to converts a string to a time serial number.

4.4.9

Compare Dates and Times

Page | 196

4.4.1 Join Strings. We use the & operator to concatenate (join) strings. Code: Dim text1 As String, text2 As String text1 = "Hi" text2 = "Tim" MsgBox text1 & " " & text2 Result:

Note: to insert a space, use " "

4.4.2 Extract the leftmost and rightmost characters from a string by using Left and Right commands.

Left To extract the leftmost characters from a string, use Left. Code: Dim text As String text = "example text"

Page | 197

MsgBox Left(text, 4) Result:

Right To extract the rightmost characters from a string, use Right. We can also directly insert text in a function. Code: MsgBox Right("example text", 2) Result:

4.4.3 Use the Mid, Len, Instr.

Mid To extract a substring, starting in the middle of a string, use Mid. Page | 198

Code: MsgBox Mid("example text", 9, 2) Result:

Note: started at position 9 (t) with length 2. You can omit the third argument if you want to extract a substring starting in the middle of a string, until the end of the string.

Len To get the length of a string, use Len. Code: MsgBox Len("example text") Result:

Note: space (position 8) included!

Instr To find the position of a substring in a string, use Instr. Page | 199

Code: MsgBox Instr("example text", "am") Result:

Note: string "am" found at position 3.

4.4.4 Gets the year of a date. Learn how to work with dates and times in Excel VBA. Place a command button on your worksheet and add the code lines below. To execute the code lines, click the command button on the sheet.

Year, Month, Day of a Date The following macro gets the year of a date. To declare a date, use the Dim statement. To initialize a date, use the DateValue function. Code: Dim exampleDate As Date exampleDate = DateValue("Jun 19, 2010") MsgBox Year(exampleDate) Result:

Page | 200

Note: Use Month and Day to get the month and day of a date.

4.4.5 Add a number of days to a date.

DateAdd To add a number of days to a date, use the DateAdd function. The DateAdd function has three arguments. Fill in "d" for the first argument to add days. Fill in 3 for the second argument to add 3 days. The third argument represents the date to which the number of days will be added. Code: Dim firstDate As Date, secondDate As Date firstDate = DateValue("Jun 19, 2010") secondDate = DateAdd("d", 3, firstDate) MsgBox secondDate Result:

Page | 201

Note: Change "d" to "m" to add a number of months to a date. Place your cursor on DateAdd in the Visual Basic Editor and click F1 for help on the other interval specifiers. Dates are in US Format. Months first, Days second. This type of format depends on your windows regional settings.

4.4.6 Get the current date and time. To get the current date and time, use the Now function. Code: MsgBox Now Result:

4.4.7 Get the Hour, Minute, Second of a time.

Hour, Minute, Second The get the hour of a time, use the Hour function. Code: MsgBox Hour(Now)

Page | 202

Result:

Note: Use Minute and Second to get the minute and second of a time.

4.4.8 Use the TimeValue function to converts a string to a time serial number.

TimeValue The TimeValue function converts a string to a time serial number. The time's serial number is a number between 0 and 1. For example, noon (halfway through the day) is represented as 0.5. Code: MsgBox TimeValue("9:20:01 am") Result:

Now, to clearly see that Excel handles times internally as numbers between 0 and 1, add the following code lines:

Page | 203

Dim y As Double y = TimeValue("09:20:01") MsgBox y Result:

4.4.9 Compare Dates and Times. This example teaches you how to compare dates and times in Excel VBA. Dates and times are stored as numbers in Excel and count the number of days since January 0, 1900. What you see depends on the number format.

1. Enter some numbers in column A.

2. These numbers are dates. This is a perfect way to enter some dates without worrying about the Date format. Change the format to Date (Right click on the column A header, Format Cells and choose Date). Result:

Page | 204

Note: Dates are in US Format. Months first, Days Second. This type of format depends on your windows regional settings. Place a command button on your worksheet and add the following code lines: 3. Declare the variable i of type Integer. Dim i As Integer 4. Add a For Next loop. For i = 1 To 5 Next i 5. The Date function returns the current date without the time. Add the following code line to the loop, to highlight all the cells containing the current date (12/22/2013). If Cells(i, 1).Value = Date Then Cells(i, 1).Font.Color = vbRed Result:

Page | 205

6. Add the following code line to the loop, to highlight all the dates earlier than 04/19/2011. If Cells(i, 1).Value < DateValue("April 19, 2011") Then Cells(i, 1).Font.Color = vbRed Result:

7. But what about times, we hear you say. They are the decimals. Switch back to General format and change the numbers to decimal numbers.

8. Now change the format to 'Date and Time' format.

Page | 206

Result:

9. If you want to highlight all cells containing the current date, we cannot use the code line at 5 anymore. Why not? Because the numbers in column A are decimal numbers now. Comparing it with Date (a whole number) would not give any match. (It would only give a match with 12/22/2013 at midnight exactly!) The following code line does work: If Int(Cells(i, 1).Value) = Date Then Cells(i, 1).Font.Color = vbRed Page | 207

Explanation: we simply use the Int function. The Int function rounds a number down to the nearest integer. This way we can get the dates without the times and compare these dates with Date. Result:

10. Add the following code line to highlight all the cells containing times in the morning. If (Cells(i, 1).Value - Int(Cells(i, 1).Value)) < 0.5 Then Cells(i, 1).Font.Color = vbRed Explanation: we only need the decimals so therefore we subtract the integer part. Noon (halfway through the day) is represented as 0.5. Decimals lower than 0.5 are the times in the morning. Result:

Page | 208

4.5

Work with Events and Array

4.5.1

Define and create Workbook Open Event

4.5.2

Define and create Workbook Change Event

4.5.3

Create a one-dimensional array

4.5.4

Create Two-dimensional Array

Page | 209

4.5.1 Define and create Workbook Open Event Events are actions performed by users which trigger Excel VBA to execute code. Code added to the Workbook Open Event will be executed by Excel VBA when you open the workbook. 1. Open the Visual Basic Editor. 2. Double click on This Workbook in the Project Explorer. 3. Choose Workbook from the left drop-down list. Choose Open from the right drop-down list.

4. Add the following code line to the Workbook Open Event: MsgBox "Good Morning" 5. Save, close and reopen the Excel file. Result:

Page | 210

4.5.2 Define and create Workbook Change Event Code added to the Worksheet Change Event will be executed by Excel VBA when you change a cell on a worksheet. 1. Open the Visual Basic Editor. 2. Double click on a sheet (for example Sheet1) in the Project Explorer. 3. Choose Worksheet from the left drop-down list. Choose Change from the right dropdown list.

Add the following code lines to the Worksheet Change Event: 4. The Worksheet Change Event listens to all changes on Sheet1. We only want Excel VBA to do something if something changes in cell B2. To achieve this, add the following code lines: If Target.Address = "$B$2" Then End If 5. We only want Excel VBA to show a MsgBox if the user enters a value greater than 80. To achieve this, add the following code line between If and End If. If Target.Value > 80 Then MsgBox "Goal Completed" Page | 211

6. On Sheet1, enter a number greater than 80 into cell B2.

Result:

4.5.3 Create a one-dimensional array.

Array An array is a group of variables. In Excel VBA, you can refer to a specific variable (element) of an array by using the array name and the index number.

One-dimensional Array To create a one-dimensional array, execute the following steps. Place a command button on your worksheet and add the following code lines: Dim Films(1 To 5) As String Films(1) = "Lord of the Rings" Films(2) = "Speed" Films(3) = "Star Wars"

Page | 212

Films(4) = "The Godfather" Films(5) = "Pulp Fiction" MsgBox Films(4) Result when you click the command button on the sheet:

Explanation: the first code line declares a String array with name Films. The array consists of five elements. Next, we initialize each element of the array. Finally, we display the fourth element using a MsgBox.

4.5.4 Create Two-dimensional Array. To create a two-dimensional array, execute the following steps. This time we are going to read the names from the sheet.

Place a command button on your worksheet and add the following code lines:

Page | 213

Dim Films(1 To 5, 1 To 2) As String Dim i As Integer, j As Integer For i = 1 To 5 For j = 1 To 2 Films(i, j) = Cells(i, j).Value Next j Next i MsgBox Films(4, 2) Result when you click the command button on the sheet:

Explanation: the first code line declares a String array with name Films. The array has two dimensions. It consists of 5 rows and 2 columns. Tip: rows go first, then columns. The other two variables of type Integer are used for the Double Loop to initialize each element of the array. Finally, we display the element at the intersection of row 4 and column 2.

Page | 214

4.6

Create Functions ,Sub and Application object

4.6.1

Differentiate between a function and a sub

4.6.2

Create Function

4.6.3

Create Sub

4.6.4

Define the use of the Application object

4.6.5

Use the WorksheetFunction property in to access Excel functions.

4.6.6

Disable screen updating

4.6.7

Display alerts while executing code.

4.6.8

Turn On and Off the automatic calculation option

Page | 215

4.6.1 Differentiate between a function and a sub. The difference between a function and a sub in Excel VBA is that a function can return a value while a sub cannot. Functions and subs become very useful as program size increases.

4.6.2 Create Function. If you want Excel VBA to perform a task that returns a result, you can use a function. Place a function into a module (In the Visual Basic Editor, click Insert, Module). For example, the function with name Area. Function Area(x As Double, y As Double) As Double Area = x * y End Function Explanation: This function has two arguments (of type Double) and a return type (the part after As also of type Double). You can use the name of the function (Area) in your code to indicate which result you want to return (here x * y). You can now refer to this function (in other words call the function) from somewhere else in your code by simply using the name of the function and giving a value for each argument. Place a command button on your worksheet and add the following code lines: Dim z As Double z = Area(3, 5) + 2 MsgBox z Explanation: The function returns a value so you have to 'catch' this value in your code. You can use another variable (z) for this. Next, you can add another value to this variable (if you want). Finally, display the value using a MsgBox. Page | 216

Result when you click the command button on the sheet:

4.6.3 Create Sub. If you want Excel VBA to perform some actions, you can use a sub. Place a sub into a module (In the Visual Basic Editor, click Insert, Module). For example, the sub with name Area. Sub Area(x As Double, y As Double) MsgBox x * y End Sub Explanation: This sub has two arguments (of type Double). It does not have a return type! You can refer to this sub (call the sub) from somewhere else in your code by simply using the name of the sub and giving a value for each argument. Place a command button on your worksheet and add the following code line: Area 3, 5 Result when you click the command button on the sheet:

Page | 217

Can you see the difference between the function and the sub? The function returned the value 15. We added the value 2 to this result and displayed the final result. When we called the sub we had no more control over the result (15) because a sub cannot return a value!

4.6.4 Define the use of the Application object The mother of all objects is Excel itself. We call it the Application object. The application object gives access to a lot of Excel related options

4.6.5 Use the WorksheetFunction property in to access Excel functions. You can use the WorksheetFunction property in Excel VBA to access Excel functions. 1. For example, place a command button on your worksheet and add the following code line: Range("A3").Value = Application.WorksheetFunction.Average(Range("A1:A2")) When you click the command button on the worksheet, Excel VBA calculates the average of the values in cell A1 and cell A2 and places the result into cell A3.

Note: instead of Application.WorksheetFunction.Average, simply use WorksheetFunction.Average. If you look at the formula bar, you can see that the formula itself is not inserted into cell A3. To insert the formula itself into cell A3, use the following code line:

Page | 218

Range("A3").Value = "=AVERAGE(A1:A2)"

4.6.6 Disable screen updating Sometimes you may find it useful to disable screen updating (to avoid flickering) while executing code. As a result, your code will run faster. 1. For example, place a command button on your worksheet and add the following code lines: Dim i As Integer For i = 1 To 10000 Range("A1").Value = i Next i When you click the command button on the worksheet, Excel VBA displays each value a tiny fraction of a second and this can take some time.

2. To speed up the process, update the code as follows. Dim i As Integer Application.ScreenUpdating = False For i = 1 To 10000 Range("A1").Value = i Next i

Page | 219

Application.ScreenUpdating = True As a result, your code will run much faster and you will only see the end result (10000).

4.6.7 Display alerts while executing code. You can instruct Excel VBA not to display alerts while executing code. 1. For example, place a command button on your worksheet and add the following code line: ActiveWorkbook.Close When you click the command button on the worksheet, Excel VBA closes your Excel file and asks you to save the changes you made.

2. To instruct Excel VBA not to display this alert while executing code, update the code as follows. Application.DisplayAlerts = False ActiveWorkbook.Close Application.DisplayAlerts = True As a result, Excel VBA closes your Excel file, without asking you to save the changes you made. Any changes are lost.

Page | 220

4.6.8 Turn On and Off the automatic calculation option By default, calculation is set to automatic. As a result, Excel recalculates the workbook automatically each time a value affecting a formula changes. If your workbook contains many complex formulas, you can speed up your macro by setting calculation to manual. 1. For example, place a command button on your worksheet and add the following code line: Application.Calculation = xlCalculationManual When you click the command button on the worksheet, Excel VBA sets calculation to manual. 2. You can verify this by clicking on File, Options, Formulas.

Page | 221

3. Now when you change the value of cell A1, the value of cell B1 is not recalculated.

You can manually recalculate the workbook by pressing F9. 4. In most situations, you will set calculation to automatic again at the end of your code. Simply add the following code line to achieve this. Application.Calculation = xlCalculationAutomatic

Page | 222

4.7

Create and use Active X control and UserForm

4.7.1

Create ActiveX controls

4.7.2

Create an Excel VBA Userform.

4.7.3

Add the Controls to the Userform

4.7.4

Show the Userform

4.7.5

Assign the Macros

4.7.6

Test the Userform

Page | 223

4.7.1 Create ActiveX controls. Learn how to create ActiveX controls such as command buttons, text boxes, list boxes etc. To create an ActiveX control in Excel VBA, execute the following steps. 1. On the Developer tab, click Insert. 2. For example, in the ActiveX Controls group, click Command Button to insert a command button control.

3. Drag a command button on your worksheet. 4. Right click the command button (make sure Design Mode is selected). 5. Click View Code.

Page | 224

Note: you can change the caption and name of a control by right clicking on the control (make sure Design Mode is selected) and then clicking on Properties. Change the caption of the command button to 'Apply Blue Text Color'. For now, we will leave CommandButton1 as the name of the command button. The Visual Basic Editor appears. 6. Add the code line shown below between Private Sub CommandButton1_Click() and End Sub.

Page | 225

7. Select the range B2:B4 and click the command button (make sure Design Mode is deselected). Result:

Page | 226

4.7.2 Create an Excel VBA Userform. This chapter teaches you how to create an Excel VBA Userform. The Userform we are going to create looks as follows:

4.7.3 Add the Controls to the Userform To add the controls to the Userform, execute the following steps. 1. Open the Visual Basic Editor. If the Project Explorer is not visible, click View, Project Explorer.

Page | 227

2. Click Insert, Userform. If the Toolbox does not appear automatically, click View, Toolbox. Your screen should be set up as below.

3. Add the controls listed in the table below. Once this has been completed, the result should be consistent with the picture of the Userform shown earlier. For example, create a text box control by clicking on TextBox from the Toolbox. Next, you can drag a text box on the Userform. When you arrive at the Car frame, remember to draw this frame first before you place the two option buttons in it. 4. Change the names and captions of the controls according to the table below. Names are used in the Excel VBA code. Captions are those that appear on your screen. It is good practice to change the names of controls. This will make your code easier to read. To change the names and captions of the controls, click View, Properties Window and click on each control.

Page | 228

Control

Name

Caption

Userform

DinnerPlannerUserForm Dinner Planner

Text Box

NameTextBox

Text Box

PhoneTextBox

List Box

CityListBox

Combo Box

DinnerComboBox

Check Box

DateCheckBox1

June 13th

Check Box

DateCheckBox2

June 20th

Check Box

DateCheckBox3

June 27th

Frame

CarFrame

Car

Option Button

CarOptionButton1

Yes

Option Button

CarOptionButton2

No

Text Box

MoneyTextBox

Spin Button

MoneySpinButton

Command Button OKButton

OK

Command Button ClearButton

Clear

Command Button CancelButton

Cancel

7 Labels

Name:, Phone Number:, etc.

No need to change

Note: a combo box is a drop-down list from where a user can select an item or fill in his/her own choice. Only one of the option buttons can be selected.

Page | 229

4.7.4 Show the Userform To show the Userform, place a command button on your worksheet and add the following code line: Private Sub CommandButton1_Click() DinnerPlannerUserForm.Show End Sub We are now going to create the Sub UserForm_Initialize. When you use the Show method for the Userform, this sub will automatically be executed. 1. Open the Visual Basic Editor. 2. In the Project Explorer, right click on DinnerPlannerUserForm and then click View Code. 3. Choose Userform from the left drop-down list. Choose Initialize from the right dropdown list. 4. Add the following code lines: Private Sub UserForm_Initialize() 'Empty NameTextBox NameTextBox.Value = "" 'Empty PhoneTextBox PhoneTextBox.Value = "" 'Empty CityListBox CityListBox.Clear 'Fill CityListBox With CityListBox Page | 230

.AddItem "San Francisco" .AddItem "Oakland" .AddItem "Richmond" End With 'Empty DinnerComboBox DinnerComboBox.Clear 'Fill DinnerComboBox With DinnerComboBox .AddItem "Italian" .AddItem "Chinese" .AddItem "Frites and Meat" End With 'Uncheck DataCheckBoxes DateCheckBox1.Value = False DateCheckBox2.Value = False DateCheckBox3.Value = False 'Set no car as default CarOptionButton2.Value = True 'Empty MoneyTextBox MoneyTextBox.Value = "" 'Set Focus on NameTextBox NameTextBox.SetFocus End Sub

Page | 231

Explanation: text boxes are emptied, list boxes and combo boxes are filled, check boxes are unchecked, etc.

4.7.5 Assign the Macros We have now created the first part of the Userform. Although it looks neat already, nothing will happen yet when we click the command buttons on the Userform. 1. Open the Visual Basic Editor. 2. In the Project Explorer, double click on DinnerPlannerUserForm. 3. Double click on the Money spin button. 4. Add the following code line: Private Sub MoneySpinButton_Change() MoneyTextBox.Text = MoneySpinButton.Value End Sub Explanation: this code line updates the text box when you use the spin button. 5. Double click on the OK button. 6. Add the following code lines: Private Sub OKButton_Click() Dim emptyRow As Long 'Make Sheet1 active Sheet1.Activate 'Determine emptyRow Page | 232

emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1 'Transfer information Cells(emptyRow, 1).Value = NameTextBox.Value Cells(emptyRow, 2).Value = PhoneTextBox.Value Cells(emptyRow, 3).Value = CityListBox.Value Cells(emptyRow, 4).Value = DinnerComboBox.Value If DateCheckBox1.Value = True Then Cells(emptyRow, 5).Value = DateCheckBox1.Caption If DateCheckBox2.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & " " & DateCheckBox2.Caption If DateCheckBox3.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & " " & DateCheckBox3.Caption If CarOptionButton1.Value = True Then Cells(emptyRow, 6).Value = "Yes" Else Cells(emptyRow, 6).Value = "No" End If Cells(emptyRow, 7).Value = MoneyTextBox.Value End Sub Explanation: first, we activate Sheet1. Next, we determine emptyRow. The variable emptyRow is the first empty row and increases every time a record is added. Finally, we transfer the information from the Userform to the specific columns of emptyRow. 7. Double click on the Clear button.

Page | 233

8. Add the following code line: Private Sub ClearButton_Click() Call UserForm_Initialize End Sub Explanation: this code line calls the Sub UserForm_Initialize when you click on the Clear button. 9. Double click on the Cancel Button. 10. Add the following code line: Private Sub CancelButton_Click() Unload Me End Sub Explanation: this code line closes the Userform when you click on the Cancel button.

4.7.6 Test the Userform Exit the Visual Basic Editor, enter the labels shown below into row 1 and test the Userform. Result:

Page | 234