1. Double-click the spreadsheet you want to publish to open Excel. Make sure that you select the spreadsheet that contains the data you want published to the Web.2. Click the 'File' button and then click the 'Save As' option. The Save As window displays on screen.3. Select the 'Web Page' option for the file you're saving from the drop-down menu. Click the 'Publish' button. The Publish as Web Page dialog box displays on screen.4. Click the 'Open published Web page in browser' check box and then click the 'Publish' button to open the data as a Web page in your brows...
Browse » Home » Archives for July 2011
Thursday, July 28, 2011
How to Move the Excel 2007 PivotTable Field List
1. Open the Excel workbook and navigate to the worksheet containing your PivotTable.2. If the Field List is not visible on the screen, click the “Field List” button in the Show/Hide group. The Field List appears on the right side of the Excel window. Click “Field List” or the “X” at the far right of the title bar to hide the Field List.3. Move the Field List by moving the mouse pointer to the title bar of the Field List, clicking and holding down the left-side mouse button and dragging the mouse across the screen. You may also left-click on the down arrow on the Field List title bar, click...
How Do I Link to Cells That Move in an Excel Spreadsheet?
Merge Cells in Excel 20031. Open the Excel 2003 document that contains the cells that you want to link together. Highlight the cell that you want to keep together and stop from moving. You must make sure that the data is in the upper-left cell of a range of the selected sells to merge them together.2. Click on the “Copy” option from the “Standard” toolbar menu, then click on the upper-left cell that is part of the range of cells you want to merge. Click on the “Paste” option.3. Select the cells that you want to link together again. Go to the “Formatting” tool bar and click on the “Merge...
Wednesday, July 27, 2011
How to Use the Tangent Function in Excel
1. Open up the Excel worksheet where you want to use Excel's tangent function.2. Select the cell where you want to determine the tangent of an angle.3. Enter in the following formula:=TAN(X)where 'X' equals the angle you want the tangent for, in radians. If 'X' is listed in another cell, you can instead use a cell reference, like A1 or B12 for 'X.' If your angle information is in degrees, use this formula instead:=TAN(RADIANS(X))as the RADIANS formula will convert the angle to radia...
How to Fix Excel 2007
1. Double-click the Excel 2007 program icon on your desktop to open it or select its name from the list of programs in the Start menu's 'All Programs' list. Click the Excel Office button and select 'Excel Options.' Click 'Advanced' and scroll down to the 'General' section. Click the checkbox to remove the checkmark from 'Ignore other applications that use Dynamic Data Exchange (DDE).' Close Microsoft Excel. Double-click on any saved Excel file to open it and see that the 'There Was a Problem Sending the Command to the Program' error message no longer appears.2. Open the Excel program. Click...
What Are Some Practical Uses for Excel?
1. Manage your finances with Excel. Create a personal or family budget and track your income and expenses by month or year. Create a spreadsheet that helps you plan and track your savings for retirement, or for your child's college education. Use Excel's built-in mathematical functions to automatically calculate routine or complex equations.2. Create a calendar or schedule with Excel. Whether it's a weekly, monthly or yearly calendar for your family; a personal daily appointment planner; or a schedule for managing homework, bill payments, or your favorite sport team's games, Excel makes it...
How to Lock Cells in Microsoft Excel
Microsoft Excel 20071. Open the Microsoft Excel 2007 application on your computer. Make sure you have the worksheet with the cells you want to lock open.2. Select the cells that you want to lock on your worksheet using your mouse. You can also select an entire column or row.3. Click on the “Home” tab and then click on the “Format” option from the “Cells” group. Click on the “Format Cells” option.4. Click on the “Protection” tab and then click on the box next to the “Locked” field so that it’s selected. Click on the “OK” button.5. Click on the “Review” tab and then click on the “Protect...
Tuesday, July 26, 2011
How to Make Merged Cells in Excel 2003 Grow With Wrapped Text
1. Select the cell in which you want the merged text to appear by clicking it.2. Open the 'Format' menu, select cells from the shortcut menu and when the Format Cells dialog box opens, click on the 'Alignment' tab. The alignment page opens. Go to the Controls section and click the 'wrapped text selection' button. Click 'OK' to close the Format Cells dialog box.3. Adjust the cell width to the desired size using the 'select and drag' method or the Format Cells dialog box.4. Begin the merge formula by typing '(=' without quotes in that cell.5. Click the first cell that contains the wrapped...
How to Manually Change the Margins in Excel 2010
1. Open 'Microsoft Excel 2010.'2. Press 'Ctrl' plus the letter 'O.' Locate and open an Excel spreadsheet that you want to print.3. Click 'Page Layout' at the top.4. Click the 'Margins' icon and click 'Custom Margins' at the bottom of the menu. Set custom margin sizes depending on your requirements. Click 'OK.'5. Press 'Ctrl' and the letter 'P' to open the Print window, which will also show a print preview. If you want to adjust the margins on the page visually, click the 'Show Margins' icon, which is the left-most of two small icons in the bottom-right corner of the Print window. Click...
How to Size All Columns to Fit
1. Hold down the Shift key and click on each column you want to size to fit, or click 'Select All' to highlight all columns.2. Go to 'Format' and select 'Column.'3. Click 'AutoFit Selectio...
How to Connect Excel Spreadsheets
1. Open the two spreadsheets that you want linked (you should have two open windows).2. Go to the worksheet you want to import data to and click on a cell that you want the imported data displayed.3. Type an '=' sign into the formula window (the blank line at the bottom of the toolbar.4. Navigate to the sheet you want to import the data from and click on the cell with the data. For example, click on cell A1.5. Go back to the spreadsheet that you want the data imported to. Excel will paste the name of the worksheet and the cell reference into the formula line. Press 'Enter' and the imported...
How to Disable Auto Backup in Excel
1. Click the 'File' tab and then 'Save As.'2. Click the down arrow next to Tools and click 'General Options.'3. Un-check the 'Always create backup' box to stop Excel from automatically backing up your fil...
Microsoft Excel Visual Basic Tutorial
1. Click on the 'Alt' and 'F11' keys from within any Excel worksheet or workbook to open the Visual Basic Editor (VBE). You can also access the VBE by clicking on the 'Developer' tab and then clicking on 'Visual Basic.'2. Click on the 'Insert' tab, and then click on 'Module' to open a blank window for your program.3. Type the word 'Sub' (for subroutine) or 'Function' into the window, followed by the name of your program. For example, 'Sub italics()' is the first line of a subroutine called 'Italics.' If the VBA returns a result such as a number, it's a function. If it performs a task without...
Monday, July 25, 2011
How to Merge Cells on an Excel Spreadsheet
1. Click the 'Home' tab on the command ribbon.2. Click and drag to select two or more adjacent cells. A black border surrounds the range of cells. The highlighted cells display one white cell in the upper-left corner and one or more blue cells. Only this white cell retains its data.3. Click the down-arrow in the 'Merge Center' command in the 'Alignment' group. The three merge options include 'Merge Center,' 'Merge Across' and 'Merge Cells.'4. Click the preferred merge option. The cells become one larger ce...
How to Create a Table in Excel 2003
1. Open Excel on your computer, and start a new spreadsheet.2. Enter your data into the spreadsheet. If the table is to be row-oriented, enter the desired calculation formulas to the left and just below the data. If the table is column-oriented, enter the calculation formulas above the first value and in the column immediately to the right of the data. Assistance for understanding formulas is available in the Excel 'Help' section.3. Go to the 'Data' tab, and select 'Table.'4. Enter the cell number where the calculated data should appear in the 'Column input cell' field or 'Row input cell'...
How to Remove Mass Links in Excel 2007
1. Open the Microsoft Excel 2007 file on your computer that contains the links you want to remove.2. Enter '1' into a blank cell within your worksheet. Right-click on the cell with your mouse and then click on the 'Copy' option.3. Hold down the 'CTRL' key on your keyboard and select each link in your worksheet that you want to remove.4. Click the 'Home' tab. Click the arrow below the 'Paste' field in the 'Clipboard' group. Click the 'Paste Special' option.5. Click the 'Multiply' option and then click the 'OK' button. Click the 'Home' tab again and then click the 'Cell Styles' option...
Sunday, July 24, 2011
How do I Add the Microsoft Excel 11.0 Object Library?
1. Insert your Office 2003 disc. The Office 2003 Setup window will open.2. Select 'Add or Remove Features.' Click 'Next.'3. Check the 'Choose advanced customization of applications' box. Click 'Next.'4. Click the plus sign next to 'Microsoft Office Excel.' This expands that section of the applications and tools list.5. Click the down arrow beside '.NET Programmability Support.' Click 'Run from My Computer.' Click the 'Update' butt...
How to Remove Grid Lines
For Excel 20031. Select the cells in the spreadsheet from which you want to remove the grid lines. Do this by left-clicking on the mouse and holding it down as you move the cursor over the cells you want to select.2. Click on the 'Format' menu at top of page.3. Click 'Cells.'4. Click on the 'Patterns' tab.5. Click on the background color of the spreadsheet. In most cases, the color is white.6. Click on the 'Border' tab.7. Click on the arrow in the 'Colors' box. Click on the color of the gridlines. In most cases, the color is 25% gray.8. Click 'Outline.'9. Click on 'OK.' The...
How to Determine if a File Exists in Excel VBA
1. Copy the following code:Option ExplicitFunction FileOrDirExists(PathName As String) As Boolean'Macro Purpose: Function returns TRUE if the specified file' or folder exists, false if not.'PathName : Supports Windows mapped drives or UNC' : Supports Macintosh paths'File usage : Provide full file path and extension'Folder usage : Provide full folder path' Accepts with/without trailing '\' (Windows)' Accepts with/without trailing ':' (Macintosh)Dim iTemp As Integer'Ignore errors to allow for error evaluationOn Error Resume NextiTemp...
How to Share Excel Macros
1. Write the macro to be shared in an empty spreadsheet. Click the 'View' tab on the ribbon menu. If you are going to record the macro, click the small arrow at the bottom of the 'Macros' button and then select 'Record Macro...' from the drop-down menu. If you elect to write the macro using Visual Basic for Applications, select 'View Macros...' from the drop-down menu. Provide a macro name and click 'Create' on the 'Macro' dialog.2. Save the spreadsheet as an 'Excel Macro-Enabled Workbook' file. If the macros are saved in other types of Excel files, they may not be visible due to internal...
How to Link a File to MS Excel
1. Open the saved Excel file.2. Click on the cell or object to insert the file link.3. Click on the 'Insert' tab on the command Ribbon.4. Click on the 'Hyperlink' button on the 'Links' group. A window will show 'Insert Hyperlink.'5. Click on the file name from the list of files in the box.6. Click 'OK.' The colored hyperlink will show on the Excel worksheet.7. Save this Excel fi...
How to Summarize Data in Excel
1. Use the Subtotals method. Choose one customer name by selecting a cell in column A. Click the 'AZ' sort button--you can find this on the standard toolbar. Next, select 'Data' and then 'Subtotals.' The Subtotals dialog box should appear. Make sure you've checked off 'OldSales,' 'NewSales' and 'Total.' Click 'OK' to see the summary data in bold on your spreadsheet. Press the '2' in the top left corner of your spreadsheet to see only the summary data.2. Try the Consolidate method. Select a blank cell to the right of your data. Click 'Data' and 'Consolidate.' In the 'Reference' section in the...
How to Make a Pie Chart Using Microsoft Office 2003
1. Click 'Start,' 'All Programs' and then click 'Microsoft Office Word 2003.'2. Click 'File' from the top menu and then click 'Open.' Click the file to which you want to add a pie chart and click 'Open.'3. Click 'Insert' from the top menu and click 'Object' from the drop-down menu.4. Click 'Create New.'5. Click 'Microsoft Graph Chart' from the 'Object Type' box and click 'OK.'6. Click inside a cell on the datasheet window and enter the text or numbers that you want to display in the pie chart. The default chart type is a bar graph.7. Double-click the chart. A box appears around the...
How to Display a Count of Cells on the Bottom of the Window in Excel
1. Open Microsoft Excel and load the spreadsheet you want to edit. This can be an existing file, or you can just use the default new page to test the functions.2. Enter data in the first column. Use the following data to populate the 'A' column on the spreadsheet:Price2.003.004.003. Use the Count or CountA function in the bottom field. The following code is an example of how to use the Count function:=count(a1:a4)To use the CountA function, enter the following code instead:=countA(a1:a4)The equal sign is used to denote a formula. This is prefixed in all cells that contain formulas. The two...
How to Turn Off Filter Arrows in Microsoft Excel 2007
1. Open the Excel sheet that has the filtering option turned on.2. Click the 'Data' tab on the Microsoft Ribbon.3. Click the highlighted 'Filter' button in the 'Sort Filter' section to turn off all filter arrows in the spreadshe...
Saturday, July 23, 2011
How to Sum the Span of Cells of Microsoft Excel 2003
1. Create the numbers in the cells that you wish to sum the span of. To sum the span of cells, you will first need to create a series of numbers that you wish to be summed. You can create lists vertically or horizontally depending on your preference.2. Select the cells that you wish to sum by highlighting them. To highlight the cells that you wish to sum the span of, simply left-click on the first cell and hold the mouse button in as you drag the cursor over the other cells that you wish to sum. Release the mouse button once you have highlighted the desired cells, and they will stay highlighted.3....
How to Enter a Comment in Excel 2007
1. Open an Excel 2007 spreadsheet.2. Use one of the following methods to bring up a comment box: Right-click the cell and select 'Insert Comment' from the list; right-click the cell and press 'Shift' 'M'; use the keyboard shortcut 'Shift' 'F2'; or select the cell, click the 'Review' tab on the ribbon and select 'New Comment' from the 'Comments' group.3. Enter your text into the comment box. Highlight the text and add any formatting options including bold, italics, color, font type and size. Click outside the 'Comments' box to close ...
Friday, July 22, 2011
How to Check for Duplicate Records in Excel
1. Click the 'Home' tab.2. Click 'Conditional Formatting' in the 'Styles' box in the Office ribbon.3. Select 'Highlight Cell Rules' and then 'Duplicate Values.' The 'Duplicate Values' box will open.4. Select 'Duplicate' from the drop-down box and choose the color for the highlighted duplicates. Click 'OK.' Excel will show all duplicate values in the color that was chos...
How to Change the Vertical Axis Numbers on an Excel Bar Chart
Using Chart Tools1. Click on the vertical axis numbers on the chart area. This action opens a selection box.2. Click on the 'Layout' tab under 'Chart Tools.'3. Choose 'Axes->Axes->Primary Vertical Axis->More Primary Vertical Axis Options.' Choose the factor you would like to change. For example, if you want to change the interval between tick marks to 100, write '100' in the top box.4. Press 'Close.' Excel will automatically update the graph with the new numbers.Manual Change5. Click on the number you want to change in the Excel worksheet (not on the graph). For example, if...
How to Align Double
1. Create a new Excel 2007 spreadsheet from the circular 'Office' tab of the Ribbon. Select 'New' and then 'Blank Workbook' to create a new, blank spreadsheet. Save your document as an Excel 2007 file format from the 'Save As' button from the 'Office' tab.2. Switch to 'Page Layout' view from the 'View' tab of the Ribbon. Page Layout view allows you to see the parameters of your print document including margins, column width and row height. It also allows you to view the alignment of your cells to prepare them as tags.3. Right-click the column header and select 'Column Width' to input your...
How to Edit a Picture in Excel
1. Right-click on the 'Start' button in the bottom-left corner of the Windows desktop.2. Select 'All Programs' and then select 'Microsoft Office.'3. Scroll down to the bottom of the Microsoft Office programs choices and select 'Microsoft Office Tools.'4. Scroll down in the tool selection category and select 'Microsoft Office Picture Manager.'5. Click 'Pictures' in the pane on the left side of Microsoft Office Picture Manager.6. Select the picture that is in the Excel spreadsheet.7. Click 'Edit Pictures' in the tool bar of the picture manager and an 'Edit Pictures' pane will open.8....
How to Merge Two Excel Workbooks Into One
1. Save your original file as a shared file. With the workbook open, Under 'Tools' select 'Share Workbook.' Check the 'Allow Changes' box, then click the 'Advanced' option. Input the number of days you'd like to allow track changes for. It's a good idea to set this for several days after you plan to merge the two workbooks or meet with the individual(s) in charge of the other workbooks, in case you need to make additional changes at that time. Click 'OK.' Save when prompted.2. Create a copy of the original workbook. Under 'File' select 'Save As' and give the new workbook a different title....
Thursday, July 21, 2011
How to Update Links in Excel
How to Update Links in Excel1. Open Excel workbook in which you want to update links.2. Select the 'Edit' menu from the menu list.3. Select 'Links' from the drop-down list.4. In the 'Edit Links' pop-up window, select 'Update Value...
Wednesday, July 20, 2011
How to Compare Merge Workbooks That Are Greyed Out
1. Share the workbook that needs to be compared and merged. This can be done by clicking 'Tools' then 'Share Workbook.' This is an initial step that needs to be completed before sending the workbook out to be reviewed.2. Save all workbooks that have changes or additions with unique file names that differ from the original workbook's file name. Also, ensure they are all saved in the same folder as the original workbook before attempting to use the Compare and Merge feature.3. Click the 'Compare and Merge Workbooks' buttons located on the toolbar. When prompted, select the workbooks that you...
Tutorial on Microsoft Excel 2003
1. Click 'Start,' 'All Programs,' 'Microsoft Office,' and then Microsoft Office Excel 2003. Excel 2003 will launch.2. Click on any cell in the worksheet to select it.3. Type numerical data or text in a selected cell and then press 'Enter' to enter information into the cell.4. Click on the 'Sheet' tabs at the bottom of the window to work in different worksheets. Each sheet acts as its own separate workspace within the same Excel workbook.5. Click on a cell, hold down the mouse button and then drag the mouse across the spreadsheet to select a block or range of cells. Selecting ranges of...
Tuesday, July 19, 2011
How to Send a Mass Email From an Excel Spreadsheet
Send a Mass Email from an Excel 2003 Spreadsheet1. Make an Excel spreadsheet containing all of the information you will need for your mass email, such as your contacts' names and email addresses. Enter a heading into the first cell in each column. Save and close the Excel spreadsheet.2. Open Outlook and minimize it. Open Word and type your email, leaving blanks where you want the recipients' names and other personal information to go. This data will come from the Excel spreadsheet you created.3. Go to the 'Tools' menu. Point to 'Letters and Mailings' and select 'Mail Merge.' Under 'Select...
How to Make a Ledger
1. Open Excel by double-clicking the Excel icon on your desktop. If you don't have an Excel icon on your desktop, you can click 'Start' and then 'All Programs,' followed by 'Microsoft Office.' Then select 'Microsoft Excel.'2. Enter 'Entry Date' in the A1 field. Enter 'Account Name' in the B1 field. Enter 'Debit/Credit' in the C1 field. Finally, enter 'Amount' in the D1 field.3. Enter your financial transactions into these four fields to create the general ledger. For example, you purchased office supplies on July 1, 2010, in the amount of $50. For this transaction, you'd enter '7/1/10' in...
How to Write an Excel VBA Program
1. Find the Excel file that you want to augment by including a VBA program. Double-click on the file to open the spreadsheet.2. Hold down Alt and press F11 to open the VBA editor. The editor will open up in a new window.3. Right-click on any of your worksheets, which will be listed in the thin column on the left side of the VBA editor. Move your mouse over 'Insert' and select 'Module.' This will add a new module, which is just something to hold your VBA program, to the list. Double-click on the module that appears, and the right half of the VBA editor will turn white.4. Click on the right...
How to Hide Gridlines in Microsoft Excel 2003
1. Open Excel 2003 and select a workbook to print. Click 'File' on the menu bar and select 'Open.' Browse your files for the workbook. Click the workbook and select the 'Open' button.2. Click 'Tools' on the menu bar once the Excel 2003 workbook opens and select 'Options.'3. Click the 'View' tab once the Options dialog box opens.. Locate the 'Windows Options' section and uncheck 'Gridlines.' Click 'OK' to confirm your changes. The gridlines on your spreadsheet are now hidden from vi...
Monday, July 18, 2011
How to Add Cells in Excel
Add Blank Cells to an Excel Spreadsheet1. Highlight the location for the new cells.2. Click on a cell and drag the mouse until all the cells in the location have been highlighted. The number of cells you select should be equal to the number of cells you want to add.3. Click on 'Insert' on the toolbar and select 'cells' from the menu.4. Select 'Shift cells right' or 'Shift cells down.' 'Shift cells right' will move the data in the cells you have selected to the right and put new cells where the old data used to be, while 'Shift cells down' will move the data down, making space for the new...
How to Draw Separating Lines in Excel
Draw a Border Line1. Open the Excel worksheet.2. Click the “Home” tab on the command ribbon.3. Click the arrow next to the “Border” button in the 'Font' group to display a list of border styles. To create a custom line, click the “Draw Border” option in the “Draw Borders” section. The pointer converts to a pencil symbol.4. Click and drag the cursor on the worksheet to start the line.5. Release the mouse to end the line. Press any key to convert the pencil symbol back to a pointer.Insert a Pre-Defined Border6. Open the Excel worksheet.7. Click and drag on the range of worksheet cells...
How to Make a Timeline Using Microsoft Excel
1. Open Excel 2010 and select the 'File' tab on the ribbon. Click 'New.' Type 'Timeline' in the search box. Review the timeline templates that appear. Click 'Timeline.' Click the 'Download' button. The template downloads to your computer.2. Review the timeline and update the default timeline. Click on the existing time in the timeline and add your day, month or year sequence to the timeline.3. Update the text boxes with your timeline data. Remove the timeline guide by clicking the outer edge of the box and selecting 'Delete' on your keyboard. Save your change by clicking the 'Save' icon...
Sunday, July 17, 2011
How to Insert a Radio Button Into Excel 2003
1. Open Excel 2003. Right click in the gray area of your standard toolbar. You will see a list of available toolbars. Click the 'Form' toolbar.2. Click the button that has the radio dial button. If you point to it, it will display 'Option Button.' The cursor will become a thin ' .' This is your indicator to draw the radio button control field. Click and drag until you get a small box. The box will display the name 'OptionButton1.'3. Rename the radio button by right clicking on the newly drawn button. Select 'Edit Text.' Type a new name over the default na...
How to Create a Border in Excel
1. Open Microsoft Excel.2. Select the cell or range of cells you want to create a border. For adjacent cells, click the first cell you want to include in the border then drag your mouse until the last cell. This highlights the cells to be included in the border. For nonadjacent cells, click the first cell then press the 'CTRL' key and continue clicking the other cells you want to include. For all cells within the worksheet, click the small box on the corner between the first row (row 1) and the first column (column A.) This selects all the cells.3. Right-click on the cells included in the...
How to Convert Birth Date to Age in Excel
1. Open Excel and bring up the document with birth dates you want to convert.2. Enter birth dates for each person in one column if there are no birth dates in your spreadsheet yet.3. Click on an empty cell where you want the age to appear. To make it easy, create a column next to the birth date column and label it 'Age.' For example, if birth dates are in column B, age might be in column C.4. Enter the following formula in the cell and press 'Enter':=DATEDIF(C2,TODAY(),'Y')Where it says 'C2,' replace this with the cell number that contains the first birth date. Using 'TODAY' will calculate...
How to Create an Excel Invoice Number Counter
1. Create an invoice in Excel, and save the file as 'Invoice.xls'2. Enter your initial invoice number in cell A1. For example, if your initial invoice number is 300, you'd type 300 in cell A1.3. Press the 'Alt' and 'F11' keys at the same time. This will open the Visual Basic editor.4. Within the Visual Basic editor, press 'Ctrl' 'R' to open the Project Explorer window.5. Double click the line that says VBA(Invoice.xls). When the menu opens up, double-click 'ThisWorkbook,' which is a special function in Visual Basic detailing how operations on this workbook will operate. A pane will...
How to Make Frequency Tables
Make Frequency Data Ranges1. Load your data into Excel. It is easiest to have the data in columns per question, and the responses from the different participants in rows. For example, you might have 100 responses to a survey in your data set. Start numbering your first row with the question numbers, and the respondent responses in the first column in cell A2. Cell A1 would be blank, but cell A2 would have the first respondent's answers to the questions going across. Cell A2 would have the first question's results, cell A3 would be the second question, and so on, to the end of the questionnaire.2....
How Do I Calculate CAGR in Excel?
1. Lay out the list of your data on a line either vertically or horizontally. Do not skip any spaces because the spreadsheet will interpret them as zero values.2. Arrange the numbers in exactly the chronological order that they occurred. If the data list is short, you can even identify each year next to the figure. For example,$5,000 2007$6,250 2008$7,000 2009$7,900 20103. Use the formula to calculate CAGR using the numbers in you data set.(Last number/first number)^(1/n)-1n is the difference of the years. In this case 2010 - 2007 = 3.4. Plug in the numbers to find the solution.CAGR...
How to Set the Advanced Filter in Microsoft Excel 2003
1. Open the advanced filter properties box. Scroll to “Data” and click on “Filter.” Select “Advanced Filter” from the submenu.2. Set the list range. Click on the “List Range” field to activate it and then scroll with cursor as you depress the mouse button and highlight the range of cells and columns that you wish to filter; the information will automatically be added to the field; or you can enter the range of cells in Excel format on your own.3. Set the criteria range. Click in the “Criteria Range” filter and then click on a single cell in the workbook which contains the criteria; it will...
Saturday, July 16, 2011
How to Use Subtotals and Totals in an Excel Spreadsheet
Creating Labels for Your Spreadsheet1. Start Microsoft Excel and open the file you want to change.2. Enter label titles for your columns and rows.3. Drag over the column that contain the label titles.4. Click B (boldface icon) on the tool bar.5. Drag over the row that contain the label titles.6. Click B (boldface icon) on the tool bar.Creating Subtotals and Grand Totals7. Drag over the columns and rows for which you want to create subtotals and grand totals.8. Open the Data menu and select Subtotals.9. In the Subtotal dialog box, select the columns names you want subtotaled in...
How to Insert and Size Pictures in Microsoft Excel 2003
1. Open and insert the picture file. To open a picture file, scroll to the “Insert” tab and then select “Picture.” Under the submenu, select “From File” and browse to the desired image file. Click “OK” to insert it into the spreadsheet.2. Drag the picture where desired. Once the picture is inserted, you will noticed circular drag points that border it. You can click on the middle of the picture and hold, then drag the picture around the spreadsheet.3. Resize the picture using drag points. To resize the picture using these circular drag points, left-click and hold on a drag point and then...
How to Alphabetize an Excel Spreadsheet
1. Right-click on the Excel workbook you want to alphabetize. Click 'Open,' then click on the worksheet tab at the bottom of the workbook that contains the data that you want to organize.2. Click the upper left corner of the spreadsheet, just above Row 1 and to the left of Column A, to select all of the cells in the sheet.3. Click 'Sort and Filter' on the home tab (it is on the far right side of the window) and then select 'Custom Sort....'4. Change the entry in the 'Sort by' drop-down menu to the column you want to alphabetize and click 'OK.' You can also arrange entries in reverse alphabetical...
Friday, July 15, 2011
How to Link Sheets in Excel 2007
1. Highlight the content of the first (original) sheet you want to link in Excel 2007.2. Click on the worksheet tab (at the bottom of your spreadsheet) of the worksheet you want to link to.3. Select the 'Home' tab. Choose 'Paste' and 'Paste Link' from the 'Clipboard' group to link to the she...
How to Make a Linear Vs. Logarithmic Chart
1. Click on the Excel logo on your desktop to open Excel.2. Click on a cell in a new Excel document and type in the first of your data points. Press the enter key and enter the second data point in the cell below. Repeat this until you have entered all your data points.3. Click on the first cell in your column of data and then drag down, keeping the left mouse button pressed, until you have highlighted your entire column of data.4. Click on the 'Insert' tab in Excel and then click the 'Line' button in the charts section of the ribbon. A menu should appear.5. Click on one of the 2-D chart...
How to Hide an Excel Worksheet so Another User Can't Unhide It
1. Open the Excel worksheet.2. Click the sheet tab you wish to hide. If the tab is not visible at the bottom of the screen, click the tab scrolling button until the tab comes into view, then click the tab.3. Click the 'Home' tab on the command ribbon.4. Click the down-arrow for the 'Format' button in the 'Cells' group. A list of options appears.5. Click the 'Hide Unhide' option in the 'Visibility' section.6. Click the 'Hide Sheet' option. The worksheet hides from view.7. Customize the command ribbon so the 'Cells' group and its 'Format' button also hides from view by clicking the...
Thursday, July 14, 2011
How to Write Macros for Graphs in Excel
1. Map out the process. You can create a graph from a macro as long as the process is always the same; that is, you will need to pick the same graph type every time. The best way to ensure you don't miss any steps is to map the process out first on a piece of paper. You can write out steps or use blocks and arrows, whichever is most comfortable and easy to read. This process has the potential to save a lot of time in terms of reducing trials and errors.2. Open the report you want to create a graph for. The challenge with creating a macro for graphs is that the information must be pulled from...
How to Modify the Data Source in Excel 2007
1. Launch Excel 2007.2. Click the 'Windows' button in the top-left corner. Scroll down an click 'Open' and then find the spreadsheet that you want to modify. Highlight the field or fields that you want to modify.3. Click the 'Options' tab. Click the 'Change Data Source' button in the 'Data' group.4. Click the radio button either next to 'Select a table or range' or 'use an external data source.' Type in the Table/Range' in the field or hit the 'Choose Connection' button and find the source that you want to link with your Excel file.5. Click the 'OK' butt...
Rotate Text in Microsoft Excel
1. Open Microsoft Excel. Then open an existing worksheet that contains text you would like to rotate-orr create a new worksheet.2. Select the cell or cells that contain text you would like to rotate by clicking on a single cell, dragging your mouse across them, or holding down the 'Ctrl' key and clicking on each cell.3. Right-click the selected cells and choose 'Format Cells.' Click on the 'Alignment' tab.4. Click the 'Horizontal' drop-down button in the 'Text Alignment' section and choose 'Center.' Click the 'Vertical' drop-down button as well, and choose 'Center' there, too.5. Move...
How to Create an Excel 2007 Chart With Text
1. Click the 'Insert' tab of the ribbon at the top of the page. In the 'Charts' area, select the type of chart you want to create. Excel 2007 provides many chart options including bar graphs, scatterplots and pie charts.2. Click on the 'Select Data' button in the 'Design' section of the ribbon. In the 'Chart Data Range,' select the entire area that contains your chart data. In the 'Legend Entries (Series)' section, click 'Add' to select each segment of information.3. Define the 'x' values that are displayed on the horizontal axis and the 'y' values that are displayed on the vertical axis....
Wednesday, July 13, 2011
How to Create a Report to Display Quarterly Sales in Excel 2007
1. Open a new Excel workbook. A blank spreadsheet will appear on the page.2. Click on cell 'A1,' which is the top-left cell in the spreadsheet. Type 'Quarter' into the cell and press 'Enter.' Excel will automatically select cell 'A2,' as it is directly beneath the first cell.3. Type the names of the quarters into the cells directly beneath the first one. Each quarter should have its own cell, and be sure to include year numbers of your data spans more than four quarters. After you type the name of each quarter, press 'Enter' to go to the next cell.4. Select cell 'B1,' which is directly...
How to Delete a Single Cell Excel 2003
1. Click the Excel cell you wish to delete.2. Select 'Edit' at the top of the Excel window. Click 'Delete' from the menu, which opens a dialog box.3. Select one of the following options in the box: 'Shift Cells Left,' Shift Cells Up,' 'Entire Row' or 'Entire Column.'4. Click the 'OK' button to close the dialog box, and complete the deletion of the ce...
How to Restore Microsoft Excel to Its Defaults
Option One1. Open up Microsoft Excel.2. Click on the 'Tools' menu and then click 'Customize.'3. Right-click the menu you want to restore and then click the 'Reset' button. This will restore the menu to its original settings. Consequently, restoring all the menus will restore Microsoft Excel to its defaults.Option Two4. Click the 'Help' menu in the upper right-hand corner.5. Select 'Detect and Repair' from the scroll-down menu. Make sure the 'Restore my shortcuts while repairing' is selected.6. Click 'Start.' This process will restore Microsoft Excel to its original settings (i.e.,...
How to Repair a Corrupt XLSX File
1. Navigate to the folder containing the XLSX file you want to repair.2. Right-click the file and select 'Copy' and then right-click on a blank space within the folder and select 'Paste.' This action generates a backup copy of the corrupted file.3. Open Microsoft Excel 2007 or later.4. Click the 'Office' button in the top left corner of the window and select 'Open.'5. Navigate to the folder containing the copy of the XLSX file you generated previously and select it by single-clicking its icon.6. Click the arrow located to the right of the 'Open' button and select 'Open and Repair.'7....
How to Calculate an Interest Rate Using Excel
1. Do your homework. To find the interest rate, you will need to know the time period or length of the loan or investment, the monthly payments and the principle of the loan or investment.2. Create an Excel spreadsheet to determine your interest rate. Enter a list of headings-Current Value, Future Value, Monthly Payment and Number of Payments. If you begin in cell A1 with the heading 'Current Value,' the remaining headings listed here will fall in cells A2, A3 and A4.3. Enter the information required in the cells to the right of the headings. If you began in cell A1 as suggested above, you...
Tuesday, July 12, 2011
How to Do Bar Charts in Excel 2007
1. Open Microsoft Excel 2007 by double-clicking on the Excel 2007 icon. After Excel loads, open up the spreadsheet you want to make a bar chart for by clicking 'Open,' then selecting the file.2. Arrange the data on your spreadsheet into either columns or rows. Essentially, you want one heading, followed by data in the cells directly to the right of it (for rows) or directly below it (for columns). Do this for each bar you want to have in your chart. For example, if you want three bars in your bar chart, you'll need three headings, each followed with data.3. Highlight all of the data. To...
How to Draw a Scatter Plot on Microsoft Excel
1. Open a blank worksheet in Microsoft Excel. Enter two columns of data you want to plot on a scatter chart. The data should be two sets of value that intersect. For instance, in column A, you could enter daily rainfall in inches, and in column B, enter temperature in degrees. These are two sets of data that intersect at single data points. Enter the title of each set of data in the first cell of each column.2. Highlight the cells you want to plot on the chart.3. Click the 'Insert' tab. In the 'Charts' group, select 'Scatter.'4. Select a chart icon for the scatter plot you want to use....
How to Disable a Macros Using VBA Excel
1. Open the Excel workbook in which you want to change your macro settings.2. Click the Microsoft Office icon button in the top, left of your window.3. Select 'Excel Options' and click 'Trust Center.'4. Click 'Trust Center Settings' and select 'Macro Settings.'5. Uncheck the box for the security option 'Trust access to the VBA project object model.'6. Click 'Apply' and close your settings windo...
Monday, July 11, 2011
How to Convert to VBA in Excel 2007
1. Open Excel. Click the Office button and select 'Excel Options' from the bottom of the menu that opens.2. Select the 'Popular' tab from the menu on the left. Click the box next to 'Show Developer Tab in the Ribbon.' This will allow you access the VBA editor function.3. Click the 'Trust Center' tab and select 'Trust Center Settings.'4. Choose 'Enable All Macros' under 'Macro Settings' and then check the box next to 'Trust Access to the VBA Project Object Model.'5. Click 'OK.' After you exit to 'Excel Options,' click 'OK' again to go back to Excel. VBA will now be activat...
How to Find Duplicate Cell Content on Excel 2003
1. Place the cursor in cell A1 (or the first cell where you want to test for duplicates). Click the 'Format' menu and then click 'Conditional Formatting.' Click the down-arrow in the box under 'Condition 1' and select 'Formula Is.' Enter the following formula into the text box to the right of 'Formula Is.'=COUNTIF(A:A,A1) > 12. Click the 'Format...' button on the right side of the window. Click the 'Patterns' tab and select an easy-to-find color such as red or yellow. Click 'OK.'3. Click the 'Edit' menu with the cursor still in the cell where you entered the formula and select 'Copy.'...
How to Create New Table Style in Excel 2007
1. Locate the Styles group under the Home tab. Click 'Format as Table.'2. Choose an existing table. Table styles are organized under light, medium and dark. Each style includes a variety of color choices.3. Click anywhere in the table to bring up Table Tools. Select the Design tab and click 'More.' Select 'New Table Style.' Type a name for the new table you are creating in the name box.4. Makes changes to the Table Element box by clicking on each element. Click 'Format' to format or 'Clear' to clear the existing formatting.5. Click the options you want from the Font, Border and Fill...
Subscribe to:
Posts (Atom)