New Skills Academy-Basic Course in Excel PDF

Preview

Summary

A lecture on excel basic techniques....

Description

MS Excel Basic Course Module 2-To add cell:

= SUM (highlight row) Alt + = =Sum (Row,Row,Row) for multiple

rows/column Module 3-To multiply rows: = PRODUCT = Cell*Cell

Module 4- To subtract rows: = Cell-Cell Module 5- To divide rows:

= Cell/Cell

Module 6- Sorting data Some examples of the uses of this function are if you want to organize names in alphabetical order, to organize prices from highest to lowest, or rows by colors or icons. To sort data by text, select a column of the data with text, or make sure the active cell is in a table column with data with text that you want sorted. Click the Data tab, and find the Sort & Filter group. Click on the button desired to either organize from A to Z (A→Z) or Z to A (Z→A). To sort numbers, same as above, select a column of the data, or make sure the active cell in a table is a column with the numbers you want sorted. In the Data tab, in the Sort & Filter group, click on the A → Z button to organize from smallest to largest. Click on the Z→A button to organize from largest to smallest.

To sort dates or times, the A→Z button will sort from the oldest date or time to the most recent date or time. The Z→A button will sort from the most recent date or time to the oldest date or time.

Module 7- Percentage Excel allows you to turn a value you know represents part of a whole into percentage form. To display a piece of data in percentage format, (e.g. 10%), that is in decimal format, simply click on the cell, column, or row where you want the data to show up in percentage format. Go to the Home tab and the number group, and click on the % sign. The data should show up in the form of a percentage. Module 8- Running Totals [$,:] The running total, also called a running count, allows you to watch the number of items in a table add up as you continue to enter new items. To use the Running Total function, your table or worksheet should have at least, in general, a column with a description (like the description of a transaction), a column with numerical data to which you want to apply the running total (like deposits and withdrawals or product value), and the destination column, where the running total output information will go (which could be a total balance or an inventory value total). To explain the process, let us take an example: the total number of items in an inventory. First, click the first blank cell in the column where you want the running total.

Type the following formula: =SUM(First value cell:Current value cell). For example, =SUM($C$2:$C2). The $ means that the value will not shift once you copy the formula; it will always refer to that row or column. Copy the formula into all cells of the running total column, and it will automatically calculate the running total of items in inventory. If you do not want to manually type in the dollar signs, you can click on the relevant part of the formula and press F4. Each time you press F4 it will change where the dollar signs are placed, eventually going back to the original formula with no dollar signs.

Module 9- Print Excel allows you to print entire workbooks, worksheets, partial worksheets, or specific tables. Before you print, it is highly recommended that you view your worksheet using a Page Layout view, which will show exactly where the worksheet ends. For this, you simply go to the View tab, and select page layout, or click on the quick access button on the bottom of the page. To print a partial worksheet, click on the worksheet you want to print and select the range of data you want to print. To print an entire worksheet, click on the worksheets (any cell) to activate it. To print a workbook, it does not matter what worksheet you have activated. Then, click File, then Print, or press CTRL+P. In settings, you can choose to print the selection, the active sheet or sheets, or the entire workbook. Finally, click print.

Module 10- Paste Special Sometimes, in addition to copying and pasting data, you want to paste the format, column width or others into the sheet.

To do this, once you have copied the data or formatting that you want to paste, click on the cell or cells you want to paste it in, and right click. Click Paste Special under Paste Options, and choose whether you want to paste only values, only formatting, or others.

Once you click, the data or formatting you want will paste into the area.

Module 11- Merge Because Excel is composed of a series of cells to make tables, sometimes, you want to merge cells to make one large cell, and make it appear like the cells below it have been split. It is important to note that cells cannot be split, but you can use the merge function to make it appear like cells above and below, or the left and right of, the merged cells have been split. When you merge cells, the cells become larger, and are displayed across multiple row or columns. To merge cells, select two or more adjacent cells you want to merge. One of the cells can have text, but if it does, the others cannot have text.

After you have selected the adjacent cells you want to merge, find the Alignment group in the Home tab and click Merge & Center. This will automatically merge your cells. If you want to unmerge cells after merging them, the Merge button in the Alignment group of the Home tab will now have an Unmerge Cells option, which will bring them back to normal size. Module 12- Filter You can filter data in your table when you want to find a subset of data in a specified range. This allows you to work with specific data in the table. Filtered data will only display the rows that meet criteria you have specified. Once you have filtered data, you can copy, edit, print and format the subset of data you created. The most common way to filter is by using the AutoFilter function. With this function, there are three types of filters. You can filter by list values, by a format or by specified criteria.

To use the Filter function, first click on a range of cells with data that you want filtered. Then, go to the Data tab and look for the Sort & Filter group and click on the Filter icon. An arrow should appear in the column header, and when you click on the arrow, you can specify the criteria you want to use to filter the data. You can filter by dates or times, numbers, by top or bottom numbers, by blanks or non-blanks, by text, by cell color, by font color, by icon set, or by selection. Note that if you want to filter by more than one criteria, you can select And, and if you want to filter the column or selection with either or both criteria, you can select Or.

To clear a filter, on the Data tab, in the Sort & Filter group, click Clear.

Module 13- Average The formula to calculate the average is: =AVERAGE(range) OR =Average (number1,number2,number3) Keep in mind that if a range or cell reference has text, logical values, or empty cells, they will be ignored, but cells with the value “0” will be included in the calculation. Module 14- AVERAGEIF The AVERAGEIF function is similar to the AVERAGE function in that it calculates the arithmetic mean of a set of values, only of values that meet certain criteria. For example, you can specify that it should only take into account values greater than 30 or 100. The formula for the AVERAGEIF function is: =AVERAGEIF(range, criteria, [average_range]) The range should refer to the range of cells you want to be included in the calculation. The criteria should be in parentheses. The average_range part is optional, though here you could specify even further what cells to take into account. Module 15- SUMIF Formula

The SUMIF function is used to add up the values in a range that meet the criteria specified. For example, if you want Excel to only add up values greater than a certain number, you can do that with the SUMIF function. The formula for the SUMIF function is as follows: =SUMIF(range, criteria) Range: you must input the range of cells you want to be taken into account for the function. Criteria: The criteria could be in the form of a number, text, an expression, or a particular cell reference. This defines which specific cells will be added. You can use a question mark (?) or an asterisk (*) for the criteria. The question mark matches any single character in the specified range (number of letters, e.g. s????????), and an asterisk matches any sequence of characters in the specified range (starts with letter, x*). is used to exclude values, names, or letters. Module 16- IF Formula The IF function allows you to make logical comparisons between an actual value in a worksheet, and what you expect. The result of the comparison can be TRUE or FALSE. The function can carry out different actions depending on whether something is true or false. It looks something like this: =IF(Something is TRUE, then do something, if not do something else) The IF function can be used to evaluate text and values, and it can be used to evaluate errors.

One example of when you would want to apply the IF function is if you want Excel to determine whether a value is within budget or over budget. In one column (B) you can note your budget, and in the next column (C) what you actually spent. In the third column, you can use the IF function to determine automatically if an amount is within budget or not, using a formula like this:=IF(B6>C6,”Over budget”,”Within budget”) The formula is asking Excel to determine whether the actual amount spent is greater than the amount budgeted, and if it is, the result cell should say “Over budget”. Otherwise, it should say “Within budget.” 17.1 VLOOKUP in Excel The VLOOKUP function is one of the reference and lookup functions in Excel, and it helps to you find specific data in a table or in a specific range by row of data. For example, the VLOOKUP function is useful if you want to look up an inventory item by inventory number. The way to input the function is the following:=VLOOKUP(value, table, col_index, [range_lookup]). Value: the value to look for Table: the table from which to retrieve a value Col_index: The column in the table from which to retrieve a value Range_lookup: TRUE= approximate match, FALSE= exact match. This part is optional. If you do not use it, it will allow a non-exact match but it will use the exact match if there is one. This function applies to Excel 2007 to Excel 2016 for PC, and Excel 2011 to Excel 2016 for Mac, and most mobile versions.

18.1 HLOOKUP in Excel The HLOOKUP function is similar to the VLOOKUP function, but it looks for values horizontally (thus, the H), rather than vertically. The HLOOKUP function allows you to search for a specific value in the top row of a table or an array of values, and the resulting value will be in the same column from a row you specify in the table. You use HLOOKUP when you want to compare values that are found in a row across the top of a table of data, and down a specific number of rows. The formula for the HLOOKUP Function is:HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) Where, Lookup_value: refers to the value you want to find in the first row of the table. It can be a value or a text string. Table_array: The table where you want the data looked up. These can be text, numbers, or values. Row_index_num: This is the row number in the table being referenced where the matching value will be returned. Range_lookup: This is optional, but it will allow you to specify whether you want an exact match or an approximate match. TRUE will allow approximate matches, whereas FALSE will look up an exact match. 19.1 Countif Formula The Countif function automatically counts the number of cells that meet the criteria you have specified.

You can use it to count the number of times a particular brand shows up in a list of items specified by brand, for example. The Countif formula is the following:=COUNTIF(range, criteria) For example, if you want to look for how many times the name “Mary” shows up in a table, it might look something like this: =COUNTIF(A2:A20,”Mary”) You use quotation marks around the criteria you are searching for when you put it in manually. However, if you have Mary typed into a cell, or a value for that matter, you can just use a cell reference instead of typing it in manually. 20.1 CountBlank The CountBlank Function counts the number of empty cells in a specified range. If you are putting together a database of information, for example, the CountBlank Function could help you determine how much information still needs to be filled out. The formula for the function is: =COUNTBLANK(range) This function counts cells that return empty formulas, and those not yet filled out. However, cells with a zero value are counted. Note that you need to turn off the iterative function for this formula to run. To make sure you have done that, click FILE, then Options, then Formula, and under Calculation options, make sure the Enable iterative calculations box is checked off. 21.1 Counta Formula The Counta formula is the opposite of the CountBlank formula. It counts the number of cells that are not empty in a given range.

The formula is: COUNTA(value1, [value 2]) There are two values so that you can find the number of cells in two separate sets of data. It can process any type of data, including text. 22.1 Count Formula The count function simply counts the number of cells that contain numbers within the list of arguments. The formula is:=COUNT(range) For this function, it doesn’t matter what sort of data is contained in the cell; arguments include numbers, dates, and others. 23.1 Concatenate The Concatenate function is one of the text functions that Excel has, which joins two or more text strings into one string. It can make full sentences by joining data, or join first and last names contained in different columns. The formula is:=CONCATENATE(text1, [text2]…) The first text reference or input is required, though from the second one forward, they are optional for a maximum of up to 255 items. 24.1 Transpose You can use the Transpose function to switch or rotate cells. You can do this by copying, pasting and using the Transpose Option, but be aware that this will create duplicated/static data i.e. any later changes to data in your original columns will not be pulled through to the transposed data. (This is the method demonstrated in the video below).

Alternatively, you can type in a formula using the Transpose Function which will also switch the arrangement of cells from vertically to horizontally, but is dynamic and will capture changes to your original data. To use the transpose function, select the same number of blank cells as the original data. Then, type: =TRANSPOSE(range of cells to be transposed) Finally, press CTRL+SHIFT+ENTER. Without this, the function will not work....