How to Customizing the Excel Status Bar

1. Right click anywhere on the Excel status bar to bring up the 'Customize Status Bar' window.
2. Click on the left side of the Customize window next to the items that you want to add or remove from the status bar. Any field that has a check mark next to it is active and will show up on the Excel Status bar. Remember that many of the items on the list, like caps lock or permissions, will only show up on the status bar if they are turned on, so placing a check next to them might not show any immediate results.
3. Click anywhere outside of the Customize window to finish customizing the status bar and return to your work. The status bar is not bound to the worksheet, so the changes you made to the status bar will remain even in a new project.

Read more ►

How to Shrink Columns in Microsoft Excel 2003

1. Select the column you want to shrink. Left-click on the column letter to activate and select that column.
2. Shrink the column using the column width menu. Right-click on the activated column and select “Column Width.” Enter the desired column width in pixels and then click 'OK' to change.
3. Select the column you wish to shrink manually. Left-click on the column letter that you wish to shrink manually to activate that column.
4. Shrink the column manually. Hang the cursor over the edge of the column borders at the top of the column until an arrow appears. Then left-click and hold as you drag the column to shrink it.

Read more ►

How to Divide an Excel Spreadsheet

Freeze Panes
1. Double-click on the Excel file that you want to work with in order to open it up in the Microsoft Excel program.
2. Click on the cell that is below and to the right of the point where you want to freeze the panes. If you only want to freeze the top row or leftmost column, you don't need to select any cell.
3. Select the 'View' tab on the toolbar at the top of the Excel window. Find the 'Window' group within that toolbar. If your Excel window is very small, you may have to click on the 'Window' button to view all the buttons in the group.
4. Click on 'Freeze Panes' to open up a drop-down menu.
5. Select 'Freeze Panes' from the drop-down menu to cause everything positioned up and to the right of your selected cell to freeze in place. You can also choose 'Freeze Top Row' or 'Freeze First Column.'
Split Panes
6. Open the Excel file that you want to work on.
7. Select a cell anywhere in the middle of your worksheet. The panes will be created above and to the left of this cell, but the panes are easy to adjust so it doesn't have to be perfect.
8. Click on the 'View' tab and find the 'Window' group in the toolbar. Click the 'Split' button to divide the worksheet into four panes.
9. Double-click on either the horizontal or the vertical separator to remove it, if you only wanted to divide your worksheet into two panes instead of four.

Read more ►

How to Write a VBA Script

1. Open the Microsoft Office application that you want to write a script for. For example, open Microsoft Excel, Word or PowerPoint.
2. Click on the 'Developer' tab and then click on 'Visual Basic.' The Visual Basic Editor (VBE) screen will open. Alternatively, you can press the 'Alt' and 'F11' keys to open the VBE.
3. Click on the 'Insert' tab and then click on 'Module' to open a blank window. This is where you will type your script.
4. Type your script into the open window. You should start by typing 'Function [function name]()' if your script is a function or 'Sub [subroutine]()' if your script is a subroutine. Type the body of your script, then end with either 'End Function' or 'End Sub.' Use a function if you want to return something (like a value) or a subroutine if your script performs a task without returning anything (like formatting a document). Microsoft's website offers an extensive help section for writing VBA code.
5. Press 'F5' to run the script.

Read more ►

How to Use Excel Countif

1. Select the desired cell on the spreadsheet to display the calculated formula.
2. Type '=countif(' and select the range of cells to be evaluated by clicking the first cell, holding down the mouse button and dragging the cursor to the last cell before releasing the mouse button.
3. Type ',' and then identify the criteria for counting each cell's content as 'TRUE' for one point, making sure to enclose characters between quotation marks. For example, type 'purple' within quotation marks to count those cells containing the word; type '>9' within quotation marks to count the number of cells containing a number above nine; type A2 to count those cells containing information matching the contents in cell A2.
4. Type ')' and then press the 'Enter' key to complete the formula.

Read more ►

How to Create an Option Group in MS Excel

1. Display the Developer tab if it is not already visible. To turn this tab on, click the Microsoft Office Button (a round button with the mutli-colored Office logo) and choose 'Excel Options'. In the section called 'Top Options for Working with Excel', find the 'Popular' category. Place a check next to 'Show Developer Tab' in the Ribbon checkbox and click 'OK'.
2. Access the Developer tab and enter the 'Controls' section, then click the 'Insert' menu.
3. Select the 'Form Controls' option inside the 'Insert' menu, then click on the 'Option' button.
4. Click the cell on the spreadsheet that you want to contain the option button.
5. Launch the 'Properties' setting from the 'Controls' group in the 'Developer' tab.
6. Configure the settings for the new option button using the fields in the 'Properties' area. The 'Value' option allows a default state for the button that remains in effect until the user changes it. An option button is either on or off, so the 'Value' field accepts these two states as 'Checked' or 'Unchecked'.Each option button in a group carries a numerical value, with the first button equaling the value 1. The 'Cell Link' field copies the value of the option button as numerical data stored in any cell. Identify the cell using standard Excel cell referencing. For example, A1 is the upper left cell in a worksheet.
7. Add a checkbox to the option group in the same manner as the option button, if desired. In the 'Form Controls' option inside the 'Developer' tab's 'Insert' menu, click on the 'Checkbox' button.
8. Add more option buttons or checkboxes until the option group is complete.

Read more ►

How to Insert a Zip File Into Excel

1. Open the Excel file that you want to insert a Zip file into.
2. Select the worksheet you want to add the Zip file to and click the 'Insert' tab located in the Excel ribbon.
3. Click 'Object' from the 'Text' section of the ribbon. The 'Object' window will open.
4. Select 'Create from File' and then click 'Browse' so you can search for the Zip file you want to insert.
5. Click the Zip file you want to insert in your Excel workbook and then click 'Open.'
6. Click 'OK' to insert the Zip file you selected. The Zip file will show up as a folder icon in your Excel spreadsheet.

Read more ►

How to Create a Form in Excel 2003

1. Open Excel 2003 from your 'Start' menu.
2. Click the 'New' button that looks like a blank sheet of paper. Delete 'Sheet 2' and 'Sheet 3' by right clicking on the Tab, and then select 'Delete.'
3. Enter any questions and instructions you want in the corresponding cells.
4. Input any calculations you want for the form.
5. Change the font, color, highlighting and formatting to fit your preferences.
6. Save the form as a template. Print the form if you desire by clicking the 'Print' button.

Read more ►

How to Open Excel 2007 Files As Separate Windows

1. Open Microsoft Excel.
2. Click the 'Office' icon located in the upper-left corner of Microsoft Excel.
3. Click 'Excel Options.'
4. Select 'Advanced.'
5. Scroll down to 'Display.'
6. Check the box next to 'Show all windows in taskbar.'

Read more ►

How Do I Vertically Center in Excel?

Instructions
1. Type some text into a cell. Keep the cursor on this cell.
2. Select 'Format' from the top menu bar. The top menu bar includes all the commands.
3. Select 'Cells' from the drop-down menu.
4. Select the 'Alignment' tab from the 'Format Cells' menu that pops up. The alignment tab sits between the 'Number' and 'Font options
5. Select 'Vertical' from the 'Text Alignment' menu. Look for the 'vertical' option right below the 'horizontal' option.
6. Select the vertical alignment position you want for your text. For instance, when you click the 'vertical' option, a drop-down list will show four options: Top, Center, Bottom and Justify. If you want your text to rest along the top of the cell, click 'top. If you want the text to align in the very center of the cell and have a slight margin around all the edges, select 'center.' If you want the text to vertically along the bottom of the cell, select 'bottom.' If you want to distribute the text evenly throughout the cell, select 'justify.
7. Click the 'OK' button and save your preferences.

Read more ►

How to Alphabetize an Excel Spread Sheet

1. Highlight the data you want alphabetized (including the headers). If the employee payroll spreadsheet has columns for 'First Name,' 'Last Name,' 'Hours Worked' and 'Pay Drawn,' and you want to sort data in an alphabetic order by 'Last Name,' then you will have to highlight data under 'Last Name' as well as 'First Name,' 'Hours Worked' and 'Pay Drawn.' If you don't highlight data in other columns, i.e. 'First Name,' 'Hours Worked' and 'Pay Drawn,' then the information in the 'Last Name' would be alphabetized, while information in other columns would not be sorted, and you'd end up jumbling all the data.
2. Select 'Data' from main menu bar and click 'Sort.'
3. Select 'Header Row' in the small new window, if your top row is a header row and you do not want it alphabetized with the other rows. Select 'No Header Row' if otherwise.
4. Select the column header you want to sort data by in the 'Sort by' list of the same window. Here, we want to sort all information by 'Last Name.'
5. Select 'Ascending' if you want to sort this information in an ascending order.
6. Click 'OK' at the bottom of the window. The spreadsheet will be sorted alphabetically.

Read more ►

How Can I Sort Dates on an Excel Spreadsheet by Months Instead of Years?

1. Insert a blank column to the left of the column that has your date range. Go to the top menu and select 'Insert' and choose 'Columns.'
2. Click the first blank cell that is next to your date cell and type =TEXT( cell number,'MMDD'). For example, if your first date range is B4 and your date range is 03/25/1970, your formula would be =TEXT(B4, '0325').
3. Copy the cell with the formula and highlight the remaining blank cell and click 'Paste.' You will see just the months of your date range for each cell.
4. Sort your data by the new-month column. Go to the top menu; click 'Data' and then 'Sort.' The sort dialog box will pop up and continue to select the 'Expand the Selection'option to sort a series of columns or rows. Click 'OK' in the sort dialog box. Your data range is now sorted by month.

Read more ►

How to Use Inches in Excel

1. Select the cells that you would like to format as inches. To do this, click and drag to highlight the cells. You can also click the letters at the top to highlight entire columns or the numbers on the side to highlight entire rows.
2. Right-click the selected cells to open the context-sensitive menu. If the cells contain numbers or are empty, one of the options will be 'Format.' Select 'Format.'
3. Select the 'Number' tab in the Format Cells screen to show settings for number cells. In most cases, this will be the default tab when the Format Cells screen opens.
4. Scroll down, and choose 'Custom' from the category list on the 'Number' tab. This will show options for setting up a custom format for numbers that is not provided by any other options.
5. Type '#0 [$ inches]' to format the selected cells as inches. If you would rather the cells show the abbreviation of inches, use '#0 [$ in].'

Read more ►

How to Calculate Macros in Excel

1. Click the 'File' menu's 'Options' button, then click the 'Formulas' link. Click the 'Manual' option, then 'OK' to close the 'Options' dialog box. This step turns off automatic calculation, which your macro will restore.
2. Type the following into cells A1 through C1 of the current worksheet: 5, 10, '=a1*a2.' Notice that the formula in C1 is incorrect, due to your selection of the manual option in step one. Your macro will restore automatic calculation.
3. Click the 'Developer' tab's 'Visual Basic' button to enter the Visual Basic programming environment. Paste the following program into the window that appears. This program uses the 'Range' object to enter values in two different worksheet cells, and a formula into another cell. The program has two statements that force the current worksheet to update its calculation. The first is 'Application.Calculation = xlCalculationAutomatic.' This statement sets the 'Calculation' property of the Excel application itself. The second is the one calling the 'Calculate' function of the 'ActiveSheet' object. This statement tells only the active sheet to calculate, not the entire workbook.Public Sub recalc()'Enter numbers in a cell'Enter a calulation that depends on those numbers'Force recalculationApplication.Calculation = xlCalculationAutomaticRange('a1') = 5Range('a2') = 10Range('a3').Formula = '=a1*a2'ActiveSheet.CalculateEnd Sub
4. Press 'F5' to run the program.
5. Click the 'Excel' icon on the Windows taskbar to return to Excel. Notice that the cell A3 has a function that requires updated calculation, and that the result of the calculation, the product of the worksheet's top two cells, is accurate.

Read more ►

How to Add Two Email Hyperlinks to One Cell in Excel

Adding Multiple Email Hyperlinks in Excel 2003
1. Launch the Microsoft Excel 2003 application.
2. Create a new spreadsheet or open an existing sheet in which you wish to add the email hyperlinks.
3. Configure the desired cell to a size that can easily accommodate two lines of text. This can be accomplished by clicking on the side of a row or column heading that you wish to expand and dragging it in the direction of expansion. For example, if you want to adjust cell B2, you would click on the right border of the column B heading and drag it to the right. The actual width will depend on the length of the email address. You would then click on the bottom border of the row 2 heading and drag it down. A height of 50 should easily accommodate two rows of text.
4. Click the 'View' option at the top of the screen.
5. Choose the 'Toolbars' option, then select the 'Drawing' option.
6. Select the text box from the bottom toolbar.
7. Click on your cell and configure the shape.
8. Type the email address into the text box.
9. Repeat steps 7 and 8 for the second text box, making sure the boxes are not overlapping. Your email hyperlinks will appear automatically.
Adding Multiple Email Hyperlinks in Excel 2007
10. Launch the Microsoft Excel 2007 application.
11. Create a new spreadsheet or open an existing sheet in which to add the email hyperlinks.
12. Configure the desired cell to a size that can easily accommodate two lines of text. This can be accomplished by clicking on the side of a row or column heading that you wish to expand and dragging it in the direction of expansion. For example, if you want to adjust cell B2, you would click on the right border of the column B heading and drag it to the right. The actual width will depend on the length of the email address. You would then click on the bottom border of the row 2 heading and drag it down. A height of 50 should easily accommodate two rows of text.
13. Select the 'Insert' tab, then click the 'text box' option. Configure the shapes of the boxes within your cell so that they are not overlapping.
14. Highlight each email address, then right-click it and choose 'Hyperlink.' Click the 'email address' option on the bottom-left and type in the email address.

Read more ►

How to Anchor Columns in a Spreadsheet

1. Open the Excel program and open the desired spreadsheet or enter data to create a new spreadsheet.
2. Select the column or columns that are to remain visible when scrolling. To select a column, click on the letter at the top of the column. The column will then be highlighted showing it has been selected.
3. After selecting a column when using Excel 2007, click on the view tab on the toolbar. Once the view tab is open, in the Windows group, click on the arrow that is located below the freeze panes. To anchor only one column, click on Freeze First Column. To anchor more than one row, click on Freeze Panes. To anchor columns in other versions of Excel, such as 2003, select the column and click on the Windows tab on the toolbar and click on Freeze Panes.
4. When the column or columns need to be unanchored, click Unfreeze Panes in the Window menu.

Read more ►

How to Make Text Appear as Typed in Excel

1. Open the Excel 2010 worksheet in which you want to enter your information.
2. Click on the top-left cell in the area where you want to alter the cell's format. Hold shift and then click the bottom-right cell in the area. If desired, you can also click the letters above the columns or the numbers to the left of the rows to select an entire column or row, respectively. Finally, to alter the format for the entire worksheet, click the button that sits to the left of the 'A' and above the '1' in the upper left corner of the sheet.
3. Click the 'Home' tab at the top of the screen. Locate the 'Number' area of the ribbon and click on the drop-down box found there.
4. Choose 'Text,' which will be at the bottom of the list that appears. Once you click on this option, your selected cells will display exactly what was typed into them.

Read more ►

How to Convert Numbers to Text in Microsoft Excel

1. Open a new workbook in Microsoft Excel.
2. Type the list of numbers you would like to convert to text down a column.
3. Type =Text(cell,'units') in the next empty column or row, where 'cell' is the cell containing the number you want to convert and 'units' is the unit you want the number to be expressed in. For example, if you have 10 in cell A1 and want to express it in U.S. dollars, type =text(A1,'$0.00') in cell B1.
4. Click on the cell containing the formula and go the lower right-hand corner of the cell until your mouse pointer turns into sign. Drag the formula down the column to the last row containing data.

Read more ►

How to Create a Drop

1. Select the data you want the drop-down menu to contain. This can be any list of information. Highlight those cells and click the 'Data' menu. Select 'Sort' and 'A-Z.' This will alphabetize your list.
2. Place your cursor in the cell where you want your drop-down list located. Click the 'Data' menu and the 'Data Tools' menu. In the 'Data Tools' menu, select 'Data Validation.'
3. Choose 'List' in the 'Allow' menu of the 'Data Validation' dialog box. Click the arrow beside the 'Any Value' option to view the options where 'List' will be found.
4. Click the button at the very end of the 'Source' entry box. This will generate a 'Data Validation' dialog box. Select the worksheet where the data is located for your list. Highlight all of the cells you want included in the drop-down list. Click the button at the end of the 'Source' entry box to select the cell range. If you do not want the list to be left blank, you should clear the check mark in the the 'Ignore Blank' check box in this menu.
5. Select the 'Input Message' tab to set an input message that will appear when the list is selected. This will allow you to provide people with an understanding of what you would like them to select in this menu.
6. Select the 'Error Alert' tab to set an alert to notify users if they have improperly populated the list or failed to select an option.
7. Select 'OK' to populate and publish the drop-down list.

Read more ►

How to Use a Chart Wizard in MS Excel 2007

1. Open the Excel workbook with the data that you wish to chart.
2. Select all the cells containing the relevant data. Press 'CTRL' and 'A' to select them all.
3. Select 'Chart' from the Insert menu. This will open the Chart Wizard.
4. Select a chart type under 'Standard Types.' If you are unsure how a specific chart will represent your data, click it to view a preview. Click 'Next' when you are done.
5. Confirm the data range to include in your chart. The information is already filled in to show the cells you selected in Step 2, but you can switch around the order in which your spreadsheet's rows or columns appear on the final chart. Click 'Next' when you are done.
6. Choose from the wide range of chart options in the next window. They are divided into six tabs: Titles, Axes, Gridlines, Legend, Data Labels and Data Table. If you have the 'Preview' box checked on these pages, you can see what various options will look like. Note that users trying to create basic charts can leave these options at their default settings; they can be changed later if need be. Click 'Next' when you are done.
7. Choose whether to place your chart in the existing workbook or to create a new document. Click 'Finish' when you are done.

Read more ►

How to Add a Header and Footer in Excel

1. Create or open the file you will be working with. A header and footer can be changed in an existing file or added to a new or existing file.
2. Determine if you will use a header, footer or both. Also decide what you want each of these to look like and say. These steps show how to add both a header and footer so choose the steps suitable to what you need.
3. Go to 'File' in the menu bar and select 'Page Setup.' A new box with options will appear. If you do not see 'Page Setup' in your options from the drop-down menu, click the arrow at the bottom of the 'File' list to see more options.
4. Click the tab that says 'Header/Footer,' and then the 'Custom Header' button. Type the words you want displayed into the appropriate box (or boxes) for where on the top of page you want the header to display (left, center or right). Once you have entered the words, click the 'A' to change the font to whatever type, size and color you want these words to be displayed as and click 'OK' on both the font and header boxes. Each word or section of words (left, center or right) can have its own font selection.
5. Include a footer by clicking the 'Custom Footer' button of the 'Header/Footer' tab. Again enter and format the text where you wish it to be displayed. You can automatically add the page numbers, date, time, file path, file name or tab name by using the buttons. Note that a code automatically appears depending on what you choose. You should not edit that information, but you can include text before or after that code.
6. Select the 'OK' button once done formatting the footer. Click the 'Print Preview' button to verify that you header and footer look the way you want. Edit them as needed and click 'OK' to add them to your document. Save the file by selecting 'Save' or 'Save As' from the 'File' menu.

Read more ►

How to Create a Timeline Using Word

Create a Timeline in Word 2003
1. Open the Word document on which you want to create a timeline. Go to the 'File' menu, click on 'Page Setup' and go to the 'Margins' tab. Select the 'Landscape' orientation option and click 'OK.'
2. Go to the 'View' menu, point to 'Toolbars' and select 'Drawing' if it is not already selected. Click the rectangle auto shape in the drawing toolbar and draw a long, narrow rectangle horizontally onto the Word document, leaving room above and below it for text.
3. Right-click the shape and select 'Format AutoShape.' The 'Format Shape' dialog box will open. Go to the 'Colors and Lines' tab of the dialog box. Select a color from the 'Fill Color' drop-down menu.
4. Click 'AutoShapes' on the 'Drawing' toolbar. Point to 'Lines' and select a straight line. Draw the line coming up from the rectangle in the first position you want at the beginning of the timeline. Repeat, drawing the next line downward from the rectangle. Alternate each mark on the timeline.
5. Go to the 'Insert' menu and select 'Text Box.' Draw a text box above the first line coming out of the rectangle. Type a date or time and other pertinent information in the box. Repeat for the remaining points on the timeline.
6. Go to the 'Insert' menu, point to 'Picture' and select 'From File' or 'Clip Art' to add graphics to points on the timeline. Save and print it as desired.
Create a Timeline in Word 2007 or 2010
7. Open the Word document on which you want to create a timeline. Go to the 'Insert' menu and select 'SmartArt.' The 'SmartArt Gallery' will open.
8. Click 'Process' in the list of 'SmartArt Types.' Hover your mouse over the gallery options or click on an option to see its name and description. Select 'Basic Timeline' and click 'OK.' A timeline graphic will appear along with a corresponding text pane.
9. Click on the first text entry in the text pane. Enter the date or time and other pertinent information in the box. Repeat for the remaining points on the timeline.
10. Add an entry to the timeline by selecting the entry before or after the desired new entry. Right-click that entry, point to 'Add Shape' and select 'Add Shape After' or 'Add Shape Before.'
11. Click on the timeline and go to the 'Design' tab in 'SmartArt Tools.' Click 'Change Colors' to select a different color scheme. Select an option in the 'SmartArt Styles' gallery to change the appearance of the timeline.
12. Go to the 'Insert' tab and select 'Picture' or 'Clip Art' to add graphics to points on the timeline. Save and print it as desired.

Read more ►

How to Print Bar Codes in Microsoft Excel

1. Launch Microsoft Excel from the Microsoft Office folder in your Start menu.
2. Click the 'Tools' drop-down menu. Select the 'Macro' tab, and choose 'Security.' Set it to 'Medium.' If you use Office 2007, click on the Microsoft Office button at the top left corner and select 'Excel Options.' Click on 'Trust Center Settings' under the Trust Center category. Click on 'Macro Settings.' Put a check mark next to 'Enable all macros.'
3. Click the 'File' drop-down menu and select 'New.' Select 'Enable Macros' when you get a prompt. For Excel 2007, click on the Office button and select 'New.'
4. Enter the formula for your specific bar code in Column A, which is the first column. Make sure to use only numbers and/or letters while developing your bar code formula. Also, do not add any spaces between the characters.
5. Increase the width of Column B, the column next to the first column from last step. Click on the line that divides Column B and Column C and drag it to your right until you have enough space for the bar code.
6. Double-click on the formula you developed and type it in Column A from Step 4. Right-click on it and select 'Copy.' Right-click on the next cell in Column B and select 'Paste.'
7. Double-click to select all the formula in Column B. Select the 'Font' drop-down menu. Choose the font that says 'Bar code.' This will transform the formula into a bar code.
8. Select the cell with the bar code. Click the 'File' drop-down menu and select 'Print...' For Excel 2007, click the Office button and select 'Print.' Click 'Print...' again.
9. Choose 'Selection' under the Print What sub-category. Click 'Print' to end the task.

Read more ►

How to Print Mail Labels From Excel

1. Open Excel 2010 and select the 'Mailing' tab on the ribbon. Select 'Start Mail Merge.' Click 'Labels.' Select the label vendor and product number. Click 'OK.' A grid of the label appears in your Word document.
2. Click 'Select Recipients.' Select 'Use Existing List' from the drop-down list. Browse your files and locate the Excel workbook containing the mail labels. Click the workbook and select the worksheet containing the mail label information. Click 'OK.'
3. Format the labels by clicking the 'Insert Mail Merge' field. From the drop-down list, select a field name. It appears in the first label in the document. Add a space or additional formatting as necessary. Press the 'Enter' key to advance to the next line in the label.
4. Click the 'Preview Results' button to see the labels. Click 'Update labels' to apply the new label format to all of the new labels. Select 'Finish and Merge' to print the labels. Select 'Print Records' and 'All' to finalize the print process. Click 'OK' and review your printed mail labels from your printer.

Read more ►

How to Transform a Spreadsheet into a Web Page in Excel 2003

1. Fill out your Excel 2003 worksheet. You will create the Web page from this version of the worksheet.
2. Format your worksheet. Make sure that your worksheet is formatted so it looks good enough to appear on the Internet. Save the file without exiting.
3. Under 'File' from the toolbar at the top of the Excel window, click 'Web Page Preview' on the dropdown menu. This will preview how the page will appear online. You may have to make formatting changes; if so, preview until you're finished.
4. Once the preview satisfies you, click 'Save as Web Page' under 'File.' This will save your spreadsheet as an 'htm' file so people can view it on the Internet.

Read more ►

How to Evaluate Multiple Columns in Excel 2003

1. Choose a formula or function that you want to use. For example, use the 'Sum' function to add columns or the 'Average' function to calculate the average of all the numbers.
2. Type the function into a blank cell. For example, type 'Sum(' without quotes to start the Sum function.
3. Type the column references, separated by a colon, then close the function with a closing parenthesis. For example, type 'a:b)' without quotes. Your function should look like '=Sum(a:b)'.
4. Press the 'Enter' key or click out of the cell.

Read more ►

How to Make an Invoice Using Excel

1. Navigate to the Excel templates website (see References below for link).
2. Select a template option and examine it for relevance to your needs. For example, click on 'Basic Invoice,' 'Sales Invoice,' or 'Service Invoice.' This brings up a window with a 'Download' button. Click on the button to download the template to your computer.
3. Locate the downloaded file on the computer by using Windows Explorer. To run Explorer, click on the 'Windows' button in Windows 7 or Vista or the 'Start' button in Windows XP, then click on 'Run.' Type 'Explorer' into the text box, then click 'OK.'
4. Double-click on the template file. The template will open up in Excel automatically, and you can then tailor the invoice to fit your needs by changing the business name, amount and other categories. When finished customizing the template, save it to the location of your choice on the computer for future use.

Read more ►

How to Find Standard Deviation on Excel 2007

1. Open Excel 2007 and select the 'Formulas' tab. Click the 'Statistical' button. A drop-down list appears.
2. Click 'Stdev.' The Standard Deviation function arguments window appears.
3. Type the first value in your standard deviation function in the 'Number 1' field. Type the second value in your standard deviation function in the 'Number 2' field. Click 'OK.' The standard deviation function is displayed in the cell.

Read more ►

How to Make a Stem Leaf Plot in Excel 2007

1. Rename a blank Excel worksheet “Data” by clicking the bottom tab for the worksheet and typing the new name.
2. Rename another blank Excel worksheet in the workbook “Stem” by clicking the bottom tab for that worksheet and typing the new name.
3. Enter your list of numbers in column A of the “Data” worksheet.
4. Press “Alt” and “F11” at the same time to open the visual basic editor.
5. Double click “This Workbook” under Microsoft Excel Objects in the left navigation pane to open a blank code window.
6. Paste the following VBA code into the blank window:
Sub StemAndLeaf()
dataColumn = 1'Clean everything out of the Stem worksheet.
Worksheets('Stem').Cells.Clear'Look at the Data worksheet.
Worksheets('Data').Activate'Find the maximum value.
rowPointer = 2
Do Until Cells(rowPointer, 1).Value = ''
rowPointer = rowPointer 1
Loop
Maximum = Cells(rowPointer - 1, dataColumn).Value'Set the divisor to strip off leaves.
divisor = 1
Do Until Maximum / divisor
divisor = divisor * 10
Loop'If the first digit of the largest value is less than 5, then
'use a smaller divisor.
'Otherwise you could end up with four or fewer rows in the plot.
If Fix(Maximum / divisor)
topStem = Fix(Maximum / divisor)'Set up the Stem worksheet.
Worksheets('Stem').Activate
Cells(1, 1).Value = 'Count'
Cells(1, 2).Value = 'Stem'
Cells(1, 3).Value = 'Leaves'
For rowPointer = 2 To topStem 2
Cells(rowPointer, 2).Value = rowPointer - 2
Cells(rowPointer, 3).Value = '|'
Next rowPointer'Calculate the counts.
'The following code is slower than it needs to be,
'but a faster code would be harder to read and understand.
Worksheets('Data').Activate
rowPointer = 2
Do Until Cells(rowPointer, dataColumn).Value = ''
measurement = Cells(rowPointer, dataColumn).Value
Stem = Fix(measurement / divisor)
Worksheets('Stem').Cells(Stem 2, 1).Value = Worksheets('Stem').Cells(Stem 2, 1).Value 1
rowPointer = rowPointer 1
Loop'Calculate the shrink factor.
Worksheets('Stem').Activate
maximumCount = 0
For rowPointer = 2 To topStem 2
If Cells(rowPointer, 1).Value > maximumCount Then
maximumCount = Cells(rowPointer, 1).Value
End If
Next rowPointershrinkFactor = Fix(maximumCount / 50)
If shrinkFactor
Cells(1, 4).Value = 'Each digit represents' Str(shrinkFactor) ' cases.''Return to the data, and fill the leaves in light of the values in the data.
Worksheets('Data').Activate
rowPointer = 2
Do Until Cells(rowPointer, dataColumn).Value = ''
measurement = Cells(rowPointer, dataColumn).Value
Stem = Fix(measurement / divisor)
leaf = measurement - Stem * divisor
leaf = Fix(leaf * 10 / divisor)Worksheets('Stem').Cells(Stem 2, 3).Value = Worksheets('Stem').Cells(Stem 2, 3).Value Trim(Str(leaf))
rowPointer = rowPointer shrinkFactor
Loop'Get to the Stem worksheet.
Worksheets('Stem').Activate
End Sub
7. Press “F5” to run the code. Your Stem and Leaf plot will appear in the “Stem” worksheet.

Read more ►

How to Reduce the Size of a Large Excel 2007 File

1. Press 'Ctrl' and 'End' at the same time. This will bring you to the bottom right cell of the worksheet.
2. Select all the rows between your real last used row, and the row that Ctrl End brought you to. To select a row, click on the row identifier (for example, 'A.'). To select multiple rows, hold down the 'Shift' key and click the first and last row. Press 'Delete.'
3. Select all the columns between your real last used column, and the column that Ctrl End brought you to. Press 'Delete.'

Read more ►

How to Use Scatter Plots in Excel

1. Open the Excel 2010 file containing the data you want to make into a scatter chart.
2. Arrange the data so the first row for each column has a header in the top row. The header is just a word that describes the data. If you need to add a row to accommodate the headers, right-click the number on the left side of the top row and select 'Insert' from the pop-up menu.
3. Arrange the information so that the leftmost column holds the values you want to use on the X-Axis. You can move a column by right-clicking on the letter at the top of the column and choosing 'Cut' from the pop-up menu. Then, right-click on the letter at the top of the first column and choose 'Insert Cut Cells.'
4. Select any cell within your data table. Click 'Insert' at the top of the screen. Find the 'Charts' area of the ribbon and click the 'Scatter' button. Choose one of the five different scatter chart types, which include 'Only Markers,' showing just the intersection of the data fields; 'Smooth Lines,' with a curved line running from one data point to another; and 'Straight Lines,' with a jagged line running between the data points. Smooth- and straight-line scatter charts are available with and without markers. Click the chart type and Excel will create the scatter chart.
5. Click the 'Quick Layout' button on the 'Design' tab to get a list of pre-set layouts for your chart. These layouts will control how the chart appears by adding axis titles, showing a number grid and displaying the marker data as numbers, as well as a few additional options. You can fine-tune the chart's appearance even more by selecting the 'Layout' or 'Format' tab and modifying the options from the ribbon.

Read more ►

How to Generate a Random Number in a Range in Excel

1. Start Microsoft Excel 2007, and open an existing spreadsheet from your files or create a new blank spreadsheet into which you want to generate a random number within a range that you designate.
2. Select the cell or multiple cells that you want to generate the random number into. You can use the SHIFT or CTRL keys on your keyboard to select multiple cells at the same time. The cells that random numbers will be generated into will be outlined by a black heavy line.
3. Type '=RANDBETWEEN' (no quotes) into the 'Formula' textbox near the top of the Excel screen.
4. Continue to type the rest of the function that specifies the range you want the random numbers to fall into. '([Bottom],[Top])' (no quotes) should be inserted after '=RANDBETWEEN' (no quotes) with numbers replacing the word 'Bottom' and 'Top.' The 'Bottom' number should be the lowest random number you want to be generated ant the 'Top' number should be the highest number you want to be generated.
5. Press the 'Enter' key on your keyboard if you selected one cell or the 'CTRL ENTER' if you have selected multiple cells. You will now see random numbers have been generated in the range that you have specified.

Read more ►

How to Make a Bar Chart in Excel

1.
Open the Excel file that contains your data. If you have not created a file with your data yet, open a new Excel workbook and do that now. In this example, there is e a small list of data detailing how many people chose various animals as their favorite to use as an example.
2.
Use your mouse to highlight the cells that contain your data. In this example, cells A1 through B7 are highlighted.
3.
Choose the “Insert” menu from the toolbar in Excel. Now choose the “Bar” chart submenu. Pick which type of bar chart you would like to create. For the example, use the simplest 2-D version.
4.
Look over the chart you created and decide if you want to make any changes to the format. Feel free to experiment here. If you try something and find that you don’t like it, you can always use the “Undo” key to return to your file’s previous state.
5.
Save your file so that you don’t lose your work.

Read more ►

How to Use Microsoft Excel Date Difference Function

1. Type a date into a cell. For example, type '10/02/2010' into cell A1.
2. Type a second date into a cell. The second date should be later than the first date. For example, type '10/06/2010' into cell B1.
3. Type an interval into another cell. For example, type 'd' into cell C1.
4. Click on another cell and type the DATEDIF formula, inserting the cell locations of the DATEDIF components. In this example, type '=DATEDIF(A1,B1,C1)'.
5. Press the Enter key for the result. In the example, the result returned will be '4,' indicating there are four days difference between the two dates.

Read more ►

How to Set Margins in Powerpoint

Set Margins for Print
1. Open the presentation in Microsoft PowerPoint.
2. Select 'File' and 'Page Setup' from the toolbar. Select an option from 'Slides sized for.'
3. Use the scroll bar to adjust the height and width or type it in. Then click 'OK.'
Set Margins for Text Boxes and AutoShapes
4. Select the text box or AutoShape on your slide.
5. Double-click the selection to open the 'Format Text Box' or 'Format AutoShape' dialog box.
6. Go to the 'Internal margin' section under the 'Text Box' tab. Then change the left, right, top and bottom margins and click 'OK.'

Read more ►

How to Convert Dates in Excel

1. Open the worksheet.
2. Click the cell or a selection of cells.
3. Click the 'Home' tab on the command Ribbon.
4. Click the Dialog Box Launcher in the lower right corner of the 'Number' group. The dialog box opens.
5. Click the 'Number' tab. A list of categories displays.
6. Click 'Date.' A list of date formats displays in the 'Type' box. Examples include March 14, 2001 and 14-Mar-01.
7. Click the date format.
8. Click the down-arrow for the 'Locale (Location).' A menu includes 'English (United States),' for example. The formatted date can translate into other languages.
9. Click the preferred language.
10. Click 'OK.' The dates convert to a different format in the selected cells.

Read more ►

How to Make Clustered Histograms on Excel

1. Open your Microsoft Excel worksheet that contains the data for your chart. Label a new column as Bin Range, and type the groups of values that you want to use as your horizontal axis. For example, if you're counting the frequency of test scores, type '
2. Label a new column 'Frequency1' to the right of your Bin Range column. Count the number of occurrences for each of your groups of your first data set and type them into the 'Frequency1' column. Repeat the process for your second data set and label the column 'Frequency2'.
3. Highlight your 'Frequency1' and 'Frequency2' columns and click the 'Insert' tab on the Ribbon at the top of the page. Click the 'Column' drop-down box and select the style that you want to display on your chart. Click the 'Select Data' button on the Design tab of the ribbon, click 'Edit' under Horizontal Axis Labels and highlight your Bin Range values in the Axis Label Range text box.
4. Customize your chart with the tools in the Design, Layout and Format tabs of the Ribbon. Add chart titles, axis titles and data labels from the Labels area of the Layout tab. Change the color scheme from the Design tab and customize the shape styles and colors from the Format tab.

Read more ►

How to Change Interval of Value Axis in an Excel Chart

1. Start Microsoft Excel 2007 and open a spreadsheet that contains a chart to which you would like to change the interval of the value axis.
2. Select the entire chart by clicking in the white area of the chart. You will then see a light blue outline around the chart indicating you have selected the entire chart.
3. Choose the 'Format' tab at the top of the Excel screen and location the 'Current Selection' group. The 'Current Selection' group is the first group that appears on the 'Format' ribbon.
4. Click the 'Chart Elements' drop-down list and select '(Value) Axis' from the list. The 'Chart Elements' list is the top item in the 'Current Selection' group.
5. Click the 'Format Selection' button to open the 'Format Axis' dialog box. Click the 'Axis Options' option on the left side of the 'Format Axis' dialog box if necessary.
6. Change the minimum value, maximum value, major units and minor units by clicking the 'Fixed' radio buttons beside the options. This allows you to be able to type in the intervals you want to see in the text boxes to the right of the options.
7. Click the 'Close' button to close the 'Format Axis' dialog box and return to your Excel chart. You will notice that the interval of the value axis has changed.

Read more ►

How to Convert Excel Formulas to Hard Numbers

1. Highlight the cell(s) with that contain formulas you wish to convert to hard numbers.
2. Right click your mouse and select 'Copy'.
3. Right click again and select 'Paste Special'.
4. Select the 'Values' option in the Paste section of the Paste Special box.
5. Click 'OK.'
6. Hit the Enter key. The cell(s) selected will now display numbers rather than formulas.

Read more ►

How to Use the '' Symbol in the Header in Excel 2007

1. Open an Excel 2007 document and click the 'Insert' menu.
2. Click 'Header and Footer.'
3. Type the text you want to include in the 'Header' box. When typing the symbol, type '' two times. Microsoft Excel will now recognize one of those ampersands and include it in the header.

Read more ►

How to Create an Excel Macro for a Frequently Used Formula

1.
You will need to have the Developer tab displaying. If it is not displaying, click on the Microsoft Office Button (upper left corner red, blue, yellow, green logo). Click on the Excel Options button, and then under the Popular category, click to fill the box next to 'Show Developer tab in the Ribbon.' Click OK to exit. You will need to adjust the security level to run macros, so on the Developer tab, in the Code group, click on Macro Security. Fill the box next to 'Enable all macros (not recommended, potentially dangerous code can run).' Click OK. It is fine to run your own macros.
2.
This demonstration is to insert a formula into cell C2 that will extract the last word in cell A2, but any formula can be saved. In this case, the formula is long, making it a great candidate for a macro. The easiest way to create a macro for a formula is to open a workbook that currently contains the formula, or open a new workbook and input the formula. CUT THE FORMULA FROM THE CELL BY HIGHLIGHTING THE FORMULA AND PRESSING CRTL-X BEFORE YOU START CREATING THE MACRO. Click in any cell, and to start creating the macro, click Record Macro in the Excel Developer tab. The Record Macro dialog box will appear.
3.
Enter a name for the macro. If you use more than one word, separate the words with an underscore ( _ ). Next is the Shortcut key that you will use to later run the macro. The Ctrl key is shown by default as the first key, followed by a box into which you enter one letter. (If you choose a letter that is already assigned to a built-in macro, Shift will automatically appear after Ctrl .) Beneath the shortcut key, you are asked where to store the macro. To be able to use the macro in other workbooks, choose 'Personal Macro Workbook.' Lastly on the Record Macro is the option to enter a Description. When you have filled the boxes on the Record Macro dialog box, click OK. This process creates a file name for the macro.
4.
Next, you need to 'record' the steps that you want saved in the macro. These are the keystrokes or mouse clicks that you want to repeat at another location in the current, new, or saved Excel workbook. To record the macro, click on Record Macro on the Developer tab. After you click Record Macro, it will read Stop Recording, so any keystrokes or mouse clicks you make will be recorded. Click the cell into which you want the formula to appear, in this case, C2, and then press Ctrl-V to paste the formula in cell C2. Click Stop Recording in the toolbar. Your macro has been created.
5.
To use the macro, from any workbook, use your shortcut key selection, which would be Ctrl plus the letter you assigned to the macro. You can see from Step 3 that our macro shortcut key is Ctrl m. If you do not recall the shortcut keys, click on Macros on the Developer tab and the Macro dialog box will appear. Click on the macro name. Notice that if you included a description when creating the macro, the description will appear at the bottom of the Macro dialog box. With the desired macro highlighted, click the Run button and the formula will enter into, in this case, cell C2.

Read more ►

How to Merge Cells in Excel With the Keyboard

1. Navigate to the first cell in an open spreadsheet you want to merge by using the arrow keys on your keyboard.
2. Select the adjoining cell or cells by holding down the 'Shift' key and pressing one or more of the arrow keys on the keyboard. You should see an outline of the cells that you will merge.
3. Press the 'Alt' key to display number and letter key tips in Excel.
4. Press 'H' to display the key tips for all commands in the 'Home' tab.
5. Press 'M' to open the merge menu. Select one of the merging options: press 'C' to 'Merge and Center,' press 'A' to 'Merge Across' or press 'M' to 'Merge Cells.' You can also use the down arrow key to select an option.

Read more ►

How to Calculate Time in Excel 2007

1. Open the Excel 2007 document in which you want to include time calculations.
2. Enter headings for your calculation columns. For example, in a new Excel 2007 spreadsheet, you might type “Start” in cell A1, “End” in cell B1, and “Total” in cell C1.
3. Enter start and end times in the cells under the first two headings you added. For example, type 11:30 AM in cell A2 and 12:15 PM in cell B2. Always enter AM and PM after the time, unless you are using 24-hour clock time.
4. Insert formulas in the third column to calculate difference in times: If you have start and end times in cells A2 and B2, respectively, in cell C2 you would type “=B2-A2” (without the quotes) to subtract the later time in cell B2 from the earlier time in cell A2.
5. Change the cell formatting of the columns involving time calculations. Unless you do this, you may see incorrect or no results in the cells of the time totals column, though a formula is still visible in the Formula bar when you click a cell in that column. To do a time-sheet-style display of time, you will need two different time formats. Select the cells containing the start and end times and click the box at the lower-right corner of the “Number” group on the “Home” tab. The “Format Cells” dialog box appears. On the “Numbers” tab, select “Time” from the “Category” list, and select “1:30 PM” from the “Type” list. Click OK to return to your worksheet.
6. Select the cells containing the calculated time totals and again click the box in the lower-right corner of the “Number” group. Select the “Time” category, but now click the second item in the “Type” list, “13:30.” Click OK. The cells to the right of the start and end times should now show the difference in times displayed in hours and minutes. For example, for a time of 11:30 AM in cell A2 and 12:15 PM in cell B2, cell C2 will display '0:45.'

Read more ►

How to Use Excel's Intercept Function

1. Find the function field at the top of the page, with the = sign to the left. This is where you can program a cell value. If the function field is not immediately available, you can go to 'Insert' and select 'Function.'
2. In your function field, add the operator INTERCEPT.
3. Start a parenthetical statement after INTERCEPT. Your values and parameters will all be inside a set of parentheses.
4. Add a cell range for your 'y axis.' The most common arrangement might be a range of values for a y axis in the A column of the spreadsheet, and a range of values for an x axis in the B column. In this case, your cell range, joined by a colon, would look something like this: A2:A7.
5. Add a comma, then add the range of cell values for the x axis, such as: B2:B7.
6. Close your parentheses. Your statement should look like this: INTERCEPT (A2:A7, B2:B7). Adding this to a function field for a cell will make that cell's value the point at which your x and y axis converge.

Read more ►

How to Create a Sales Invoice

Excel 2010
1. Open Excel 2010. Click the 'File' tab and select 'New.' Click the 'Invoices' category in the left task pane. Review the available sales invoices. Download the sales invoice by clicking the invoice image and the 'Download' image. The invoice opens in Excel 2010.
2. Right click the default logo image and select 'Change Picture.' Browse your files and select your logo. Click the 'Open' button. Your image replaces the sales invoice template logo.
3. Highlight the default customer information on the template and type your custom information. Save the changes by clicking the 'Save' icon on the Quick Access Toolbar.
Google Documents
4. Access the Google Documents website. Type 'Invoice' in the search box. Click 'Search Templates.' Review the available sales invoices. Download the sales invoice by clicking the 'Use This Template' button. The invoice opens in Google Documents.
5. Delete the default logo image by clicking the image and pressing the 'Delete' button on the keyboard. Select 'Insert' and 'Image.' Browse your files and select your logo. Click the 'Open' button. Your image comes up in place of the template.
6. Highlight the default customer information on the template and type your custom information. Save the changes by clicking the 'Save' icon on the menu.
OpenOffice
7. Access the OpenOffice website. Type 'Invoice' in the search box. Review the available sales invoices. Download the sales invoice by clicking the 'Use This' button. The invoice opens in OpenOffice Calc.
8. Delete the default logo image by clicking the image and pressing the 'Delete' button on the keyboard. Select 'Insert' and 'Image.' SelectGH 'From File.' Browse your files and select your logo. Click the 'Open' button. Your image takes the place of the sales invoice template.
9. Highlight the default customer information on the template and type your custom information. Save the changes by clicking the 'Save' icon on the menu.

Read more ►

How to Write Percentage Formulas in Excel

1. Calculate the percent of a total. Choose a cell for the percentage formula and enter the '=' symbol to activate the formula in Excel. Highlight the cell with the part, then enter the '/' symbol, and then select the cell with the total. For example, if the total lies in cell A10 and the part lies in A3, the formula is '=A3/A10'.
2. Calculate difference in percent between two numbers. Choose a cell for the percentage formula and enter the '=' symbol to activate the formula in Excel. Enter the opening parenthesis symbol, or '('. Highlight the cell with the first number, then enter the '-' symbol. Select the cell with the second number, and then enter the closing parenthesis symbol, or ')'. Then enter 'ABS' and select the second number to use its absolute value as a divisor. For example, if you are comparing sales data, and July 2011 sales are in A2 and the July 2012 are in B2, the formula is '=(B2-A2)/ABS(A2)'.
3. Select the percent cell and choose your format. From the Home tab, click 'Percent Style' from the Number group.

Read more ►

How to Use Text Wrap in Excel

1. Open a new workbook in Excel by choosing 'File,' and then 'Page Setup.'
2. Select the small square at the top left corner of the screen of the new page. This highlights the entire workbook for formatting. If you want to format only a single cell, select that cell and continue with Step 3.
3. Right-click your mouse and select 'Format Cells,' 'Alignment' and then click the 'Wrap Text' box listed under 'Text Control.'
4. Resize the width of any Excel columns as necessary. Type in the text you need into each column of the spreadsheet. Whenever you get to the right margin of a particular cell, your text will wrap to the next line and continue to do so until you finish typing in that cell. Note that wrapped text automatically adjusts the row height of the cell.

Read more ►

How to Open a Dbf

1. Navigate to the folder containing your DBF file.
2. Right-click the DBF.
3. Drop down to the 'Open With' submenu.
4. Select 'Microsoft Office Excel' from the list of programs.

Read more ►

How to Use Freeze Frame in Excel

1. Click on the cell after the row or column you want to freeze. If you want to freeze column A, then put your cursor in column B. If you want to freeze row 1, then put your cursor in row 2. Combine these if you want to freeze a row and column.
2. Click 'View' then 'Window.'
3. Select 'Freeze Panes.'

Read more ►

How to Protect a Sheet in Microsoft Excel 2003

1. Access the sheet protection menu. To access this menu, scroll to the “Tools” tab on the command bar and select “Protection.” Under the sub-menu that opens, select “Protect Sheet.”
2. Set the sheet protect options. Make sure that you check the box labeled “Protect worksheet and contents of cells” to enable protection. Under the scroll menu in the middle of the sheet protection properties menu, you can check the corresponding boxes for the features you want disabled when unauthorized users are viewing the file.
3. Add a password to your worksheet. To add password to your worksheet, simply type it into the “Password” field.
4. Implement the sheet protection. To implement all of your protection changes, click the “OK” button.

Read more ►