How to Make a Graph on Excel With Intervals of 0.5

1. Open the Excel 2010 spreadsheet file that contains the data you want to make into a graph.
2. Click on any cell within the data field that you want to use in your graph, then click the 'Insert' tab at the top of the window. Click on your desired chart type from the Charts area of the ribbon, then click on the specific chart that you wish to create. Excel will place the chart in the middle of your spreadsheet.
3. Click on any of the white space in the chart to select the entire chart, then click the 'Layout' tab at the top of the screen. Click the drop-down box on the left end of the ribbon and choose 'Vertical (Value) Axis.' If you have created an XY Scatter chart and want to modify the horizontal axis, you can instead choose 'Horizontal (Value) Axis.' Click the 'Format Selection' button, located just below the drop-down box, to open the Format Axis window.
4. Select the 'Fixed' radio button next to 'Major Unit' on the right side of the window. Place your cursor into the text box to the right of this radio button and type '0.5' into the box.
5. Click 'Close' to close the window and see your new axis on the chart.

Read more ►

How to Make a List with Colors Using Microsoft Excel 2007

1. Open Excel 2007 and enter data into your spreadsheet. Make sure that you include a few rows and columns as you add your data.
2. Highlight a few rows of data and click the 'Home' tab. Click the 'Fill Color' icon in the 'Font' group. Select a color for these cells.
3. Highlight the remaining rows of data. Click the 'Home' tab and select the 'Fill Color' icon. Select a different color for this set of cells.

Read more ►

How to Use Excel Dashboard

1. Open Excel.
2. Download or create your data as an Excel spreadsheet, with the first row containing the column names. Select 'Save As' in the 'File' menu, name your file, and click 'Save.' Select 'Exit' on the file menu to close the Excel program.
3. Open the Excel Dashboard program. Select the 'Excel' button. The driver field will auto-fill. Select the 'Access/Excel File' button. Select the file name, and the 'Connect Name' will auto-fill.
4. Select 'New Blank Dashboard' in the 'Choose Action' box. Name the dashboard. Select 'OK.' Expand the menu in the 'Table Browser' window. Double-click on the file to be opened. Select the top alias box to select all of the column titles. Select a column to change its name, if required. Select 'Create table.'
5. Right-click the first item of data in a column. Select 'Create' and 'Create Calculation' to add calculations to the raw data. Select the first column to be used in the calculation. Select the operation symbol. Select the second column to be used in the calculation. Select a name for the 'Total' column. Select 'Use This Formula.'
6. Right-click the title of a column. Select 'Create Chart' from the pop-up menu.
7. Select the type of chart from the drop-down menu in the 'Chart Factory' window. Select the columns to be used for the X and Y axes. Select the Y aggregation type. Select 'Create Chart.' Right-click the 'Get Name' in the 'Chart Editor.' Select 'Full Apply.' Repeat this process to create further charts.
8. Select 'Save As' in the Dashboard 'File' menu. Select the file to be saved or type it into the 'File Name' box. Select 'Close All' from the 'File' menu.
9. Select 'Open' from the 'File' menu. Select the required file. Select 'Open.' Select the 'Name' portlet, and select the scissors to delete the data relating to the charts, if required. Select 'Yes' in the 'Warning' box to delete the table. Repeat this process for other charts that are no longer required.
10. Select 'Save As' in the Dashboard 'File' menu. Type the new file name to be saved in the 'File Name' box. Select 'Close All' from the 'File' menu.
11. Right-click and change the 'Get Name' in the 'Chart Editor.' Select the 'Edit' option, and retype the name.
12. Drag the corners of each portlet to resize the charts. Select 'Edit.' Select 'Resize Mode.' Select 'Align' and the alignment option to do a group alignment.
13. Select 'Save As' in the Dashboard 'File' menu. Type the new file name to be saved in the 'File Name' box. Select 'Close All' from the 'File' menu.

Read more ►

How to Sort Data in Excel 2003

1. Open the Excel 2003 file that contains the information you need to sort.
2. Click and hold on the top left cell in the data that you want to sort. Drag the mouse to the bottom right cell and release the button. Your selected data will now be highlighted in a different color.
3. Click 'Data' on the right side of the toolbar on the top of the window. Choose 'Sort' from the drop-down menu to open the 'Sort' window.
4. Click the drop-down arrow next to 'Sort By' and select the column that you want to sort by. You can also choose to sort the data further by selecting additional columns in the 'Then By' fields. Next to each column selection, you can choose to select 'Ascending' or 'Descending' depending on how you want to sort the data. If your columns do not have a header row, select 'No header row' at the bottom of the window.
5. Click 'OK' to close the window, and Excel 2003 will sort your data.

Read more ►

How to Open a 2003 Excel Workfile From 2007 Excel

1. Launch Excel 2007.
2. Click the Microsoft Office button in the top left corner of Excel.
3. Click 'Open.' This brings up a list of files in your default directory. Find the directory where your Excel 2003 file resides if it is not displayed in the default list.
4. Double-click the Excel 2003 file to open it. If you do not see your Excel 2003 document in the list, click the down arrow on the button that says 'All Excel Files,' and select 'All Files' or 'Excel Files' instead. The Excel 2003 file opens in Excel 2007.

Read more ►

How to Sort Numbers Dashes in Excel

1. Launch Microsoft Excel 2010.
2. Right-click on the letter 'A' above the first column and click 'Format Cells' from the context menu. Click the 'Number' tab and click 'Text' in the 'Category' box. Click 'OK' to save the setting and change the way Excel treats column 'A' to text. This allows you to enter and sort numbers that contain dashes.
3. Click the first cell in the first column, or Cell 'A1.' Type '100' and press 'Enter' to save the contents of the cell and move to the next cell down.
4. Type '1200' in the second cell and press 'Enter.' Continue to enter the following series in the next nine cells: '1300,' '1200-12505,' '1200-311,' 1200-312,' '1200-312506,' '1199-5,' '1201-5,' '3-565' and '1200-5.' At this point, the first 11 cells in column 'A' should have unique entries.
5. Click on the letter 'A' above the first column to select the entire column.
6. Click the 'Sort A to Z' button in the 'Sort Filter' group on the 'Data' tab of the toolbar. The whole numbers without dashes are sorted in numerical order at the top of the list, and all of the numbers with dashes are sorted below the whole numbers. The numbers which include dashes are sorted by the first digit, the ones that match in the first digit are sorted by the second digit and so on.

Read more ►

How to Remove Duplicates from Excel Macro

Build a Macro to Remove First Column Duplicates
1. Browse to Tools on the toolbar and down to Macro. To the right, find the Visual Basic Editor and left-click on it. Once in the Visual Basic Editor, find the drop-down that allows you to create a User Form, Module or Class Module. Choose 'Module.'
2. Copy and paste the following into the editing box that opens:
Sub DelDups_OneList()
Dim iListCount As Integer
Dim iCtr As Integer
' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False
' Get count of records to search through.
iListCount = Sheets('Sheet1').Range('A1:A100').Rows.Count
Sheets('Sheet1').Range('A1').Select
' Loop until end of records.
Do Until ActiveCell = ''
' Loop through records.
For iCtr = 1 To iListCount
' Don't compare against yourself.
' To specify a different column, change 1 to the column number.
If ActiveCell.Row
Sheets('Sheet1').Cells(iCtr, 1).Row Then
' Do comparison of next record.
If ActiveCell.Value = Sheets('Sheet1').Cells(iCtr, 1).Value Then
' If match is true then delete row.
Sheets('Sheet1').Cells(iCtr, 1).Delete xlShiftUp
' Increment counter to account for deleted row.
iCtr = iCtr 1
End If
End If
Next iCtr
' Go to next record.
ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
MsgBox 'Done!'
End Sub
3. Click 'File' and then 'Close and Return to Microsoft Excel.' Once returned to Excel, browse to the Tools option in the toolbar and then to Macros. To the right, choose the 'Macros' option. In the Macro option box, see the macro called DelDups_OneList. Choose 'Options' and assign a shortcut key of 'q.' Click 'OK.'
4. Click 'Run.' 'Done' will appear. The duplicate entries in the first column are deleted.
5. Because you have set up your shortcut key for Ctrl q, you only need to type Ctrl q in the future and the duplicate entries in your first column of data will be removed.

Read more ►

How to Create Bar Codes in Excel

Determine Requirements and Install Bar Code Font
1. Determine font requirements. Bar code type determines font requirements. Add-ins covering a number of font types is available but can be costly so a better idea is to purchase either a general-purpose font, or a font specific to the bar code you need. Common bar code fonts include:
Code 39 -- General-purpose bar code
Code 128 -- Used as the basis for Health Industry and Blood Bank bar codes
UCCEAN -- International bar code typically used for shipping/receiving
ITIF14 -- Used to mark external containers of products with an EAN (European Article Numbering) identifier
12of5 -- High-density bar code supporting alphanumeric characters
UPC/EAN -- Most common retail bar code font
GS1 Databar -- Compact version of the UPC bar code that can carry more information and identify small items more easily
2. Ensure the bar code font is compatible with Microsoft Excel. Most fonts will work with Microsoft Excel but double-check to make sure. If you cannot find compatibility information in your user manual, call the manufacturer's customer service department.
3. Read installation instructions and install bar code font add-in.
Create Bar Code
4. Open a new spreadsheet in Microsoft Excel. Bar code fonts run via macros so enable macros before you start. For Excel versions up to 2003, from Tools, click on Macro, and then click on Security. Set the Security level to Low to allow macros to run unrestricted. In Excel 2007, click the Microsoft Office button, then Excel Options, and Trust Center. From the Trust Center, click Trust Center Settings, then Macro Settings. Set macro settings to Enable All Macros.
5. Enter data for the bar code in column A and append asterisks to the data in column B. Data in column A should be in a normal form, such as 123-45-6789. In column B, the same data should be in formula form to append asterisks preceding and following the formula. For example, ='*'A2'*' will append asterisks to the data in column A so it reads *123-45-6789*(assuming the data is on line A2). Enter all data following this procedure.
6. Change the data in column B to a bar code. Select all data in column B (highlight) and choose the bar code font from the toolbar.
7. Format the bar code. First, set the bar code size. If you will be using a scanner to read the bar code, choose a 24-point size. Center the text below the bar code by clicking the Center Text button or use the shortcut Ctrl E. Ensure the entire bar code is visible within the spreadsheet cell by double-clicking the header in column B to auto-fit the contents of the cell.

Read more ►

How to Use Subtotals in Excel 2003

1. Open your Excel worksheet with your data arranged in columns.
2. Select the column you want subtotaled. Hold down the left mouse button and drag down the column.
3. Under 'Data' on the top menu, select 'Subtotal' from the dropdown menu.
4. In the 'Subtotal' pop-up window that appears, check the features you want in your subtotal, and then click 'OK.'
5. A subtotal of your numbers will appear on the worksheet. Save your work.

Read more ►

How to Fix a Corrupt XLS File

1. Locate the file that requires repair. For best results, copy the corrupt file to a stable hard drive, if it is located on removable media or on a networked device.
2. Click 'File->Open...,' (Excel 2003), or the 'Office Button,' then 'Open.' (Excel 2007).
3. In the 'Open' window, select the corrupt file.
4. Click the drop-down arrow next to the 'Open' button, and select 'Open and Repair' from the menu. Excel will open the file in 'File Recovery' mode and attempt to repair the corruption.

Read more ►

How to Create Labels From Excel

1.
Open a blank Excel worksheet. Go to the 'Page Layout' tab of Excel 2007 and select 'Margins.' In Excel 2003 or earlier, go to the 'File' menu, select 'Page Setup' and go to the 'Margins' tab.
2.
Change the 'Top' box to '0' and leave the header at '0.5.' Change both the left and right margins to '0.19.' Change the bottom margin to '0' and leave the footer at '0.5.' Select 'Horizontally' and 'Vertically' under 'Center on Page.' Click 'OK' to apply these changes.
3.
Select cells A1 through A10. If you are using Excel 2007, click the 'Format' dropdown of the Home tab and select 'Row Height.' In version 2003 or earlier, go to the 'Format' menu, point to 'Row' and click on 'Height.' Change the row height to '72' and click 'OK.' Repeat this step, selecting 'Column Width' instead of row height. Change the width to '35.' Apply these changes to cells C1 through C10 and E1 through E10, also.
4.
Select cells B1 through B10. Following the instructions in Step 3, change the column width to '1.29.' Change D1 through D10 to '1.29' also.
5.
Select cells A1 through E10. Click the 'Borders' dropdown arrow, which is in the Font section of the Home tab in Excel 2007 and is on the Format toolbar in Excel 2003 or earlier. Select 'All Borders.'
6. Enter addresses into the cells in columns A, C and E. B and D will be margins between the columns.

Read more ►

How to Validate Data in Excel

1. Know the types of data that you can validate in an Excel spreadsheet. Numbers, dates and times and length can be limited to whole numbers, or ones including decimals, be a minimum or maximum amount or within a range.
2. Use a list of values for the cell. This is helpful when categorizing items with a specific list of data or when the cell always contains one choice such as small, medium or large.
3. Decide if you want the user to view a message when the cell is selected prior to them entering data (input message) or when incorrect data is entered (error message.) Error messages can simply provide information about what should be in the cell, a warning that the data doesn't fit the cell or a stop message not allowing any data that isn't correct to be entered.
4. Set up and name the list. The list can be in the same worksheet or in a different one. Once the data is entered, highlight all relevant cells and click on the 'Insert' command. Choose 'Name' then 'Define.' Enter the equals sign and the name of the list, for instance, '=listname.'
5. Choose one of the cells that you want to validate and select the 'Data' command. Choose 'Validation' and under the 'Settings' tab select the appropriate description for the cell limits. Depending on the choice, additional boxes will appear that need to be filled in.
6. Click the 'Format Painter' icon on the top of the toolbar and click the cell you just validated. Highlight any other cells that have the same validation criteria.

Read more ►

How to Reset the Excel Document Password

1. Open the worksheet that you wish to change or remove the password for. Enter the password when prompted.
2. Select the 'File' menu to open a left-hand menu pane.
3. Click 'Info.'
4. Click 'Encrypt Workbook' next to 'Permissions.' Another menu will show up.
5. Choose 'Encrypt with password.' A password encryption window will appear and the other menus will close.
6. Remove the password by clearing the 'Password' field and selecting 'OK.' Modify the password by typing in a new one in the 'Password' field, then selecting 'OK.'

Read more ►

How to Make a Pie Chart in Excel 2003

1. Enter the data labels into the cells in one column or row of the Excel worksheet. Enter the data into corresponding cells in the next column or row.
2. Select the data by depressing the left mouse button and dragging the mouse pointer over the cells.
3. Go to the 'Insert' menu and select 'Chart' or click the 'Chart Wizard' button on the standard toolbar to start the 'Chart Wizard.'
4. Select 'Pie Chart' as the chart type. Choose a subtype, such as 'Exploded Pie' or '3-D Pie.'
5. Click 'Next.' Make any other changes or additions in the wizard as desired. Click 'Finish' to complete the chart.

Read more ►

How to Remove Page Breaks From Excel 2007

1. Open the Excel file in which you'd like to remove a page break. Click the 'View' tab at the top of the screen.
2. Click 'Page Break Preview' in the 'Workbook Views' group. This allows you to see your manual page breaks.
3. Click the row or column label after the page break you wish to delete. As an example, to delete the page break between columns E and F, you would click the top 'F' column label, which selects the entire F column. Likewise, click the '20' row label to select the page break between rows 19 and 20.
4. Click the top 'Page Layout' tab.
5. Click 'Breaks' in the 'Page Setup' group, and select 'Remove Page Break.' Alternatively, select 'Reset All Page Breaks' to remove them all.
6. Click the 'View' tab, and click 'Normal' in the 'Workbook Views' group to return to your normal view mode.

Read more ►