How to Graph a Trend Analysis in Microsoft Excel

1. Enter the data in Microsoft Excel on which the trend analysis will be performed.
2. Create a chart from the data entered. The chart must be an unstacked, two-dimensional chart that is formatted as bars, columns or lines.
3. Click anywhere in the chart. This will display the Chart Tools and add the Design, Layout and Format tabs at the top of the worksheet.
4. Click Trendline under the Layout tab in the Analysis grouping. This will create a drop-down showing the types of trend analysis that can be performed on your data chart.
5. Select one of the predefined trend-line options. The options are linear, exponential, linear forecast and two-period moving average. This will automatically graph the trend analysis in the chart.

Read more ►

How to Calculate Total Number of Rows in MS Excel

1. Log on to your computer and open Microsoft Excel by clicking on the 'Start' button, choosing 'All Programs' from the menu and selecting 'Microsoft Office.' Choose 'Excel' from the list of programs, then click the 'File' menu and choose 'Open' from the menu.
2. Hold the 'Ctrl' key down and tap the 'End' key on your keyboard. This will automatically move the cursor to the last row in the spreadsheet that contains an entry. If you simply want to know how many rows are in your spreadsheet you can use that row number. If you need to know how many rows actually contain data, move on to the next step.
3. Position your cursor underneath the last row that currently contains data and type '=COUNT(' hit the up arrow on your keyboard, then the period key. Hit the up arrow again and continue pressing the up arrow until you reach the top of the spreadsheet. When you have reached the top of the spreadsheet type ')' to close the calculation. You will see the number of rows that contain data.
4. Type the formula directly if you are working with a large spreadsheet. If you are working with a spreadsheet that contains a large number of rows, it will be easier to enter the formula directly. To enter the formula directly type '=COUNT(firstrow:lastrow)' For instance, if the first row in your spreadsheet is A1 and the last is Z1, the formula would be '=(COUNT(A1:Z1).

Read more ►

How to Remove Passwords in Excel 2007

1. Open the password-protected Excel 2007 file by double-clicking it from Windows or clicking 'Open' from the Office menu at the upper left of the screen.
2. Attempt to modify the document by double-clicking a cell and attempting to change a value. An error message will appear and warn you about your attempted action.
3. Click the 'Review' tab at the top of the screen.
4. Click 'Unprotect Sheet' and supply the document's password when prompted. Click 'OK' to submit the password.
5. Click the Office icon and then click 'Save.' The document will be saved in its unprotected state, and a password will no longer be needed to modify it.

Read more ►

How to Turn Excel 2007 Documents to Excel 2003

1. Open the Excel 2007 file that you want to convert into Excel 2003.
2. Click the 'File' button in the upper left hand corner.
3. Highlight 'Save As,' and then select 'Excel 97-2003 Workbook' as your save option. This will save your spreadsheet as '.xls', which is the Excel 2003 format.

Read more ►

How to Write Macros in Excel 2003

1. Click 'Tools > Macros > Record New Macro...' in the top menu.
2. Choose a name for your macro and type it into the 'Macro Name' textbox. For example, if you want to create a button that sets a certain cell to a special format, you might name the macro 'Special Format Macro.'
3. Type the shortcut key you want to use to access the macro into the 'Shortcut Key' textbox. If the example, you might choose 'S' as your shortcut key.
4. Press 'OK.'
5. Make the changes you wish to record into the macro. For example, you could make the text of the cell bold and the borders green.
6. Press the stop button on the Stop Recording Dialog. The button has a blue square on it.

Read more ►

How to Label Category Axis (X) in Excel 2007

1. Open Excel 2007 and open a workbook. Click the 'Office' button and select 'Open.' Browse your files and locate the workbook you wish to use, and double click it.
2. Locate a range of data that contains row headers. Highlight the data and the row header information. The row header information will appear in the label category x axis. Click 'Insert' on the ribbon. Select a 'Line' chart to represent your data. The chart appears in your worksheet.
3. Click the outside border of the chart. The Chart Tools tab appears above the ribbon. Select the 'Layout' tab. Click the 'Axis Title' icon. A drop down list appears. Click the 'Primary Horizontal Axis Title.' Select 'Title below Axis.' Your horizontal or x axis is now labeled.

Read more ►

How to Create an Exploding Pie Chart in Excel

1. Start Microsoft Excel 2007 and open an existing spreadsheet that creates a pie chart or create a pie chart from existing data in the spreadsheet.
2. Click on the pie chart to select the entire pie chart. Be careful to click in the white area of the chart so the entire pie chart is selected. You can tell the whole pie chart is selected because it will be surrounded by a light blue line.
3. Hover your mouse over the piece of pie that you want to explode. Your mouse cursor will turn into a 4-headed arrow.
4. Click and drag the slice of pie outwards, away from the rest of the pie in the chart. Notice the blue outline that will grow outside of the pie chart. This line represents how far away the pieces will explode from each other.
5. Release the mouse button. The pie chart will now be exploded with all of the slices of pie broken away from each other.
6. Click and drag the individual pieces of pie to their new location if you would like to manually adjust the exploding pie chart pieces.

Read more ►

How to Get Rid of Circular References in Excel

Excel 2003
1. Open the 'Tools' drop-down menu at the top of the screen if the 'Circular Reference' toolbar is not displayed, then click 'Customize.'
2. Click on the 'Toolbars' tab at the top of the window that appears, then check the 'Circular Reference' check box if it is not already checked. Click the 'OK' button and close the window.
3. Click on and highlight the first cell in the 'Navigate Circular Reference' box within the 'Circular Reference' toolbar.
4. Examine the formula in the highlighted cell carefully for errors; i.e., referencing the cell itself. If you find no such mistake that could be the cause of the circular reference message, click to the next cell in the 'Navigate Circular Reference' box. (If the word 'Circular' is shown in the status bar without referencing which cell, switch to another worksheet.)
5. Continue through each cell, reviewing and correcting any circular references present in each until the status bar no longer shows the word 'Circular.'
Excel 2007
6. Click on the 'Formulas' tab at the top of the window.
7. Click on the arrow on the 'Error Checking' button in the 'Formula Auditing' box.
8. Select the first cell listed in the submenu under 'Circular References.'
9. Examine the formula in the cell closely and look for self-references. If you find no cause of the circular reference in the cell, click to the next one in the 'Circular References' submenu.
10. Repeat the previous step and review each cell in turn until the words 'Circular References' disappear from the status bar.

Read more ►

How to Use Excel Query

Query a Database
1. Open the Excel worksheet into which you want to import data.
2. In Excel 2003, go to the 'Data' tab, point to 'Import External Data' and select 'New Database Query' to query a database. In Excel 2007 or 2010, go to the 'Data' tab and select 'From Access' in the 'External Data' group.
3. Select the database you want to query and click 'Open.' Select the table in this database from which you want to import data and click 'OK.'
4. Choose 'Table,' 'PivotTable Report' or 'PivotChart and PivotTable Report' under 'Select How You Want to View This Data in Your Workbook.'
5. Enter a range of cells in the current worksheet and click 'OK.' The query will import the database table you selected into the current worksheet.
Query a Web Page
6. Open the worksheet into which you want to import data from a web page.
7. In Excel 2003, go to the 'Data' menu, point to 'Import External Data' and select 'New Web Query.' In Excel 2007 or 2010, go to the 'Data' tab and click on 'From Web' in the 'Get External Data' group. The 'New Web Query' dialog box will open.
8. Enter the address of the web page you want to query in the 'Address' field and click 'Go.'
9. Click the arrow next to the table you want to import. If you do not see arrows, click the 'Show Icons' button on the 'New Web Query' toolbar. To import several tables, simply click the arrow next to each one.
10. Click 'Import.' Enter the range in the current worksheet where you want to place the data. Click 'OK.' The query will import the table or tables you selected into the current worksheet.

Read more ►

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.

Read more ►

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.

Read more ►

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.

Read more ►

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.

Read more ►

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.'

Read more ►

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.'

Read more ►