Excel

Browse » Home » All posts

Saturday, April 27, 2013

How to Format Cells to Make Negative Numbers Appear Like 0000.00 in Excel

1. Open the Excel 2010 spreadsheet where you want to adjust the number format.
2. Click on the top-left cell in the area where you want to adjust the number format. Hold the mouse button down and drag your mouse pointer to the bottom-right cell, then release the button to select the entire area. Alternatively, you can select the column letter, or row number, to select an entire column or row.
3. Right-click your mouse while your pointer is located anywhere within your selection. Choose “Format Cells” from the small menu that appears and the Format Cells window will appear.
4. Click on “Custom” from the list on the left side of the window. Then place your cursor into the “Type” field on the right side of the window and use “Backspace” to erase whatever format is currently in there.
5. Enter the following code into the “Type” box:#,##0;'0000.00'The characters before the semicolon will control how positive numbers appear, while the characters after the semicolon will replace any negative number with “0000.00.” Click “OK” to close the window and complete your format change.

Thursday, April 25, 2013

How to Graph a Line Graph in Excel 2007

1. Open the Microsoft Excel 2007 application on your computer and click on the 'Microsoft Office' button. Click on the 'Open' option.
2. Locate the Excel 2007 file on your computer that contains the data you want to turn into a line graph. Select the file and then click on the 'Open' button.
3. Click on the 'Insert' tab and then click on the 'Line' option from the 'Charts' group. Click on the 'Line with Markers' option.
4. Use your mouse to highlight the data within the spreadsheet that also contain labels for the rows and columns. The Chart Tools feature now becomes available.
5. Click on the 'Design' tab and the select a line graph style from the 'Chart Styles' group. Click on the 'Layout' tab and then click on the 'Chart Title' option. Click on the 'Above Chart' option.
6. Click in the chart title text box and enter a title for your chart. Right-click on the chart title and enter a size for the chart title that meets your preference in the 'Size' box.
7. Click on the 'Layout' tab and then select a legend style for your line graph from the 'Chart Elements' box. Click on the 'Legend' option from the 'Layout' tab and select the position for the legend, such as the 'Show Legend at Top' option.
8. Click on the 'Axis Titles' option from the 'Layout' tab and enter titles for the X axis and Y axis of your line graph. You make any other changes to the line graph, such as thickness or color of lines, by clicking on the 'Design' tab.

How to Lose the Zeroes in Excel

All Zero Values
1. Move your mouse pointer to the 'File' tab and left-click. Click 'Options.'
2. Find the 'Advanced' category at the top and click it. Locate 'Display options for this worksheet' and select your worksheet.
3. Remove the check beside 'Show a zero in cells that have zero value.' The cells with zero values now appear blank and contain no numbers.
Selected Cells
4. Move your mouse pointer to the cells with zero values that you want to lose. Hold down the left button and drag your mouse. This highlights the cell(s).
5. Locate the 'Cells' group in the 'Home' tab. Click 'Format.' followed by 'Format Cells.'
6. Find the 'Category' list. Click on 'Custom.'
7. Type '0;-0;;@', without the quotation marks, in the 'Type' box. This hides the zero values.

How to Do Addition in Excel

Using the Plus Sign
1. Open the Microsoft Excel 2010 spreadsheet that you want to work with.
2. Click on an empty cell on the spreadsheet, wherever you want the results of your addition to be listed.
3. Type '=' -- the 'equals' symbol -- which indicates the start of a formula, then type in the number or cell reference of the first thing you want to add. If entering a cell reference, place the column letter of the cell in front of the row number, so that the first cell on the spreadsheet is 'A1' and the one under that is 'A2.'
4. Enter a ' ' sign into your formula, then enter the second number or cell reference that you want to add. Continue alternating ' ' signs and numbers or cell references until you have entered all the information you want to add together. Press 'Enter' to complete your formula. The result of the addition appears in the cell.
Using the SUM Function
5. Open the Microsoft Excel 2010 spreadsheet that you want to work with. Click on any empty cell.
6. Type '=' -- the 'equals' symbol -- which signifies that you are starting a formula, then type the word 'sum' and place an open parenthesis at the end of the word. This tells Excel that you will be performing the 'SUM' function on the values in the parentheses.
7. Enter the first number that you want to add, or the first cell reference, followed by a comma, then enter the second value or reference, followed by another comma. Continue alternating values and references with commas until you have entered all the items you want to add together. Enter a close parenthesis and press 'Enter.' The result of your addition appears in the cell.

How to Make a Bar Graph in Excel 2010

1. Open a new Excel 2010 spreadsheet. Identify two or more variables you want to include in your bar graph. For example, to graph the number of lung cancer cases occurring over the past 100 years, use the variables 'Decade' and 'Lung Cancer Cases.'
2. Place the independent variable on the x-axis of a bar graph. Type the variable name, such as 'Decade,' into cell A1. Type the second variable name in cell B1. This variable goes on the y-axis of your bar graph. Add additional variables to the spreadsheet if necessary.
3. Type numerical data under each variable. Include as many cells as necessary to complete your entire data set.
4. Click the 'Insert' tab and select 'Column' in the 'Charts' section. Select the '2-D Column' chart. A blank box will appear in your Excel 2010 spreadsheet.
5. Click 'Select Data' in the 'Design' tab of the 'Chart Tools' area. Click the 'Add' button to select the data for the y-axis of the bar graph. Type the variable name in the 'Series Name' box. Select the range of data points in the 'Series Values' box. Click 'OK.' Add additional variables to the y-axis if applicable.
6. Click the 'Edit' button in the 'Horizontal (Category) Axis Labels' section. Click and drag your mouse to highlight the x-axis values from the spreadsheet. They will appear on the bottom of the bar graph.
7. Click the 'Layout' tab of the 'Chart Tools' area. Use the options in this tab to change the axis names, grid lines, legend or chart title. Click the 'Format' tab in the 'Chart Tools' section to modify the color, shape and appearance of the bar graph.

How to Create Line Graphs With Two Groups in Microsoft Excel

1. Open the Microsoft Excel file that contains the data sets you wish to graph.
2. Click the 'Chart' button on the Excel toolbar. A pop-up chart wizard will appear.
3. Select the 'XY Scatter' chart type and any chart sub-type you prefer. The options provide four types of line graphs to suit your style. Press the 'Next' button.
4. Delete anything that is displayed in the 'Data Range' field. Select the 'Columns' option in the 'Series in' field. Then click the 'Series' tab.
5. Create a new series for each line you wish to graph based on a group of data. If you have two groups of data, you will create two series. Click the 'Add' button twice to create these two series.
6. Click on the first series in the 'Series' list box.
7. Type the name of this series in the 'Name' field. Specify the ranges for the X values and Y values of this first line group separately in the two other fields.
8. Click the second line group series in the 'Series' list box and fill in the same fields. Press the 'Next' button to customize the visual details of the chart, if desired, or alternately press the 'Finish' button to display the graph.
9. Right-click on each line or XY scatter plot and select the 'Format Data Series' option if you wish to alter how the lines display. This is useful if the lines are close together so each is uniquely identified.

How to Use Absolute Cell Referencing

Creating an Absolute Reference
1. Determine if a formula should be absolute or relative. When designing a formula, look for hard-coded numbers. A sample formula for the discount could be 'discount = cost of merchandise * 20%'. The 20% is a hard-coded number and has the potential to change. Instead, create a cell titled Discount Amount. The new formula would be 'discount = cost of merchandise * discount amount'. That way, if the discount ever increased, you would need to change only the amount in the Discount Amount cell, and all the formulas would automatically update.
2. Create the formula. Once you know a formula will have an absolute reference, you need to create the formula in Excel. Create a cell that holds the data that has the potential to change; in this example it will be A4. This is the 20% discount in our example. Identify the first cell that will contain the cost of the merchandise; for example, cell D4 is the first cell on the purchase order that contains merchandise cost. To figure out the discount amount, in cell D5, type =D4*A4 and press the Enter key.
3. Make the cell absolute. To make the cell A4 absolute in this formula (meaning Excel will use cell A4 absolutely every time), simply highlight A4 in the formula and press your F4 key. This will place a $ before the cell row and column name. You could also enter a $ in front of the cell row and column name in the formula. The new formula be =D4*$A$4.
4. Copy and paste the formula to the other cells. When you paste the formula into other cells, the relative reference, D4, will automatically update to the corresponding row (or column) number, and the absolute reference will stay the same. For example, the next row would contain the formula =D5*$A$4.

Wednesday, April 24, 2013

How to Add the Word 'Draft' As a Watermark to Excel Documents

1. Open your Excel document and click 'Insert,' then 'Word Art.'
2. Select the style you want your watermark to look like.
3. Type 'Draft.'
4. Right-click your word art and select 'Format Text Effects.'
5. Check that 'Solid Fill' is selected and drag the transparency bar to a setting that allows you to see the data on the spreadsheet and the word 'Draft' on the spreadsheet.

How to Add Two Cells in Excel

1. Start Microsoft Excel, and open the file you want to use.
2. Click on the cell in which you want the total to appear.
3. Press the key on the keyboard. This character will appear in the cell and in the Formula Bar.
4. Click on the first cell you want to add. That cell's name, such as B6, appears in the Formula Bar after the sign.
5. Press the key again.
6. Click on the second cell you want to add. That cell's name appears in the Formula Bar.
7. Press Enter to complete the equation. The total will appear in the initial cell.

How to Create a Bar Code in Microsoft Office Publisher

1. Start up Microsoft Excel and prepare your data. This will include all the pertinent information you wish to associate with the bar code. You will also need to create a column for your bar code data. This is simply a code that will translate into a bar code and act as a reference point for that specific row of data.
2. Highlight all of the data in the bar code column and in the 'Custom Toolbar' of Excel; click on 'Settings,' and choose the appropriate bar code type. You will also need to click on the drop-down box below 'Convert To' and select 'Image File.' At this point, click 'Generate Now.'
3. Save your data and close Excel; then open Publisher.
4. Select the appropriate label size in Publisher once it starts. This selection will be based on the labels the bar codes will be printed on.
5. Click 'Tools,' then 'Mailing and Catalogs' and then 'Mail Merge,' at which point you will select 'Picture Field' to insert the bar code. You can also select any of the data from your Excel spreadsheet to accompany the bar code, such as other product or lot information.

How to Make Excel 2007 Behave Like 2003

1. Visit AddInTools.com and download 'Classic Menu for Office 2007.' This product costs $29.99, as of September 2010, and you will be asked to pay with a credit or debit card. You will not be able to proceed until you have successfully purchased and downloaded this product to your computer.
2. Open Excel 2007 on your computer. To do this, click the computer's Start menu. Click 'Microsoft Office 2007,' then click 'Microsoft Excel 2007'. Once you open Microsoft Excel 2007, you will notice a new task on your toolbar: 'Menu,' located right before 'Home.'
3. Click the new 'Menu' task on the toolbar at the top of Excel 2007.You should now see a toolbar that looks just like the toolbar of commands you are familiar with using on Excel 2003.

How to Find Lost Excel Files

Windows XP
1. Click 'Start' in the lower-left corner of your computer screen.
2. Place your mouse pointer over 'Search,' then select 'For files or folders.'
3. Click on the 'Documents' option, then select 'Use advanced search options,' then click 'More advanced search options.'
4. Click the appropriate box for when the file was last modified. If you don't know, leave this section blank.
5. Type '.xls' (without quotes) in the box that asks for all or part of the document name. If you are using Excel 2007 or later versions, type '.xlsx' instead of '.xls.' This will limit your search to Excel files.
6. Add keywords in the keyword field if you know any from the spreadsheet and change the 'Look in' field to 'My computer.'
7. Click to place check marks in the boxes labeled 'Search system folders,' 'Search hidden files and folders,' 'Search subfolders' and 'Search tape backup.'
8. Click 'Search' and allow Windows to find your document for you. If you have not entered keywords, the system will find all Excel files that meet the other criteria, so you may have a long list to scroll through to find your file.
Windows Vista
9. Click the 'Start' orb in the lower-left corner of your screen. A search box is included at the bottom of the Start menu.
10. Type '.xls' (without quotes) in the search box. If you are using Excel 2007 or later versions, type '.xlsx' instead of '.xls.' This will limit your search to Excel files. Add any keywords you remember from the document to this box as well, separated from the Excel file extension with a space.
11. Press the 'Enter' key.
12. Look through the search results for your file. If you do not see it listed, click the box at the top of the search results window labeled 'Include non-indexed, hidden and system files.'
13. Click on 'Document' near the top of the window to narrow your results to documents only (this includes spreadsheets) and click 'Search.' Scroll through the results to find your lost Excel file.
Windows 7
14. Click 'Start,' then type 'folder options' into the search box near the bottom of the Start menu.
15. Click 'Folder options' when it appears, then click 'Change search options for files and folders.'
16. Click the boxes to search hidden files and folders and system folders, then click 'OK.'
17. Click 'Start' again and type '.xls' or '.xlsx' plus any keywords from your Excel file into the search box. Click 'Show more results' on the results page and scroll down to find your Excel file.

Tuesday, April 23, 2013

How to Use Excel 2007 to Estimate Linear Functions

1. Open a new or existing Microsoft Excel spreadsheet. If you start with a blank spreadsheet, enter your x-values into column A and y-values into column B. Each pair of numbers creates a data point on a chart, so they need to correspond to each other.
2. Label your columns with descriptions of your data, such as 'Month' and 'Hours.' In this example, if you worked 160 hours in the month of June, those two values create one data point on your chart. Highlight your data set and select the 'Insert' tab on the Ribbon at the top of the page.
3. Click the 'Scatter' drop-down box in the 'Charts' area, then select the 'Scatter With Only Markers' option. This option creates a plot from your data points and allows you to estimate the linear function. Select the 'Layout' tab of the Ribbon and click the 'Trendline' button from the 'Analysis' area.
4. Click 'More Trendline Options' from the drop-down options. Select 'Linear' from the 'Trend/Regression Type' to estimate a linear function. Select the 'Display Equation on Chart' box to view the linear function equation on the chart.

Monday, April 22, 2013

How to Work Out Percentages in Excel

1. Open the Microsoft Excel program and type the numerator (given amount) into cell A1.
2. Highlight cell B1.
3. In the 'fx' box in the toolbar at the top of the screen, type '=A1/X' (without the quotes), replacing X with the denominator (the total amount) . For instance, if you are calculating a percentage score out of 60 points, you would type '=A1/60'.
4. Hit enter. This should calculate a decimal (eg, .75) in cell B1.
5. Hit the '%' button on the formatting toolbar near the top of the screen to convert to a percentage. Alternatively, you can simply multiply by 100.

How to Remove the Password Required to Open Excel

1. Open the Microsoft Excel application by clicking 'Start' and typing 'excel' into the search bar. Click on 'Excel' in the list that appears to open it. You can also click 'All Programs' and open Excel from there. You don't need a password to open Excel this way -- you only it when you double-click on a protected file.
2. Click 'File,' then 'Open.' Click on the name of the file that is password-protected. Type the password and hit 'Enter' to open the document.
3. Click 'File,' then 'Info' and 'Permissions.' Click 'Encrypt with Password.' The password entry box opens.
4. Delete the password in the box, leaving the box empty. Click 'OK' to remove the password from the document. Click 'Save' before exiting the document.

Subscribe to: Posts (Atom)