Experience Technology Today

Creating Shortcuts to Windows 7 Desktop

2011-01-19T22:33:00.000-08:00

Case Scenario: I want to create shortcut on my desktop to a file that I refer to daily to make journal entries. Because I go to this file daily, I’d very much like to save myself some time from digging through the directories to get to it every time I want to go to it. These steps will make that happen for me and can for you if that is your wish.

1. At the lower-left side of your desktop in the farthest corner of the screen we are all familiar with the start menu. In Windows 7 there is an icon right beside it that is similar to a file folder resting in a file stand. This is your windows explorer app. Click to open.

2. By default it will open to your Libraries directory where you can see Windows 7 pre-established folders intended for user storage. These include Documents, Movies, Music, Pictures, and Videos. In this case we are going to use the Documents directory due to the type of file that we will be storing. Double click on the Documents folder to open.

3. You may already have several files there. In fact, the file you want on your desktop may already be there, if it is skip to # 8 and proceed. If not, then we will now begin to create the folder we will be using to store our document(s). Locate and point to an area where there are no files so that you are pointing to the white wall behind the files. Once you have found a good spot, right click. A shortcut menu will appear. Point to ‘New.’

4. A submenu will appear. Point to and click ‘Folder.’

5. The will create a new folder and the name of the folder will be highlighted. The Windows default name is generally ‘New Folder.’ However, if you already have a folder in that directory named New Folder, Window will automatically tack the next number on it. For example, ‘New Folder (2)’ or ‘New Folder (3) if the (2) already exist. Notice that next to the New folder I’ve created in the image below there is a folder to it’s left named New Folder so Windows tacked the (2) at the end. No two folders in the same directory can have the exact same name, regardless as to the case used (caps or not). Now, as stated, notice that the name of the new folder is highlighted. This means that you can begin typing and anything you type will automatically replace what is highlighted.

6. In this case I’m going to type ‘My Journal’ as the name of the folder. Once you have named your folder press the Enter key.

7. Notice that your folder has likely repositioned itself to fall in alphabetical order and is highlighted (see below).

8. This is the folder we want a shortcut to on our desktop. So right click on the folder; a shortcut menu will appear. This time point to ‘Send to’

9. A submenu will appear. Point to and click ‘Desktop (create shortcut). Once you have done that, you can close the file explorer window by clicking the red X at the top-right of the window.

10. You should see your new folder on your desktop with a small blue arrow at the base of the icon indicating that it’s a shortcut. You will also notice that Windows automatically tacked ‘-Shortcut’ at the end of the file name.

11. Now open what ever program you will be using to generate the document(s) you plan to store in this file. Generate a new document and click on the file menu at the top-right of the screen.

12. Depending on the application you used to generate the new document, you view may not appear exactly like mine below. I am using MS Word 2010, earlier versions appear differently. But in any version of any software, the ‘Save As’ command is in the File menu. Click ‘Save As.’

13. The Save As dialogue box will appear with a default name noted and highlighted. Remember, just type what you want and it will replace any text highlighted. Type the name you want it to be but do not hit save yet. We first have to go to the file you want to store it in.

14. At the top of the dialogue box you will see Libraries; Documents; My Documents. (Note: you’ll also see it at the right) We need to step backwards to the Libraries directory. Point to and click Libraries as shown below.

15. Now we see something familiar, Documents, Movies, Music, etc. Point to and click ‘Documents’ and then click the open button at the bottom of the dialogue box.

16. Use the scroll bar along the right side of the dialogue box to move the files up and down until you locate the file you created. In my case it was ‘My Journal.’ Once you have located it, point to and click it. Then click the Open button at the bottom of the dialogue box.

17. Review your file name to ensure it still says what you want it to say; if not, change it to what you want. Click save.

18. Finish editing the content of your document until you are happy with it and then click the save button on your toolbar. Click the red X at the top right of the screen to close that application. You should now see your desktop. Locate the shortcut to that folder and double click to open.

19. You can now see your new document in the folder. Notice the path at the top of the screen. It shows that you are in the path you followed to create the folder and you got there without having to go through all those clicks. Your first initial save of the document will be the only time you have to route through the directory to save it. From this point on, when you open the document for editing or adding content to it, when you save it, it will automatically save to its existing location without the need to route it again.

20. You simply double click to open the document, make your edits, save and close. It will be there the next time you refer to the shortcut file to access it again.

For those of you who requested this information, I hope it has been helpful to you. If you have any further questions please feel free to leave a comment on this post by clicking the ‘Leave A Comment’ link below. If my blogger will not allow you it could be because you have not registered with blogger to participate in blogs managed by the system. If this is the case with you then your welcomed to go to blogger.com and register so that you can participate in their blogs. If you had rather not, then you also have the option to join us on Facebook and post questions there. Just submit a friend request and start any discussion, post any question, or simply give feedback. We always want to hear from you.

TechMustHaves Joins Facebook

2011-01-17T17:57:00.001-08:00

TechMustHaves' has just launched it's own Facebook page. Just click the link provided in this post to join or start a discussion.

Effective Communications With Excel Charts

2011-01-15T13:28:00.001-08:00

GOAL:

Collect information from an Excel table that you want to apply to a picture that represents the information you are trying to communicate to an audience

OBJECTIVES:

Choosing a chart style
Gathering and organizing information
Displaying or Moving a chart
Getting the big picture
Changing a chart style

1. Open ‘Chart 1a’ from the Grab Box at the bottom of the screen. You will find it to contain a table of monthly profits from sales. In this lesson we will learn to extract information from this table to construct a chart that will give a pictorial view of our sales/profits.

2. Click on the Insert tab and select the Column feature as seen below

3. Choose the column chart you want to display, in this case we are going to choose the 3-D Clustered Columns

4. You screen should now look similar to the image below. You should be on the Design tab. You can browse the Chart Styles by clicking on the dropdown arrow at the right of the Chart Styles section on the ribbon.

5. Once you have located and clicked in the dropdown arrow this is what you will see, choose one of the styles available to you. We are going to choose the one in the top row, second column, highlighted in orange.

6. You will not see any change until you have extracted the data for the chart. On the Design tab/ribbon choose Select Data, as shown below

7. The Select Data Source dialogue box will appear, see below. Click the small square at the right end of the Chart Data Range field located at the top of the dialogue box. The small square is highlighted in blue in the image below.

8. This will draw the dialogue box up and allow you to select the range of cells that will apply to the chart you are building

9. Select the range of cells that holds the values you wish to track with the chart. In this case we are going to show the total gains per month; so we will select B20-M20. Notice that as you select the range; the references are automatically generated in the dialogue box. Click the button at the right of the field again to bring the dialogue box back into full focus.

10. Notice that the months are not indicated in the Horizontal Category Axis. The value data will not do us much good without knowing what months they represent. So we will need to go in and pick up the months from the table using this dialogue box.

11. Under Horizontal (Category) Axis Labels, click Edit.

12. The Axis Labels sub dialogue box will pop up; click the button at the right of the field that allows you to select the applicable range of cells. Select the range of cells containing the months and press the button again.

13. Notice the range reference is automatically placed into the sub dialogue Axis Labels; click OK

14. Notice that the months are now listed in the Data Source dialogue box. We have not yet named our series. Click on Series1 under the legend Entries (Series) section. Click Edit.

15. Click the button at the end of the field Series Name to select the cell that holds the name of the field that holds the values you are presenting.

16. We are presenting the Monthly Gain/Loss values, so the cell holding the words “Monthly Gain/Loss” will be the cell we select in this case. Notice again that its reference is automatically generated in the dialogue box.

17. Once you have selected the appropriate cell; in this case A20, click the button at the end of the field again. Click OK

18. You should now be back at your Select Data Source dialogue box. Click OK and view your chart.

19. Notice that your chart was automatically placed right on top of your table. You’ll want to move it. You could click on the border of the chart and drag it to another location on this sheet or frame it as a new sheet.

20. To frame it as a new sheet right click on the chart and choose the option to Move Chart…

21. Change the selection from Object In to New Sheet.

22. Once you have chosen ‘New Sheet,’ the Chart1 will be highlighted. Here you can type what you want the name of the sheet to be. In our case we are going to name this one Monthly Sales.

23. Once you have typed in Monthly Sales, click OK.

24. Notice that the chart is now on a sheet all of its own. Let say however, that you wanted to show the total monthly sales but you also wanted a sneak peak at what items contributed to those sales. Follow these steps. With the chart showing, click on the Select Data option on the design screen as indicted below.

25. In this instance we are going to select the entire range of the table that will be displayed on our chart. Click the button at the end of the field ‘Chart data range’ so that you can view and select the range appropriate for what you want to view on your chart.

26. Select A2-M19; (this will include the categories (months row 2) and the values (in rows 3-19) and the series in column A of rows 3-19) Once you have selected A2-M19 click the button at the end of the field again to bring the Select Data Source dialogue box back into full focus

27. Notice now that you have a series for every item listed on the inventory/table and all your months are included. Click OK.

28. Your chart then reflects a color coded column for each item of each month, but in some cases, such as this one, it causes the chart to become cluttered. This is an easy fix.

29. Select the Change Chart Type option on your Design ribbon

30. Choose a stacked chart of your preference. In this case we are going to choose the 3-D Stacked Cylinder chart style. Stacked means that the columns will be stacked on top of each other; not beside each other. This has two benefits: 1) you still are able to see the items contribution toward the sales and at the same time 2) get a the big picture view of total sales.

31. Notice that once you have selected the stacked column chart of your preference it will appear as below giving total sales of each month as well as the contributing factors for each month.

I hope you have enjoyed this tutorial and have learned something from it to improve your performance output and communications.
I’d like to also let you know that new features are coming to TechMustHaves.com; a page dedicated to the collection of free downloads that will empower your productivity whether it is in gaming, networking, office productivity, communications, financial management, tax preparations, etc. It is a new page and new items are being added every day. Currently it is secured and only viewable by guest id and password. Once it fully launches announcements will be made to this blog, so keep your eyes open.

Trouble getting gateway apps to run or online document viewers to actually view? It could be your IE (Internet Explorer).

2011-01-03T21:57:00.001-08:00

Recently after being down and out of commission for doing any work at home via our Corporate office’s Gateway provided Applications I discovered that my Internet Explorer (IE) had everything to do with it. If you are one who often works from home this article will be of some interest to you, if not know, then in the future before you decide to got IE 9 Beta or before you upgrade your OS to Windows 7. First what you need to know is that there are two types of IE; 32bit and 64bit. Windows 7 comes with both. Online document viewers, similar to something like what you see below, will not view in 64bit Internet Explorers. Gateway provided applications will not run from 64bit Internet Explorers. This is also the case with IE 9 (Internet Explorer 9 Beta), so I’d stay away from it, especially if you involve your activities in gateway apps and document viewers.

If you have Windows 7 or are wondering if you are using a 64bit IE or not, IE 64bit will have (64-bit) beside the application name, the IE 32bit will not. See image below:

You can obtain IE 8 32bit at the following location: http://www.microsoft.com/windows/internet-explorer/worldwide-sites.aspx Microsoft will offer you IE 9 Beta, ignore it. Scroll down to find two steps (select country & select operating system).

Once you have performed the steps above simply follow the steps through until finish. Then reboot your PC and enjoy your surfing or gateway apps. Remember avoid the 64bit IEs and the IE 9 Beta when working with online document viewers or gateway apps. Also, where it concerns gateways, make sure you have the most update gateway client drivers. Get with your company’s system administrator for more information on that issue.

Linking Worksheets–Part 2 of Simple Formulas/Calculations

2011-01-03T21:43:00.001-08:00

During our last discussion of building simple formulas we kept our work to one worksheet. This tutorial will carry the exercise one step further by linking two different sheets in a formula so that the information in one sheet is completely dependent on the other and that by updating the original sheet to meet your immediate needs; you are also completing another task that is on the horizon. This will become apparent as we move forward through this lesson. This lesson will also come with an added bonus as in addition to formulas you will also learn to format a table with borders and colors to make it attractive and easily readable. By the end of this lesson you will be able to do the following:

Rename a sheet to identify its purpose, task, or intention
Refer to another sheet to gather data necessary in your active sheet.
Apply formats like color, borders, bold font, italic font, etc. to a range of cells.
Calculate totals to include sales tax for replenishing your inventory.
Merge a range of cells to create one cell that stretches across several columns/rows.
Insert a formula that will automatically enter the date so that when you print the form it will automatically have the current date on it.
Insert rows
Resize columns

In our previous lesson, if you caught it, you completed the practice imaged below; you’ll need to retrieve it from your computer or from the grab box at the bottom of this screen, it is titled ‘Linking Worksheets.’ If you did not and you would like to complete it to catch up you may do so at: Understanding and Building Simple Formulas . If you’d rather begin from here, you can simply download and save the file titled ‘Linking Worksheets’ located in the grab box at the bottom of this screen. While you are down there, feel free to turn on some Christmas music to enjoy while you complete this exercise. You'll find it to the right of the grab box. Three fundamental and important rules as we go through this tutorial are: 1. Read each instruction fully before performing as each step has hints to keep you on track and if you try to do as you read you may get ahead of yourself and end up off track and frustrated; so read first, then perform (as you read again if necessary), but always read the instruction before performing. 2. Do only what the instructions say and nothing more, more could take you off track. Something as simple as striking an enter key by mistake can take you off course. 3. If you do make such a mistake perform the following steps to undo and start over with the object again: a. Hold down your Ctrl key, b. strike the Z key, c. and then release the Ctrl key. This will undo you last move. I give you this process because it works in all versions of Excel for as far back as I can remember. Other ways of making this same function occur differ from one version to another. If you know them already, great. Now, having covered your safeguards for this lesson, let’s begin. Once you have downloaded and opened the file named Linking Worksheets located in the grab box at the bottom of this screen, you should see what looks like the image below. Do you see it? Notice the tab of the sheet at the bottom left corner of the screen labeled ‘Sheet1.’ We want to rename this sheet to ‘Sales’ to reflect its purpose, to calculate sales and gain/losses. To do this, perform the following instructions: 1. Point to and double click the sheet tab so that it highlights the tab name as you see in the image below. If double clicking is difficult for you, you can also right click and then select ‘Rename’ from the list of options in the menu that appears when you right click. Either method will result in the tab label being highlighted as you see below. 2. Once you have successfully highlighted the sheet tab, type ‘Sales’ and strike the ‘Enter’ key to rename the sheet. 3. The work we will be performing today will be primarily in Sheet2, but first we are going to rename it to ‘Requisition.’ Click that tab to make that sheet active. 4. Now repeat steps (1) and (2) to rename Sheet2 to ‘Requisition.’ 5. Now we are going to pick up data from the Sale sheet to display and automatically update in the Requisition sheet. Click on cell A1 on your Requisition sheet to make it active, if you are unclear on how to get there or what this ‘A1’ is, you’ll need to go through the tutorial Understanding and Building Simple Formulas first, and then come back to this lesson. 6. Strike your = key 7. Point to and click on the Sales tab and then point to and click cell A1 of your Sales sheet. It should then have a scrolling marque around the cell as seen below. Also, make a mental note that the data in the sheet reaches as far down as row 20; you’ll need to know this in step 11. 8. Strike the ‘Enter’ key. A ‘0’ will appear in A1 of the Requisition sheet because, as you will recall, there was nothing entered into the A1 cell of the Sales sheet. 9. Point to and click cell A1 of the Requisition sheet to make it active. 10. Recall in our last lesson that we performed what is called a fill-down. Notice in the image below that at the lower right corner of the cell there is a small square; known as the ‘fill-handle.’ 11. Point to this square until your cursor becomes a small, bold plus sign; when it does press and hold your left mouse button and drag down until you reach and include row 20, see below. Recall that in step 7 we made a mental note that the data in the Sales sheet reached row 20. 12. Once you have reached row 20 release the mouse button you are holding and watch as the data from the Sales sheet appears in the selected range on the Requisition sheet, see below. 13. Notice that the data spills beyond the right border of the cells. This is a simple fix. Simply complete the following steps (keep the cells highlighted, don’t click inside the sheet or it will deselect, if you do so by mistake you can simply highlight them again before performing step 14) a. Notice that there is a line between the column A and column B indicators. b. Position your cursor over this line until it becomes a vertical line with an arrow in the middle of the line pointing both to the right and the left. c. Double click and it will automatically resize to fit the longest item in the list held in the column on the left side of the click (in this case column A) 14. With cells A1:A20 highlighted point use the fill-handle to fill in across the next two columns. See below. (Now any time you update these items on the Sales sheet it will also update on the Requisition sheet.) 15. Notice that now Requisition rang A1:C20 now hold the data entered in the same range of the Sales sheet. 16. Make cell D2 of your Requisition sheet active and type in that cell Requisition; strike your ‘Enter’ key. This column will calculate how many items you need to requisition for purchase to your purchasing department or business office. 17. Fill in the remaining columns accordingly: E2 should be Item Cost (what each item cost you to purchase at “retail” value) F2 should be Item Expense (this will calculate the total expense for replenishing this item on your shelf. 18. Now we will begin our formulas for these three columns. Make D3 your active cell and strike your ‘Enter’ key. 19. Point to and click cell B3, you screen should look like the image below 20. Strike you – key and point to and click cell C3 (this will subtract the value of C3 from the value of B3) 21. Strike you ‘Tab’ key (this will finalize your formula in cell D3 and place you in cell E3 making it your active cell); then strike your = key (see below) 22. Point to and click your Sale tab and then point to and click cell E3 in the Sales sheet (there’s that marque again so you can confirm that you selected the cell you intended) 23. Strike the ‘Tab’ key. Again this finalizes the formula in the E3 cell of your Requisition sheet and makes cell F3 active for its formula. (Notice that it also brought the retail value over from the Sales sheet to the Requisition sheet, now when this information is updated on the Sales sheet it will also update on the Requisition sheet) 24. With cell F3 active strike the = key 25. Point to and click cell D3 26. Strike the * key 27. Point to and click cell D3 (this will multiply the item cost and the number of items to requisition to reveal the total expense for replenishing that item on your shelves) 28. Strike the ‘Tab’ key to finalize your formula in cell F3. 29. Now we want to fill down the other rows. Select range D3:F3 as indicated below. 30. Use your fill-handle to fill down to row 20. 31. Once you release the button after the fill down your sheet should look like the one below. 32. Now to figure our total cost for restocking our shelves. Select range B20:F20 as seen below. 33. Strike the ‘Delete’ key to clear its contents 34. Make cell F20 active 35. Locate the AutoSum feature on your tool bar; it should look similar to the highlighted item in the image below 36. Once you have located it, double click on it. Notice that the first click places the marque around the cells directly adjacent and above the active cell. 37. On the second click it will reveal the results of your double click with a value that represents the sum of the above values 38. Of course, we all have to pay taxes so this is not a complete representation of our total cost of restocking. First let’s rename that row from ‘Total Gain/Loss’ to ‘SubTotal.’ Make cell A20 active and type the word ‘SubTotal.’ 39. Below that, in cell A21 type tax and in cell A22 type Total Expense 40. In cell B21 we need to note what kind of tax will be required (.07, .08) depending on the sales tax of the state you live in. In this case we are going to use a 7 percent sales tax by entering .07 in cell B21 41. Next we are going to figure how much sales tax this purchase will cost us. Make cell F21 active and strike the = key 42. Point to and click cell F20 43. Strike the * key 44. Point to and click cell B21 to multiply your subtotal and your sales tax requirement to find your tax cost. 45. Strike the ‘Enter’ key to finalize the formula and make the cell below it (F22) active. Notice that this purchase will result in a sales tax expense of 18.67. Strike the = key 46. Point to and click cell F20 47. Strike the + key 48. Point to and click cell F21 49. Strike the ‘Enter’ key. Notice that your total purchase will cost you $285.42. 50. Such a sheet can be, not to mention boring, but easily misread without any boundaries or guides. So we will use some formatting features to change this and make this sheet much more professional in appearance when presenting to another department. Let’s begin with bolding and italicizing some key bits of focus information in our spreadsheet. We want to bold our SubTotal row, our Total row, and our column heading row. Point to and click row indicator 20 51. Locate the Bold button on the toolbar as seen below 52. Once you have located it, click it. The text in the selected row becomes a bold, heavy type font. 53. Repeat this same step to bold the Total Expenses row (indicator for row 22) 54. Highlight row 21 by clicking on the indicator of row 21 55. Locate the Italic button on the toolbar as seen below 56. Once you have located it, click it. The text in the selected row leans to the right slightly giving it an italic appearance. 57. Next we will add some borders that will further separate the focus information from the focus dependent information and give the table boundaries. Select range A3:F19 58. Locate the borders tool on your toolbar and click on the small arrow pointing downward at the right of the border button as seen below: 59. Once you have located it, click the arrow pointing down to reveal the menu you see below. Left click on the highlighted option you see below called Outside Borders (if you have screen tips turned on you can see the short cut for making this command occur – in this case it is Ctrl+Shift+& [See Keys to Faster MS Office Operations and Document Navigation to learn out how to execute/use these shortcut keys]) 60. Once you click on Outside Borders, click outside the selected range to view the border you have just placed. 61. Select range A2:F22 62. Locate the same Border button on your toolbar and click on the same arrow. This time click on the Thick Box Border option. See below. 63. Click off the selection to view the border you have just placed 64. Now we are going to apply some color to our sheet. Select range A22:F22 65. Locate the border button on your toolbar again and click on the arrow. This time click on the Top Border option. 66. Now locate the color fill button on your tool bar and click on its arrow (looks like a tipped paint can) Pick a color and click on it, I’m choosing yellow, a bright and cheerful color. 67. Now click off the selection to view the formats you have applied. 68. Now select range A2:F2 and repeat step 66 to apply your color to this range. 69. Click of this range to view the color you have just applied. 70. Now recall that row 1 held formulas that provided no real asset to this spread sheet so we can delete it. Select row 1 by clicking on its indicator. 71. Strike your ‘Delete’ key. 72. Now a purchaser may not necessarily need to know how many you sold or what your base inventory should be so columns B and C are solely for operational purposes only and only serve as unnecessary data to the recipient. Thus there is no need in it being present or seen when submitted to the purchaser. But to completely remove it would totally destroy everything we have just accomplished. So to remove it from sight without removing it from the sheet we will perform these steps. Point to the B column indicator; press and hold the left mouse button; drag to the right through until both B and C columns are highlighted, as seen below, and then release the left mouse button 73. Right click on either the B or the C column indicator to reveal the options below. Left click on the ‘Hide’ option. 74. Click off to the side to deselect the hidden columns and see your table without the columns. (Remember that the B & C columns are completely dependent on the information in the Sales sheet, so you will never have to edit them because every time you make changes to the B or C column in the Sales sheet, it automatically updates on the Requisition sheet; which in turn updates the rest of the Requisition sheet. Give it a try when we are done.) Now our form needs a title, and like most forms, a date is a must. Let’s give our form a title and a date command. 75. Make cell A1 active 76. Select range A1:F1 77. Locate the ‘Merge and Center’ button on your toolbar as shown below 78. Click on it to merge cells A1:F1 into one cell that stretches across the columns highlighted A-F. The same can be done in rows or the combination of rows and columns. Notice how A1:F1 merged to form one cell. 79. With that cell still highlighted/selected type ‘Inventory Requisition’ 80. We now need to insert a row between the form’s title and the form’s table. Click on row indicator 2 to highlight row two 81. Right click on row indicator two to reveal the options shown below 82. Left click on ‘Insert.’ Notice a new row appears above the row you selected prior to the insert. 83. But this move left an unwanted border at the bottom of the cell holding the form’s title. Select the cell holding the form’s title 84. Locate the border button on the toolbar and click on the arrow button. Click on the ‘No Border’ option to remove the border from the form title 85. Select cells A2:F2 86. Repeat steps 77 and 78 to merge these cells. This will hold our automatic date 87. With this newly merged cell still active, locate the ‘Insert Function’ button on the formula bar, located right under your toolbar and to the right of the name box. See below. 88. Once you locate it, click on it to reveal the dialog box below. Notice that a selection of text is highlighted in the box. 89. With the text selected, type ‘date’ and notice that the selected text is immediately replaced with the word ‘date.’ Click the ‘Go’ button or strike the ‘Enter’ key. Notice that the list in the section below where the text was at changes (see below). 90. Point to and click the “NOW” option. Click the ‘OK’ button. You’ll notice a box appear like the one below. Click OK. 91. Notice that the current date and time is entered into the form. Each time you open this workbook the date automatically updates. Each time you conduct your monthly inventory and determine your Gain/Loss on the sales sheet, you will automatically be completing your monthly Requisition. All you have to do is click on the requisition sheet, print, and submit. At the start of the next month when you’ve restocked your shelves, you’ll replace the data in the Remaining Balance column of your Sales sheet with the a number matching the beginning balance only to do it all again when inventory and order time hits again. Considering the positive feedback on the tutorial Understanding and Building Simple Formulas, I hope you guys enjoy this one just as much. Here's a hint as to what to look forward to. What to look forward to… At some point we can come back and build a macro button that will automatically reset the form for you. But, we’ll leave that for another time. I’m also looking forward to showing you how to generate a pie chart to show the percentage of sales per item which is useful in identifying where your sales were strongest during the month. Maybe later we can look forward to extending this to showing trends that will show when during a year your sales seem to be at their highest in order to predict a higher sales rate, a higher need for materials, and consequently a higher inventory count. On another note… I have a couple of announcements to make regarding my blog, I’ve recently invited a co-writer to join my blog in publishing such post as these. He may be contributing in the near future. So we can look forward to the possibility of more input, more tutorials, and a broader range of IT topics. I’d like to introduce you to a new page (Sweet IT Deals) that I’ve added to my blog where I discuss great deals I find online. Each item is compared to other popular venders and the advantage is discussed. I only present them if they are a benefit or savings in comparison to your local/popular vendor. And, yes, you’ll likely find IT items posted there for the most part; maybe the occasional other items, depending on the season. I sincerely hope that I’ve kept them simple enough without boring you. I do try to post a balance of the two. On some occasions a more advanced tutorial and at other times a beginners tutorial. So if I don’t get back to the topic you are looking forward to, keep an eye out because its coming. Don’t forget you can always subscribe for alerts to notify you of updates and can rate my posts and add a comments by replying to my posts. I always look forward to hearing from my readers. Thank you for stopping by.

Understanding and Building Simple Formulas

2011-01-03T21:35:00.001-08:00

The first and most important thing to building a formula for automatic calculations in Excel is to know a cell reference when you hear it, understand what it means and how it applies to your objective; to build a calculating cell. The next is to know your operators. By the time you finish this tutorial you will be able to do the following:

Recognize and identify a cell reference
Explain its purpose
Recognize and identify the basic arithmetic operators as used by Excel
Build a basic formula for a calculating cell

As a bonus you will learn a simple shortcut to filling your sheet with a running formula without the need for rebuilding for each cell you want to calculate for you and how to automatically sum a row or column with two clicks of the mouse. This tutorial will begin with a brief introduction to the above and a practice assignment for meeting that objective. Keep in mind that while these tutorials are created using 2010 version of MS Office; earlier versions will have a different look and feel but the terminology is always the same. The tools used may be located in menus or on toolbars instead of on ribbons, but they are called the same and perform the same function. This lesson in particular will be mostly of keyboard and mouse work simply because of the nature of the lesson.
Below is a picture of a very simple worksheet built specifically for this lesson. Notice that a single cell has a darkened border around it. You will also notice that above that cell is a yellow block with the letter F in it. This is your column indicator. This one indicates that you are in the F column of the worksheet. If you follow the row to the left you will notice a similar yellow block that has a 3 in it. This is your row indicator. This one indicates that you are in the 3^rd row of the worksheet. The combination of the two indicators is always expressed as ColumnRow; in this case F3. This is what is known as a cell reference. If there is any question as to what cell you are in you can always view the Name Box, which is located right above where the column indicators and the row indicators meet at the top-left of the worksheet. Notice that this one has F3, as it is the active cell that I am in. If you want to save time navigating to a particular cell simply type its reference in the name box and hit the enter key to go straight to that cell. If you have Excel, take a moment to try it out.

Now that you know what a cell reference is, review the table below for your basic Excel operators.
Operator (key stroke)
Operations/Action
+     Addition
-     Subtraction
*     Multiplication
/     Division
=     shows the outcome a formula or comparison
Excel uses cell references in conjunction with operators to calculate your figures based on the information you have placed in the cells referenced within the formula of the calculating cells. Let’s try it out with a practice.
In this practice we are going to begin in cell F3 and build formulas to calculate the markup of the items listed on the sheet, the percentage of the markup, the items sold, and the gain/loss. I have attached this worksheet to this blog if you would like to download and use it for this practice. It is saved to be compatible with versions of MS Office or MS Excel dating all the way back to 97. If you use a newer version simply save it to the version you use; or keep it in compatibility mode if you wish. Follow along and perform each step as it is stated. If you do not wish to perform the practice as you review the instruction feel free to share the post to your favorite networking site (twitter, facebook, RSS feed, etc) for future reference. You also have the option to subscribe to this blog (absolutely no charge) and receive automatic updates with each new tutorial posted. Click Simple Calculations – Template for Download to get the practice worksheet or you can get it from the grab box at the bottom of this blog; the file is called ‘Simple Calculations – Template for Download.’
Regardless as to when you decide to do the practice, I only ask this of you – “Make sure you read each task instruction to its entirety before performing.” Many instructions come with NOTES of importance that will minimize your errors while performing them. This ensures your full understanding prior to your attempt to perform and you’ll get more out of it. Let’s begin.
Once you have opened your worksheet go to F3 by clicking on the cell or typing it in the name box and hitting the enter key on your keyboard. Follow these steps (Each step will have an image below to reassure you that you are on the right track.):
1. Strike your = key (NOTE: Do not hit the enter button or any other key – move on to next step. If you have already hit another key and your cell does not look like the one in the screen below hold down your Ctrl key, strike the Z key, release the Ctrl key. This will undo you last move. I give you this process because it works in all versions of Excel for as far back as I can remember. Other ways of making this same function occur differ from one version to another. If you know them already, great. Once you have undone your mistake, click on F3 and try again – strike your = key)

2. With your mouse point and click cell D3 (NOTICE: the cell reference of the cell you just clicked on appears in cell F3. See below. Again, do only as instructed. If you make a mistake at any point in the adventure, simply perform that Undo procedure explained in step one and try again. Your active cells should always appear as mine does in the image presented as you perform each instruction) Now with these reassurances in place, let us pick the pace up a bit.

3. Strike your – sign

4. With your mouse click cell E3 (NOTICE: the formula that tells this cell how to calculate the data in your sheet for this item reads =D3-E3. This tells Excel to subtract the value of cell E3 from the value of cell D3. The value is whatever you make it. Say you purchased your comic books for resale at $1 per comic book. You are selling them for a $2 price. This formula will provide the difference, which is the mark up (or mark down if on clearance).)

5. Strike the tab key (NOTICE: the formula in F3 became a value of $1; which is the difference, the markup/down. Recall the dealer’s retail was $1 and the buyers price was $2 which revealed a markup of $1. If the item was on clearance and priced at .75 cents to clean up inventory the outcome would have been a -.25 or (.25) mark down. If you are following along, take a moment to click on D3 and change the price from 2.00 to .75 and watch as the value of cell F3 changes from 1.00 to (.25) or -.25. Make sure you change it back to 2.00 so that what you see on your worksheet as you go through this tutorial will match what you see in this tutorial. You can always go back and play with the numbers when we are done.)

Now let’s determine the markup percentage. Percentages are determined by dividing the smaller by the larger. So we will divide the retail by the price. With your active cell being G3 complete the following steps:
7. Strike your = key and with your mouse point and click cell E3

8. Strike your / key

9. With your mouse point to and click cell D3 and strike the tab key (Notice: the markup was calculated at 50% of its current price.)

It’s the end of the week. You are taking inventory. The beginning balance is what you had at the start of the week. As you take inventory you note what you have remaining in the worksheet. And now you want the sheet to calculate how many items were sold during the course of the week. With your active cell being H3:
10. Strike the = key and with your mouse point to and click cell B3

11. Strike your – key and with your mouse point to and click cell C3. Strike your tab key. (NOTICE: that apparently you sold 5 comic books all week long)

Considering the mark up and the number of items that were sold you now want to determine how much profit your store made. You will do this by multiplying the number sold by the markup value. With your active cell being I3 perform the following:
12. Strike the = key and with your mouse point to and click cell H3

13. Strike your * key

14. With your mouse point to and click cell F3

15. Strike the tab key (NOTICE: that selling 5 comic books at the markup value of $1 generated a gain of $5.)

Now that you have entered the formulas for this one item, is it necessary to repeat these all the way down the entire list? Most stores lists get pretty extensive if the stores have any size to them at all. Luckily the wonders of Excel have a very quick solution to this task. To fill these formulas in all the way down the entire list perform the following tasks:
16. With your mouse point to and click without releasing the mouse button cell F3; with the mouse button held down, drag through cell I3, and then release the button. This will highlight the select range of cells from F3 thru I3 as shown below. (NOTE: recall the undo procedures in step one if you make a mistake while performing this task.)

Notice that at the lower-right corner of your selection (the range of highlighted cells) there is a small square (see image at right). If you were to place your mouse over that square just so it will become what is known as a cross-hair. It will look like a bold plus sign +. Now leave your selection highlighted and perform the following steps:
17. Without clicking on any cells, position your mouse over that square mentioned above until you see the cross-hair +. Once you see the cross-hair you will press and hold the left mouse button and drag it down until you reach the bottom of your list. (NOTE: As you are doing this, you will know that you are on the right path if it appears that a growing border is appearing as you move downward, see below.)

18. Once you have reached the bottom of your list you may release the button. Upon releasing the button values will appear in all the cells and the range of selection/highlight will have grown as far down as you dragged and released. (NOTE: If you find that you released too soon, don’t fret and don’t undo, simply point to the square at the lower-right corner of the new selection and pick up where you left off.)

Now that you know how much you made off each item, what about overall. There is a very quick 2-click solution to this. At the bottom of the table you will notice a row labeled as Total Gain/Loss. This means that this row is reserved to reveal the total amount you gained or loss in sales this week. This also means that you will want the value of the total to fall in the column it represents, which is the gain/loss. So with that in mind perform the following:
19. With your mouse point to and click cell I20.

20. Then locate the AutoSum button on your toolbar or ribbon; it will look similar to or in older versions of Excel it may only show the symbol . Once you have located it, click on it once. Do not click or strike anything else until you perform step 20. (NOTICE: that the range of cells immediately above your active cell has a scrolling marque border and that your active cell has a formula in it =SUM(I3:I19). This means that this cell will sum (add) all the values found within the cell range of I3 thru I19. As a rule Excel will automatically pick up the range of adjacent cells above first; if none exist above it will pick up the range of adjacent cell to the left. Any other options you prefer can be easily chosen simply by highlighting the desired range while the formula is active as it is below. You might want to play around with this feature after we are done. Only one more step.)

21. Strike the enter key; your total gain/loss value will appear. It appears that your store profited $266.75 this week. Save.

Below is an image that shows what the formulas in your worksheet look like to Excel. Notice that during the fill-down process Excel automatically adjusted to formulas accordingly by changing the row indication in the cell references for you as you filled down from row to row. This will also occur when you are filling in from column to column; it will update the column indicator in the cell references and you progress from one to the next.

As you toy with Excel you may find creative ways to manage your data. During the process there will come a time when you don’t want the indicator to change for a particular reference. In this case when building your first set, the set you intend to use for filling down, you can place a $ sign before the indicator you don’t want to change in the applicable cell reference. This will prevent Excel from updating that indicator. For example: take for instance this formula =$C$3*D5 as I fill down cell reference C3 will always be C3. D5 however, will change from row to row (D6, D7, D8, and so on) or will change from column to column (E5, F5, G5, and so on) depending on the direction I am filling in.
Now, answer me these riddles:
Can you recognize and identify a cell reference? What is a cell reference’s purpose? Can you identify Excel’s basic operators of arithmetic? If asked, could you build a basic formula for a calculating cell? Try it.
Keep in mind that you are welcomed to post inquiries, special requests, comments, and recommendations. I try to post at least once a week and you are welcomed to subscribe for email alerts so that you don’t miss out on any tutorials.