Excel

Browse » Home » All posts

Thursday, July 18, 2013

How to Make a Pie Chart on Microsoft Excel 2003

1. Open MS Excel 2003. Click on the 'File' menu at the top of the screen and select 'Open.' Browse through the folders on your computer to locate the file with the data. Click on the file to select it and click on the 'Open' button to open the file.
2. Click on the Chart button which is in a tool bar at the top of the screen. This button looks like a bar graph and will open the Chart Wizard dialog box. You can also click on the 'Insert' menu and then select 'Chart' to open the wizard as well.
3. Click on 'Pie' from the list of chart types in the first step of the 'Chart Wizard' box. Select the type of pie chart you want for your report. Click 'Next' for the 'Chart Source Data' box.
4. Click back into your spreadsheet and highlight the data you want to graph. This array will populate in the 'Chart Source Data' box. Click 'Next' to open the 'Chart Options' Box.
5. Click through the tabs at the top of the 'Chart Options' box to select options you want to display with your chart. This includes a chart title, legend, and labels. When finished, click 'Next' to open the 'Chart Location' box.
6. Select 'As Object In' if you want to place the chart in your current work sheet. Select which worksheet in the workbook you want the chart to display in. Click 'Finish' to display the chart.

How to Create Frequency Distribution for Executions by Year in Excel

1. Open a new Excel 2010 worksheet. Select cell 'A1' and enter the years for your data into column 'A.' When you have all the years entered, select cell 'B1' and enter in the corresponding number of executions in column 'B.'
2. Click on cell 'C1.' Enter in the bin ranges that you want to sort the frequency by into column 'C.' These numbers will represent the top end of the ranges. If you enter '50,' '75' and '100,' your frequency distribution will have three categories: the executions per year that range between 0 and 50, the executions per year that range from 51 to 75, and the executions per year that range from 76 to 100. Enter as many bin range numbers as you need.
3. Click the 'Data' tab at the top of the screen. Select the 'Data Analysis' button from the toolbar. Select 'Histogram' from the list of options that appear and click 'OK.'
4. Place your cursor into the 'Input Range' field. Select cell 'A1' and hold down the mouse button. Drag the mouse to the last cell that holds information in column 'B' and release the button.
5. Place your cursor into the 'Bin Range' field. Select cell 'C1' and drag the mouse down until you reach the last cell that holds data in that column.
6. Place a check next to 'Chart Output' if you want to get a graphical representation of the frequency distribution. Place a checkmark next to 'Cumulative Percentage.' Click 'OK' to create the frequency distribution. It will appear as a new worksheet on the bottom of the Excel window. Click on the worksheet to see the frequency distribution and its associated histogram.

How to Add DataPig Buttons to the Excel 2007 Ribbon

1. Open Microsoft Excel 2007. Select the “Office” button and “Open” from the menu. Click on the file named “Custom_UI_Builder.xlsm.” Click the “Open” button.
2. Click the drop-down arrow in column “A” located under “Tab to Use.” Select the name for the Ribbon Tab where you want the button to appear.
3. Type a name for your tab in column “D” under “Tab Name” if you selected to put the button in “My Own Custom Tab” in column “A.” Otherwise, leave this cell blank.
4. Type group name in column “E” and a button label in column “F.” Select the button size using the drop-down box in column “G.”
5. Click the “Find Image” button. Select an image from the “MSO Image Finder” window.
6. Repeat Steps 2 through 5 in each row until you’ve defined the cells for every button you want to create.
7. Click the “Output Now” button. Select the radial button next to the type of output you want. Click the “Go” button.

How to Use a List Box in Excel

1. Select the cells on which you want to use the current list box.
2. Click 'Data' from the top menu; then select 'Validation' from the drop-down menu.
3. Select 'List' from the Allow drop-down menu.
4. Enter the data you want in the list boxes into the Source text box, separated by commas if you don't want to display options elsewhere on the worksheet.
5. Click the image of the grid with an arrow to take back to the spreadsheet, then highlight the data you want in the list box to create list box options from existing cells.
6. Check the 'In-cell Dropdown' box.
7. Press the 'OK' button to create the list box.

Wednesday, July 17, 2013

Filter Mode Is Stuck on Microsoft Excel

1. Select the column or columns, by clicking the letters at the top of the spreadsheet, that you have filtered. Click the 'Data' menu at the top of the window, move your mouse over 'Filter,' and select 'AutoFilter.' This will remove the filter from your data.
2. Click the 'Tools' menu, then select 'Options' from the drop-down menu. Select the 'Calculation' tab at the top of the Options window.
3. Click the box next to 'Manual,' and click 'OK' to close the window. Excel will no longer perform calculations automatically.
4. Select your data, and click the 'Tools' menu. Move your mouse over 'Filter,' and select 'AutoFilter.' Click the arrow at the top of the column that you want to you for filtering your data. Select your desired option, and the data will be filtered, but the message in the status box will tell you how many records you have filtered, instead of being stuck saying 'Filter Mode.'

How to Save on 2007 Excel for 2003 Without Macros

Save a Single File in Compatibility Mode
1. Launch Excel 2007 and open the document you want to save for Excel 2003 accessibility.
2. Click the 'Office' button, in the upper-left corner of the screen. This is the round button with the Microsoft Office logo on it, which turns gold when you hover over it with your mouse pointer.
3. Click 'Save As' and select a location to save your document on your computer.
4. Enter a file name in the 'File name' field.
5. Select 'Excel 97-2003 Workbook (*.xls)' from the drop-down menu in the 'Save as type' field.
6. Click 'Save.'
Set Compatibility Mode as Default
7. Launch Excel and click the 'Office button' in the upper left corner.
8. Select 'Excel Options' from the bottom of the menu window.
9. Select 'Save' from the left menu bar.
10. Choose 'Excel 97-2003 Workbook (*.xls)' from the drop-down menu in the 'Save files in this format' field.
11. Click 'OK.'

How to Auto Fill Rows in Excel

Sequences
1. Launch Microsoft Excel or create a new, blank worksheet to experiment with.
2. Click the first cell in the first column (Cell 'A1').
3. Type the number '1' in the cell and press 'Enter' to store the cell contents and move to the next cell in the column (Cell 'A2').
4. Type the number '2' in the cell and press 'Enter' to store the cell contents and move to the next cell in the column (Cell 'A3').
5. Type the number '3' in the cell and press 'Enter' to store the cell contents and move to the next cell in the column (Cell 'A4').
6. Click cell 'A1' and hold down the mouse button while dragging the mouse pointer down to cell 'A3.' Release the mouse pointer to select the first three cells.
7. Move the mouse to the lower-right corner of cell 'A3' while the three cells are selected until the mouse pointer changes to a small black cross with no arrows.
8. Click and hold the mouse button and drag the mouse pointer down the column for a few cells and release it. Notice that Excel automatically continued the sequence of numbers in the column based upon the selected numbers. The same feature also works with dates.
Formulas
9. Launch Microsoft Excel or create a new, blank worksheet to experiment with.
10. Click in the first cell in the second column (Cell 'B1') and type the word 'Rent' in the cell. Press the 'Tab' key to store the cell contents and move to the next cell in the row.
11. Type the word 'Power' and press 'Tab.' Repeat this process for the words 'Food' and 'Phones.'
12. Click in the second cell in the first column (Cell 'A2').
13. Type 'January, 2012' in the cell and press 'Enter' to store the value and move to the next cell down the column.
14. Click the cell 'A2' and point at the lower-right corner of the cell until the mouse pointer turns into a small black cross with no arrows.
15. Click and hold the mouse button and drag the mouse pointer down to cell 'A13' to fill in the date sequence automatically.
16. Type the number '750' in cell 'B2' to represent the rent for January of 2012. Press 'Tab' and type the number '75' to represent the power bill, and press 'Tab.' Repeat using '200' for food and '120' for phones.
17. Click cell 'B2' and hold down the mouse button. Drag the mouse pointer across to cell 'E2' and release the button.
18. Point the mouse pointer at the lower-right corner of cell 'E2' until the mouse pointer turns into a small black cross with no arrows.
19. Click and hold the mouse button down and drag the mouse pointer down to cell 'E13' to copy the values of all four columns to each of the months in the list.
20. Click cell 'F2' and click the 'AutoSum' button in the 'Editing' group on the home tab. Press 'Enter' to store the formula.
21. Point the mouse pointer at the lower-right corner of cell 'F2' until the mouse pointer turns into a small black cross with no arrows. Click and hold the mouse button down and drag the mouse pointer down to cell 'F13' to copy the formula for the sums of the rows.

Tuesday, July 16, 2013

How to Format Text As All Capitals in Excel 2007

Converting the Lower-Case Text to Upper-Case Text
1. Open your workbook in Excel 2007.
2. Click in the cell immediately below the cell containing lower-case text.
3. Type, without the quotation marks, '=UPPER(' and click the cell containing the text you want to capitalize, then type ')' and then press 'Enter.'
Replacing the Lower-Case Text with Upper-Case Text
4. Click the cell containing the new capital letters to select it.
5. Right-click, then choose 'Copy.'
6. Click the cell containing the original lower-case text, right-click again, then choose 'Paste Special....' Under 'Paste,' choose 'Values,' then click 'OK.'

How to Edit a URL in Excel With Macros

Replace Characters
1. Click the 'File' menu, click 'Options,' then click 'Customize ribbon.' Click the 'Developer' check box, then click 'OK' to close the 'Options' window. Word will display the 'Developer' tab.
2. Type the following formula into any cell in the current workbook. This formula creates a hyperlinked URL in the active cell.=HYPERLINK('http://www.whitehouse.net', 'White House')
3. Click the 'Developer' tab's 'Visual Basic' button to enter the VBA programming environment, then paste the following program into the window. This program edits a URL by using the VBA 'Replace' function. The first argument of this function is the string in which you want to make a replacement. The second argument is the text you want to replace and the third argument is the replacement text itself.Public Sub editURL()Dim URL, url2URL = ActiveCell.Formulaurl2 = Replace(URL, 'White House', 'Oval Office')ActiveCell.Formula = url2End Sub
4. Click any statement in the program, then click the 'Run' menu's 'Run' command to execute the program.
5. Click the 'Excel' icon on the taskbar to return to Excel. Notice that the 'Oval Office' text now appears in place of the original 'White House' text, indicating your program's editing of the URL was successful.
Replace Entire URL
6. Click the 'Developer' tab's 'Visual Basic' button to enter the environment for creating VBA programs.
7. Use the instructions from Step 2 of the previous section to enter any URL in a worksheet cell. For example, type '=hyperlink('http://www.whitehouse.gov', 'The Oval Office')'.
8. Paste the following program into the window. This program creates a completely new URL in the active cell.Public Sub editURL()ActiveCell.Formula = '=hyperlink(''http://www.nea.gov'', ''National Endowment for the Arts'')'End Sub
9. Click a statement in the program to select the program.
10. Click the 'Run' menu's 'Run' command to execute the program. Return the Excel by pressing 'Alt F11.' The 'White House' URL you typed in Step 2 is now replaced by a link to the home page of the National Endowment for the Arts.

How to Learn Excel Free

1. Enroll in Microsoft's Excel training courses. Microsoft offers free courses for Excel 2010 to Excel 2003. Courses are self-paced, and at the end of the course you will take a quiz to test your knowledge.
2. Subscribe to Excel training podcasts. On Apple's website, you can find many podcasts for Excel training. You will need iTunes installed on your computer before you can view and subscribe to the podcasts.
3. Sign up for HP Learning Center. The company Hewlett-Packard offers free courses on several software programs. They are in partnership with Microsoft and able to offer free courses in all Microsoft Office programs. Their Excel courses range from beginner to advanced.
4. Visit Baycon Group. Baycon Group's website is suited to people who prefer to print out their lessons. They offer four in-depth lessons in Excel 2007 and tutorials on Excel 2003 and Excel 97.
5. Get advanced training. If you're familiar with the basics of Excel, the website Excel Tip will take your knowledge of Excel to the next level. Excel Tip offers a free Excel 2007 course, plus hundreds of Excel tips and techniques.
6. Download Excel templates. These templates will not only show you what Excel can do but also are another free way to learn Excel features.
7. Visit a library. If you prefer books to computer screens and printouts, check your library to see if they have an Excel course book.

How to Use Two Pointers for Excel's Vlookup Function

1. Input the data into the range of cells you want to reference. For the sake of this example, we'll assume the data is in cells D10 through G15. Make sure the data in cells D10 through D15 are your index values (names of clients, for example).
2. Enter the following formula in cell C1: '=VLOOKUP(A1,D10:G15,B1,FALSE)'
3. Input an index value, matching one of the entries in D10 through D15, in cell A1.
4. Input a number between 1 and 5 in cell B1. Cell C1 will use the index value from A1, and the column number from B1, as two pointers to pull the data from the range you specified in step 1.

Monday, July 15, 2013

How to Calculate Days in Excel 2007

1. Open a new blank Excel spreadsheet.
2. Place a date in cell A1. Use the format mm/dd/yyyy and make sure the date is the year 1900 or later. For this example, you could use '07/04/2011.' In an existing spreadsheet, the format of the cell may not be 'Date.' To change the format to 'Date,' click 'Format/Cells...' and select the Number tab and then click 'Date' under 'Category:.'
3. Place a date in cell A2. For this example, you could use '07/24/2010.'
4. Type the following formula in cell A3: '=A1-A2' then click 'Enter.' Cell A3 will contain the number of days between the two dates. In this example, the answer will show 365. If cell A3 shows a date instead, change the cell format to 'Number' and select 'Format/Cells...' Click the Number tab and then select 'Number' under 'Category.'

How to Make Blank Rows Between Populated Rows in Excel

1. Double-click the Excel file into which you want to insert a new row to open the file in Excel 2010.
2. Click the row heading at the left side of the window for the bottom row of the pair that you want to split. For example, if you want to insert a row between rows 1 and 2, click the number '2' at the left side of the window. After clicking the row heading, the entire row should be highlighted in blue.
3. Click the 'Home' tab at the top of the window.
4. Click the 'Insert' drop-down menu in the Cells section of the ribbon at the top of the window.
5. Click 'Insert Sheet Rows.'

How to Create Forms Using Excel

1. Open a workbook in Excel. If you know which workbook you want your form to belong to, then now is the time to open it. Use the File menu to access the workbook.
2. Click on the 'Tools' menu, select 'Macro' and click on 'Visual Basic Editor.'
3. Click 'Insert,' and then select 'UserForm.'
4. Drag a command button onto the form. You need at least three of these for this example.
5. Put names on your command buttons and labels. Click 'View' and 'Properties Window.' Click on one of the command buttons that you placed on the form.
6. Name the command button that will read 'OK' by clicking on the name setting in the Properties window and typing 'cmdOK'. Click on the 'Caption' setting in the Properties window and type 'OK'.
7. Click on another command button. Name this one 'cmdCancel,' and set the caption to read 'Cancel' in the Properties window.
8. Click on the third command button, name it 'cmdClearForm' and change the caption setting to read 'Clear Form.'
9. Hit the 'F7' function key on your keyboard to bring up the code window.
10. Click on the drop-down lists at the top of the code window. Click on the top-left list to open the subprocedure named 'User Form' and click on the other drop-down list to select 'Initialize.'
11. Delete the subprocedure listed above that reads: UserForm_Click() procedure.
12. Type the following code into the code window (do not type over the blue text; just add the black text):
Private Sub UserForm_Initialize()
txtName.Value = ''
txtPhone.Value = ''
With cboDepartment
.AddItem 'Employees'
.AddItem 'Managers'
End WithYourCourse.Value = ''
optIntroduction = True
chkWork = False
chkVacation = False
txtName.SetFocus
End Sub
13. Enter your code into the Cancel button. Double-click the Cancel button to open the code window. Type your code so that the code window reads: Private Sub cmdCancel_Click()
Unload Me
End Sub
14. Open the code window for the Clear Form button. Remember to double-click the 'Clear Form' button.
15. Type your code into the code window so that it reads:
Private Sub cmdClearForm_Click()
Call UserForm_Initialize
End Sub
16. Double-click the 'OK' button to add the following code in the code window:Private Sub cmdOK_Click()
ActiveWorkbook.Sheets('YourWork').Activate
Range('A1').Select
Do
If IsEmpty(ActiveCell) = FalseThen
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = txtName.Value
ActiveCell.Offset(0, 1) = txtPhone.Value
ActiveCell.Offset(0, 2) = cboDepartment.Value
ActiveCell.Offset(0, 3) = cboCourse.Value
If optIntroduction = True Then
ActiveCell.Offset(0, 4).Value = 'Intro'
ElseIf optIntermediate = True Then
ActiveCell.Offset(0, 4).Value = 'Intermed'
Else
ActiveCell.Offset(0, 4).Value = 'Adv'
End If
If chkLunch = True Then
ActiveCell.Offset(0, 5).Value = 'Yes'
Else
ActiveCell.Offset(0, 5).Value = 'No'
End If
If chkWork = True Then
ActiveCell.Offset(0, 6).Value = 'Yes'
Else
If chkVacation = False Then
ActiveCell.Offset(0, 6).Value = ''
Else
ActiveCell.Offset(0, 6).Value = 'No'
End If
End If
Range('A1').Select
End Sub

How to Use Excel for an Inventory Accounting System

1. Click the 'Microsoft Office' button, and then click 'New.'
2. Click 'Inventories' from the list in the left-hand column.
3. Double-click the inventory sheet you would like to use. For example, you could choose 'Book Inventory' or 'Parts Inventory.' The inventory will download and open automatically in Excel. There are dozens of templates you can choose from, including inventories for home and business. The actual number of templates you can access will depend upon your version of Excel and if you have downloaded any templates from the Internet.
4. Click on any items contained within brackets to customize the spreadsheet. For example, you might click [Company Name] to enter your company name.
5. Follow the instructions on the template to fill in the inventory list. Once you have populated the template with your items, Excel will use built-in template formulas to calculate the number of items in your inventory. For example, a home inventory template may have an auto-summation formula in the 'Total Items' box of your worksheet, which updates automatically with each entry you make into the spreadsheet.

Subscribe to: Posts (Atom)