exel

Overview of Excel Skills

Topics Covered

· Chapter 1

· Data Entry

· Name and Group Worksheets

· Formatting

· Sorting and Filtering

· Page Layout

· Forms

· Chapter 2

· Data Validation

· Formulas

· Conditional Formatting

· Defining a Name

· Chapter 3

· Pivot Tables

· Pivot Charts

· Chapter 4

· VLOOKUP

· IF Function

· Create a Source Document with controls

· Spreadsheet Protection

Open Excel, double-click on the Blank workbook option found in the top-left corner of the right pane. On the Menu Bar, click File > Save As and save your file as xxx_Excel.xlsx (where xxx represents your first initial and last name. For example, MSargent_Excel.xlsx). This file containing the output below will be submitted via BB.

Note – the screenshots are simply a visual to help you, they are not meant to be a perfect match to your work

Chapter 1: Data entry, name and group worksheets, formatting, sorting and filtering, page layout, and forms

Application Question: How might a manager track inventory quantities on hand?

We will begin by creating two worksheets, each containing one inventory list.

Data Entry, Naming, and Grouping Worksheets

· On Sheet1, create a Candy Bar Inventory table.

· Highlight the Candy Bar Inventory table below by clicking on the upper left corner. Note: Do not highlight the title “Candy Inventory”.

· Copy the information by “right-clicking” and selecting copy OR holding down the CTRL key and the “C” key simultaneously.

· Paste the information onto Sheet1 by placing your cursor in cell A1 and “right-clicking” and selecting paste OR holding down the CTRL key and the “V” key simultaneously.

· Create a new sheet by clicking the icon to the right of the Sheet1 tab.

· On the new Sheet2, use the same process to create a Popcorn Inventory table.

· Name Sheet1 Candy by double-clicking on the tab labeled Sheet1 and typing Candy. Note: you may also change the tab color by right clicking on a tab and selecting tab color.

· Using the same process, name Sheet2 Popcorn.

Candy Inventory

Item Code	Wholesaler	Item Number	Item Description	Quantity on Hand	Cost	Price
10	Candy Inc	501	Almond Joy	940	.45	1.35
10	Sweets Co	502	Nestle Crunch	1300	.65	1.45
10	CheapCandy	503	Reese’s Cups	1175	.70	1.55
10	Sweets Co	504	Kit Kat	650	.80	1.70

Popcorn Inventory

Item Code	Wholesaler	Item Number	Item Description	Quantity on Hand	Cost	Price
20	Sweets Co	601	Plain	675	.70	1.45
20	Candy Inc	602	Buttered	820	.90	1.60
20	Candy Inc	603	Carmel	1225	1.10	2.05
20	Candy Inc	604	Crunch	915	1.30	2.15

· Group the worksheets by holding the CTRL key and left-clicking on the two worksheet tabs, or right-click on one tab and click on “select all sheets”. You will know that the sheets are grouped because the tab colors are highlighted.

· Note: grouping allows you to make simultaneous changes to all selected sheets to make formatting more efficient and consistent. Be aware that formatting only applies to identical ranges, if one list is longer than the other, formatting will not extend to the extra rows/columns.

Formatting

· Format the data within the worksheets:

· Center text and numbers. Highlight the cells that you want to center, click on the Home tab, select the centering icon listed in the Alignment section of the Home ribbon.

· Widen columns. Highlight the desired area, double-click on the right side of the column header OR drag the right hand border of each column until you are satisfied.

· Format numbers as currency. Highlight the cost and price columns, right-click the highlighted area and select Format Cells from the pop-up menu. On the Number tab, Select the following options: Currency Category, 2 Decimal places and $ Symbol.

· Ungroup worksheets – right-click on either worksheet tab, and select Ungroup Sheets.

· Format each inventory list as a table. Highlight the entire table (including the Header row), on the Home tab, in the Styles section, select Format as Table, select a table style. Note: be sure My table has headers is checked the in the Format As Table pop-up window.

· Note that drop-down arrows appear next to each header row. These enable you to sort and filter the data.

· You may want to group your worksheets and adjust your column widths to accommodate the drop-down arrows.

· Your workbook should appear similar (as you chose your own table style) to the screenshot below.

Sorting and Filtering

· Click on the Candy inventory sheet.

· Using the drop down arrows, use the sort and filter functions to:

· Alphabetical Sort. Sort by Item Description, ascending. Select the arrow next to the item description column heading, click on sort, A to Z.

· Numerical Filter. Show only items that have a Cost between .40 and .75. Select the arrow next to the cost column heading, click on Number Filters, choose Between, enter .40 AND .75.

· Additional numerical filter. Display only items with a price of 1.35. Using drop down arrow, uncheck (Select All), and check the box next to $1.35. Your worksheet should be similar to the screenshot below:

· Display all items by turning off filtering. In the Home tab, Editing section, locate the sort and filter icon, in the drop-down list, click Clear.

· Note, Clear removes all filtering criteria, but leaves the drop-down arrows visible. To remove the drop-down arrows completely, in the Home tab, Editing section, locate the sort and filter icon, in the drop-down list, click the Filter icon.

· Note: you may also use the sort and filter icon in the Editing section of the Home tab to apply sorting and filtering.

Page Layout

· Change page layout to landscape. Group the worksheets and click the Page Layout tab. Click the Orientation and select Landscape. Note you may also make other printing decisions here, such as setting margins and hiding gridlines.

· Ungroup your worksheets.

Forms

Application Question: Is there a tool to improve data entry efficiency?

· Add the Form command to the main toolbar. Click the Customize Quick Access Toolbar down arrow and select More Commands (see screenshot below).

· Select All Commands in the Choose commands from: drop-down list. Scroll down to Form… and click on Form to highlight it, Click the button and click OK (see the screen shot on the next page) now the Form icon should be visible in the top toolbar (see screenshot below).

Note: The form command allows you to enter data in a form view, rather than a spreadsheet view.

· With your cursor in the candy table, click the Form icon now located in the Quick Access Toolbar. In the form dialog box, click New and add the items from the table below (next page) using the form.

Item Code	Wholesaler	Item Number	Item Description	Quantity on Hand	Cost	Price
10	Cheapcandy	505	Twix	800	.45	1.15
10	Sweets Co	506	Milk Duds	550	.55	1.30
10	Candy Inc	507	Heath	450	.65	1.40
10	Candy Inc	508	Baby Ruth	900	.85	1.60

· Notice that the items appear in your worksheet as soon as click the New button.

· Click Close when you have entered all the new items.

Application Question: Can Forms be used to search spreadsheets for particular data?

· Say you want to locate Twix candy. Open the Form and click Criteria.

· Type Twix in the Item Description field.

· Click, Find Next.

· The Twix record will appear in the form.

· Search for other items by quantity on hand, cost, and price.

Chapter 2: Data validation, formulas, conditional formatting, and defining names

Application question: What tools can designers use to make sure that the data entered is reliable (error-free)? Consider different ways to achieve this goal.

Data Validation

· Open the Candy inventory worksheet.

· Create a data validation rule to ensure that the item number has only three digits.

· Highlight the Item Number column.

· Select Data tab. In the Data Tools section, click the Data Validation icon.

· Enter the following information in each tab (see screenshots below):

· 1. Settings. Under Validation criteria: select Text length from the Allow drop-down list, select equal to from the Data drop-down list, and select 3 from the Length drop-down list.

· 2. Input message. Make sure the Show input message when cell is selected option is checked. In the Title textbox, input Item Number and in the Input message textbox type Item Number must be 3 digits.

· 3. Error alert. Make sure the Show error alert after invalid data is entered option is checked. Select Stop from the Style drop-down list. Input Item Number Error in the Title textbox. Type in Item Number must be exactly 3 digits in the Error Message textbox.

· Click OK.

· Test the above rule by trying to enter an item number with more or less than 3 digits.

· As you have noticed, the input message that appears when an Item Number cell is selected is annoying so remove the input message.

· Add the following rules. Adjust settings appropriately and provide an error alert. Note: Do not provide an input message.

· Quantity on hand must be between 0 and 1500. The Error Alert Style is Information .

· Cost must be positive. The Error Alert Style is Warning .

· Price must be greater than 1.00. The Error Alert Style is Stop .

· Test the rules to see if they provide the desired controls.

Formulas and Conditional Formatting

Application questions: How can you design a spreadsheet to help the manager determine which candies are the most profitable per unit? What Excel feature could you use to alert the manager when quantities drop below a reorder point?

· Create a new sheet, then copy and paste the tables from each worksheet to a new worksheet to form a single table. Note: Before you past the table, you might want to right-click in cell A1 in the new sheet, select Paste Special and then select Column widths from the Paste Special pop-up box. Copy the header rows from only one table. Name this new sheet Total Inventory .

· Create additional columns to display:

· Gross Profit (Price minus Cost)

· Gross Margin % (Gross Profit divided by Price)

· Total Cost (Quantity * Cost)

· Total Price (Quantity * Price)

Note: You may want to select the new columns, right-click anywhere in the highlighted area and select Format Cells from the pop-up menu and select the Alignment tab and check the Wrap text Text control if not already checked. Adjust column-widths as needed.

· Enter appropriate formulas. Note: The formulas will “copy down” to all rows as long as the data is still in a table format. Also, the formulas will look a little different because of the table format.

· Format as appropriate. (Percentage with no decimal places for gross margin and currency for the other items).

· Add formulas to the bottom row to display “Total quantity on hand” by entering an AutoSum function to the entire row.

· Place the cursor in the first empty cell under the quantity on hand column.

· Select the Formulas tab, and click the AutoSum icon found on the left side of the ribbon.

· Experiment with formula options as follows:

· Total: quantity on Hand, cost, price

· Average: Cost, price, gross profit, gross margin %

· Experiment with other Formula options: Count, Min, Max.

· Delete the bottom row of the table (the one with the formulas) since it is not needed for the next Chapter. Select the row number, right-click, select Delete.

Conditional Formatting

· Apply conditional formatting to alert management when items fall below minimum levels.

· Highlight the quantity on hand column, select Home tab,

· In the Styles section, select Conditional Formatting.

· Choose New Rule from the pop-up menu.

· In the New Formatting Rule window, select Format cells based on their values from the Select a Rule Type: section.

· In the Edit the Rule Description: section, select Icon Sets from the Format Style drop-down list.

· Select the icon set you want from the Icon Style drop-down list.

· In the Display each icon according to these rules section, set the green icon to >=, Type Number, Value 1200. For the yellow icon: >=, Type Number, Value 900. The red icon will change to <900 automatically. Note: Change Type , before you enter the Value .

· Click OK.

· Apply conditional formatting to alert management when gross profit falls below .75 per item by formatting those items in red text.

· Highlight the Gross Profit column and select Conditional Formatting from the Home tab Styles section. Select Highlight Cells Rules and then select the Less Than option. Enter .75 in the Format cells that are LESS THAN textbox and select Red Text from the with drop-down list.

· Remove the conditional formatting rules. To remove conditional formatting Click on Conditional Formatting in the Home ribbon, select Clear Rules and then select the Clear Rules from Entire Sheet option.

Adding a Title

· Add a title to the table. Add a row by highlighting row 1, right-click and select Insert from the pop-up menu.

· Type Total Inventory in cell A1.

· Highlight from cell A1 to cell K1.

· Merge and center the title. On the Home ribbon in the Alignment section, click the Merge and Center icon .

· Increase the font size and bold the title using functions in Home tab (see the screenshot on the next page. Keep in mind that your table style is likely different from the one in these instructions).

Defining a Name

· Select the Candy worksheet. Click on the Formulas tab and then click cell A2. Notice that A2 shows in the Name Box right above the A column in your worksheet. Highlight the entire table; cells A2:G9. The contents in the Name Box will change to Table# as shown in the screenshot below. Note that your table name may be different from these instructions.

· Click the Name Manager icon in the Defined Names section of the Formulas ribbon. Select your Table# from the list of Names in the Name Manager pop-up box and click the Edit icon (see screenshot below). Change the name to Candy and click OK.

· If you click on the down-arrow in the Name Box you will see that you now have Candy and two other table names.

· Change the name of the remaining tables to Popcorn and Total_Inventory.

· Close the Name Manager window.

Chapter 3: Pivot Tables and Pivot Charts

Application question: How can you create a visual display that neatly summarizes data into useful information for managerial decision-making?

Pivot Tables

· Create a pivot table to summarize average cost by inventory code (candy and popcorn) and wholesaler.

· Make sure the Total Inventory worksheet is your active worksheet by clicking on the Total Inventory tab then select the Total_Inventory table from the Name Box. Select the Insert tab.

· Click on the Pivot Table icon the Tables section of the Insert ribbon. In the Create PivotTable window, the Table/Range should be showing Total_Inventory. If not, select the table.

· In the "Choose where you want the PivotTable report to be placed" section of the Create PivotTable window, select the Existing Worksheet option and click in the Location: textbox and then on an empty cell below the data table. Your worksheet should appear similar to the screenshot below.

· Click OK to continue.

· The pivot table field list should now appear. See the screenshot on the next page.

· Create a pivot table that groups by Item Code (drag and drop Item Code to the Rows box in the bottom right section of your screen) and Wholesaler (drag and drop Wholesaler to Columns). Note: drag fields down to appropriate boxes rather than selecting them.

· Select Total Cost, and it will appear in the Values box. Use the drop-down arrow to the right of Sum of Total … and select Value Field Settings… from the pop-up menu. Select Average in the Summarize value field by section. Click the Number Format button at the bottom of the window, select Currency and click OK to continue. Note: you can also click on the Sum of Total Cost cell in the Pivot Table and select the Field Settings icon in the Active Field section of the Analyze ribbon to change the Summarize value to Average.

· Click Ok to close the Value Field Settings window.

· Note: You may “drill down” by clicking on any value within the Pivot Table. Excel links the data in the table to the data in the other sheets within the workbook.

Pivot Charts

· Insert a pivot chart to show a visual representation of gross profit for each item, grouped by item code.

· Place the cursor on a data cell in the Total_Inventory table. Select Insert tab and click on the PivotChart icon the in the Charts section of the Insert ribbon. In the Create PivotChart window, your table should be selected

· In the "Choose where you want the PivotChart to be placed" section, New Worksheet should be selected by default (if not, select New Worksheet). Click OK to continue.

· The Analyze ribbon should be selected. In the PivotChart section of the ribbon, Chart Name click the text box and name the new worksheet, Gross Profit by Product.

· In the PivotChart Fields pane, drag and drop Item Code in the Axis (Categories) box, drag and drop Item Description in the Legend (Series), and select the Gross Profit checkbox. Sum of Gross Profit will appear in the Values section.

· Click on the Chart and move it so its placement is roughly as shown in the screenshot on the next page.

· Rename the sheet to Gross Profit by Product.

· Click on the Format tab under the PivotChart Tools at the top of the Excel window (see screenshot on next page).

· You can use the mouse to hover the cursor over the various sections of the chart and a tool tip will appear with the name of that particular area.

· Right-click in the chart area. In the upper pop-up menu select Format Chart Area… and the Format Chart Area pane will open on the right side of the Window. Click on the arrow to the left of Fill. Select the Gradient fill option and select a color of your choice. Feel free to play with the different options. Be careful to not go crazy with your selections as you still need to be able to read the chart easily. When you are satisfied with your selections, close the Format Chart Area pane by clicking on the in the upper right corner.

· Click on the Plot Area of the chart to select it. Right-click on the Plot Area and as before, two pop-up menus will open. Click on Format Plot Area… in the bottom menu. Under Fill, select Solid fill and select an appropriate color. Close the Format Plot Area pane.

· Select the Vertical (Value) Axis. Right-click to open the pop-up menu and select Format Axis…. The Axis Options section opens by default so you can close it. Select the Number option and then select Currency from the Category drop-down list.

· Remove the Field Buttons on the chart. Right-click on one of the Field Buttons (such as Sum of Gross Profit) on the chart. Select Hide All Field Buttons on Chart from the pop-up menu.

· Make sure the Chart is selected and click on the at the top right corner of the chart.

· Check the checkboxes for Axis Titles and Chart Title. Insert the following:

· Chart Title: Gross Profit by Product

· Vertical Axis Title: Gross Profit per Unit

· Horizontal Axis Title: Item Code

· The final Pivot Chart should be similar to the screenshot below.

· Click on the Gross Profit by Product worksheet tab and drag it to the right side of the Total Inventory worksheet tab.

Chapter 4: Invoice with Controls and VLOOKUP

Application question: How can you create an automated invoice with built-in error controls? Explain how the parameters of the form can prevent backdating invoices, entering unrealistic quantities, avoiding calculation errors, and entering incorrect items.

· Add a new worksheet and name it Sales Invoice. Make sure the tab is to the right of Gross Profit by Product worksheet.

· Recreate the invoice below and we will add controls. Allow 5 lines for items.

· Note: To personalize your invoice:

· Using the Insert tab, select Word Art from the Text section of the ribbon. You will first need to select the letter style that you want to use. In the text box that appears, enter a message to customers at the bottom of the invoice (e.g., Thanks for Your Order). Move the text box and resize to a reasonable size for an invoice.

· Click on the text box with your Word Art and select the Format tab. In the WordArt Styles section, click on Text Effects and then select Transform from the bottom of the pop-up menu. Select the style you want to use.

· Insert clip art at the top on your invoice. Using the Insert ribbon, select Online Pictures in the Illustrations section. In the Insert Pictures window type in the type of image you want for your invoice and click the Search icon or press enter. Once you find your image, click on it and click the Insert button. Resize picture to an appropriate size and move it to the top left corner of the Excel worksheet. Note: you may select any image you desire.

	Your First Name's Candy and Corn Your Name 817-555-1234	Date:
Customer Name: Address:
Item Code	Item Number	Description	Price	Quantity	Total
			Total Invoice:
			Deposit (15% of total):
			Balance Due:

Date Due:

Thanks for Your Order

· Remove the Gridlines clicking on the View tab and unchecking the Gridlines textbox in the Show section of the ribbon. Adjust column widths, alignment, cell formats, fonts and font sizes as desired. See the screenshot of an example completed form on page 20.

· Now add the following controls and formatting.

· Add data validation to restrict the Date entry from backdating. Allow values to be a date between today and 5 days from today. Hint: To have Excel fill in the current date in a cell, enter =TODAY(). Likewise, for a date five days from today, enter =TODAY() + 5. Make this a stop, with and appropriate Invalid Date error explanation.

· Add data validation for Item Code that gives the data input clerk information about the Item Code when he or she clicks on any cell in the Item Code column: (Input message, 10 for candy, 20 for popcorn).

· Add data validation to create a drop down list for Item Number. First, you must assign a name to the list of item numbers within the Inventory table worksheet by highlighting the list in the Total Inventory table and typing a unique name (Item_Number) in the Name Box (upper left box that contains the cell reference) then press enter. Select the five Item Number cells on the Sales Invoice worksheet. Click on the Data Validation icon and in the Validation Criteria section of the Settings tab, select List from the Allow drop-down list. In the Source textbox, enter =Item_Number (or the range name you assigned to the item number list if different). Check your work to be sure that the list contains all the available item numbers to choose from. For the Error Alert, Select Stop for the Style, and input an appropriate Title and Error message.

· We will use the Excel VLOOKUP function (found in the Formulas tab) to use Item Number to return Item Description and Price from the Total Inventory table.

· Go to the Total Inventory table and highlight all of the data from the Item Number column through the Price column. Note: Do not highlight the header row. Enter Desc_Price_Lookup in the Name Box to name your lookup range.

· Highlight the first Item Description cell on the Sales Invoice and enter =VLOOKUP( in the Formula Bar.

· The first item to enter is the lookup value. Click on the Item Number cell to the left. This is the value you want Excel to find in the inventory table. Insert a comma in your formula.

· The next item to enter is the table array information – this is the place Excel will search for the item number. Enter the range name you just created and then insert another comma (e.g. your formula should be similar to the following: =VLOOKUP(B10,Desc_Price_Lookup,

· The last item is the col-index-num (column index number). This is the number of the column that contains the information you want Excel to return. In this case, Item Description is column 2, so enter 2 and a comma followed by false and the closing ). Your complete formula should be as follows: =VLOOKUP(B10,Desc_Price_Lookup,5,FALSE). Note: the item number column is column 1.

· Repeat for Price, remembering to select the number of the price column as the col-index-num.

· Note: your invoice will show #N/A for any Description and Price cell where the Item Number is blank. We will come back to this issue later.

· Select an Item Number for each of the 5 rows.

· Use appropriate Formulas to calculate the row totals, the Total Invoice amount, and the Balance Due (Total Invoice – Deposit).

· For the Deposit formula, round the number to the nearest multiple of .25. Hint: use the MROUND function.

· Fill in your invoice with data of your choosing.

· Format cells in each column as appropriate (e.g., date, currency, number).

· Format the Deposit to show up in red font. Use a fill color to call attention to the Balance due.

· Enter a formula for Date due. All balances are due 10 days after the sale.

· To take off gridlines, sheet tabs, row and column headings, and other similar changes, select desired options under View tab.

More information about Excel LOOKUP functions: Lookup functions are used in Excel to search a table and return a certain value. Lookup tables can be oriented (and thus searched) either horizontally (HLOOKUP) or vertically (VLOOKUP).

· Formula requires 4 arguments:

· =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) where:

· lookup_value = the cell reference you want to look up in the lookup table.

· table_array = the range of cells where the VLOOKUP will search for the lookup_value and the return value.

· col_index_num = the column number in the lookup table that contains the return value.

· [range_lookup] (optional) = a logical value that specifies whether you are looking for an exact match or an approximate match.

· FALSE - if you need the search to find an exact match

· TRUE assumes the first column in the lookup table is sorted ascending whether alphabetic or numeric. TRUE is the default if the argument is left blank.

· Rules:

· The leftmost (first) column has to include the number that the lookup_value is being compared to.

· If using the argument TRUE, the first column must be sorted in ascending order or the lookup formula will not work.

· It is best to define a name for the lookup table.

Application questions: How can you prevent a zero-dollar value ( $ - ) from appearing in the total field if the quantity is blank? Also, how can you prevent the #N/A from appearing when the item number field is blank?

· Add an IF statement (a logical Excel function) to your existing formula. Note: The IF function allows you to make a comparison between a value and what you expect. In other words, IF(Something is True, then do this, otherwise do this).

· Place the curser in the first cell of the total column, remove its current formula and replace it with the following: On the Formulas tab, click on Logical in the Function Library section, and select IF. In the Function Arguments box insert the following:

· Logical_test: F11="" Note: use the first cell of the Quantity column – yours may not be F11. This logical test determines if the quantity field is blank. and if it is, the result of the formula returns a blank, if not then the calculation will be made.

· Value_if_true: "" If the logical test is true then a blank cell will result.

· Value_if_false: E11*F11 If the logical test is false then the result of the calculation will be returned.

Note: the above is only one way you could approach the IF function.

· Copy your formula down to the row above the Total Invoice.

· Add an IF statement to prevent #N/A from showing in the Description and Price fields without Item Numbers. Try these formulas on your own. Hint: check to see if the item number is blank.

Application question: How can you protect your worksheet formulas?

· Protect sheets and cells

· Highlight the Sales Invoice and right-click anywhere in the highlighted area. From the pop-up menu, select Format Cells, select the Protection tab, and clear the Locked checkbox.

· Hold the control key down and highlight the Description, Price and Total columns (in the sales invoice only; do not highlight the entire worksheet column), right-click in the highlighted cells, select Format Cells, Protection, check the Locked checkbox.

· Use the Review tab to select Protect Sheet in the Changes section. Allow users to only Select unlocked cells by unchecking Select locked cells. Type sample in the Password… box. This command will lock all the cells you selected! A lost password cannot be recovered. As this assignment will be graded make sure this does not happen by using the password "sample."

· Click OK and you will need to reenter sample in the next window.

· Note: the screenshot below shows the password for illustrative purposes. It should not be part of your Sales Invoice.

Application questions: (1) What other information in the workbook would you like to protect? (2) What would you do to maintain a template invoice? (3) How could you save an individual customer invoice without changing the template? (4) What are additional error control measures that could be implemented to help mitigate issues with the order?

Create a new sheet to answer the above questions. You do not have to actually do the above, just type in what you could do.

2