1. Type the list items into cells in a column of the current worksheet or another worksheet. Name the list if you enter it into a different worksheet. Select the list and click inside the 'Name' box at the top left corner of the worksheet next to the 'Formula Bar.' Type a name for the list and press 'Enter.'2. Click on the cell in which you want to create a drop-down list. To enter the list into several cells, press and hold the 'Ctrl' key and click on each cell in which you want to use the list.3. Go to the 'Data' menu in Excel 2003. Click 'Validation' and go to the 'Settings' tab of the...
Browse » Home » Archives for March 2012
Wednesday, March 28, 2012
Tuesday, March 27, 2012
How to Count All Rows With a Blank Cell in Excel 2007
1. Type '=COUNTBLANK(' in an empty cell.2. Highlight the column of data you want to calculate the number of empty cells.3. Press 'Ente...
How to Create a Report in Microsoft Excel 2007
1. Complete the data and calculations in your worksheet. Even if your data and calculations span multiple worksheets, you can pull the data together into one report.2. Roughly lay out how you want the report to look, including what you want in headers and footers, what text you want to be emphasized and what kind of graphical elements you want to include.3. From the Insert tab on the Office Fluent Ribbon, click 'Header Footer' in the Text group to design the headers and footers, which are the text that displays on every page of the report. This opens the Header Footer Tools ribbon, where...
How to Calculate Sample Variance Using Excel
1. Add the numbers in the first column of your Excel 2007 document. For example, add the numbers 100, 200, 300, 400, 500 and 600 in A2, A3, A4, A5, A6 and A7.2. For this example, click on the 'A9' cell. This is the cell where you will calculate the variance. When you calculate another variance, choose any cell at the bottom of the list of numbers you are using.3. Click on the 'Formulas' tab while you are in the 'A9' cell. Click on 'Statistical' on the drop-down menu.4. Click 'Insert Function' on the top left-hand side of the Excel spreadsheet. The 'Insert Function' window will pop open.5....
How to Add a Subtotal to a Pivot Table
1. Select your items, row, or column that you would like to subtotal in your pivot table report.2. Click 'Field Settings' in the 'Active Field' group on the 'Options' tab.3. Click 'Automatic' under 'Subtotals' to subtotal the outer row or column label.4. Select 'Custom' under 'Subtotals' to choose a function for an inner row or column lab...
Monday, March 26, 2012
How to Adjust Chart Fonts in Excel
1. Start Microsoft Excel 2007 and open a spreadsheet from your files that contains a chart in which you want to change the formatting of the font in that chart.2. Right-click on the text in the chart that you want to change the font or other formatting feature. The 'Mini Toolbar' will appear over the shortcut menu that appears. The 'Mini Toolbar' contains everything you need to adjust the chart font.3. Use the 'Font Type' drop-down list to choose a new font for the selected text. The 'Font Size' drop-down list allows you to change the size of the font that you have selected. The 'Increase...
How to Add a Counter in Microsoft Excel
1. Log on to your computer and open Microsoft Excel. Create a new spreadsheet.2. Label the columns in your spreadsheet by giving them descriptive titles. For instance, you could create a 'Description' column, a 'Price' column, a 'Quantity' column and a 'Total Cost' column for a spreadsheet used to track orders.3. Add a column and give it the title 'Count.' Go down to the bottom of the spreadsheet and type '=COUNT(firstrow:lastrow).' For instance, if the first cell in your spreadsheet that contains data is B2 and the last cell containing data is B50, your formula would read '=COUNT(B2:B5...
How To Enable Excel Macros
1. Launch Microsoft Office Excel, click the 'File' tab and click 'Options' to open the Excel Options Window.2. Click 'Trust Center' to open the Trust Center Window from the left pane of the Window.3. Click 'Macro Settings' and choose the option that says 'Enable all macros (not recommended, potentially dangerous code can run).' Click 'OK' and click 'OK' again to close the Windows.4. Exit Excel and restart it to enable all macr...
How to Select Multiple Non
1. Select the first range of cells by holding down the 'Ctrl' key, selecting the fist cell of interest with the left mouse button, and mousing over the range. When you have finished highlighting the range, release the mouse button but keep the 'Ctrl' key depressed.2. Move the cursor to the next range of cells you would like to highlight.3. Press the left mouse key to highlight the cell, then (keeping the left mouse button down) highlight the next area you would like to select. When finished highlighting, release the left mouse button.4. Repeat Step 3 for as many ranges as you would like...
How to Convert XPS to Excel 2003
1. Download a preferred converter software. Websites such as Free Downloads Center or File Buzz have XPS-to-Excel 2003 converter software available for downloading. Simply click on the preferred software and select 'Run' when prompted. Install the software when the download is complete.2. Open Excel 2003. Select 'File' and 'Open.' Browse through the files to find the XPS file. Select the file and click 'OK.' The software will convert the XPS file into a format that Excel 2003 can read.3. Select 'File' and then 'Save As.' Create a preferred name and change the file extension of .xps to the...
How to Un
1. Open the spreadsheet you wish to modify. Hold down the 'Ctrl' key, and tap the 'A' key twice. This should select all of the cells in the spreadsheet. Press 'Ctrl C' to copy the cells to the clipboard.2. Click on the Microsoft Office logo at the top-left corner of the Excel screen and click 'New'. Click the cell labeled A1 to select it, then press 'Ctrl V' to copy the cells into the worksheet.3. Press 'Ctrl S' to bring up the 'Save As' dialog box. Click the 'Tools' button, and select 'General Options'. Make sure the password box is empty. Delete the contents of the box if necessary. Click...
Sunday, March 25, 2012
How to Create Macro Buttons in Excel 2007
1. Open an Excel workbook that contains a macro.2. Click the arrow to the right of the Quick Access Toolbar to reveal a drop-down menu; from that menu choose 'More Commands....'3. Click the left-hand drop-down menu in the resulting window. Choose 'Macros.'4. Double-click a macro in the resulting list to move it into the 'Customize Quick Access Toolbar' list.5. Click the 'Modify' button below the list of Quick Access Toolbar buttons.6. Choose a button image in the resulting window, and type a new display name for the button, if you want. The button name appears in a tooltip when you...
How to Make a Chart on Excel With Coordinates
1. Open a new Microsoft Excel 2010 spreadsheet.2. Click on cell 'A1' and type in the header for the first half of your set of coordinates. This header will not appear on the spreadsheet, but may be of use for referencing purposes when you are entering your data. Click on cell 'B1' and type in the header for the second half of your set of coordinates. This header will wind up as the title for your chart, and will be displayed in the chart's legend as well.3. Select cell 'A2' and enter the first part of your first set of coordinates. This part of your coordinates will be plotted on the horizontal...
How to Spell Check on Excel 2007
1. Launch Excel 2007 and open the file you want to spell-check.2. Click the 'Review' tab at the top of the screen.3. Click the 'Spelling' button in the 'Proofing' section on the ribbon at the top of the screen. The 'Spelling' button is represented by the letters 'abc.'4. Click one of the options in the 'Spelling' window when Excel encounters a potential spelling mistake. You can choose 'Ignore' if the word is spelled correctly or you can select the correctly spelled word under 'Suggestions,' then click 'Change' to fix the misspelled word.5. Click 'OK' after all spelling mistakes have...
Saturday, March 24, 2012
How to Add Gridlines to an Excel Chart
1. Start Microsoft Excel 2007 and open a workbook from your files that contains a chart to which you would like to add gridlines.2. Click the chart you want to add gridlines to so it is selected. You can tell the chart is selected because it will have a light blue border surrounding it.3. Select the 'Layout' tab at the top of the Excel 2007 screen to display the settings in the 'Layout' ribbon. Find the 'Axes' group in the 'Layout' ribbon.4. Choose the 'Gridlines' button in the 'Axes' group of the 'Layout' ribbon and point to 'Primary Horizontal Gridlines' to display the horizontal gridline...
How to Use Command Line Parameters in Microsoft Excel
1. Open a command prompt by clicking 'Start,' choosing 'Run,' entering 'cmd' and clicking 'OK.'2. Type 'excel' on the command prompt. Enter a space. Do not press Enter yet.3. Type any command line switches you'd like to use, separating them with spaces.'/r fileName.xls' will open fileName.xls in read-only mode.'/t fileName.xls' will create a new document, using fileName.xls as a template.'/s' will cause Excel to start in 'safe mode,' which prevents any start-up scripts from loading.You can see the full list of commands at the Microsoft Office page devoted to the topic.4. Press enter. This...
How to Upgrade Excel 2003 to 2007
1. Insert the Microsoft Office 2007 CD into your computer's disc drive. The automatic setup will begin.2. Type the product key into the text box. The product key is a 25-character key and is usually located on the back of the CD jacket.3. Click on the 'I accept the terms of this agreement' check box and then click on the 'Continue' button.4. Click on 'Upgrade,' which will remove Excel 2003 and replace it with Excel 2007. Wait for the installation to complete; it will take 15 to 30 minutes.5. Click on the 'Close' button to complete the installation of Excel 20...
How to Change a Reference to an Absolute in Excel 2007
1. Place a dollar sign in front of the column delimiter if you want to fix it, and in front of the row delimiter if you want to fix it.For example, you can change =VLOOKUP(A1,K1:P20,4) to =VLOOKUP(A1, $K$1:$P$20, 4) so that the reference to the table's location in K1:P20 won't change if you copy and paste or autofill this formula into empty cells elsewhere.2. Mix the dollar sign use in order to fill a table. For example, if column headings are in B1 through Y1 and row headings are in A2 through A80, and the cells of the table depend on the values in the headings, then you can enter one cell...
Friday, March 23, 2012
How to Import Access Query Into Excel
1. Open Access and create the following table of sample data, which has field names in the top row. Click 'Create>Table' to create the table.game,saledate,totalsalequake,12/1/2009,$6.01quake,12/3/2009,$7.98guitar hero,12/5/2009,$4.98guitar hero,12/6/2009,$5.98Save the table (by pressing 'control-s') with the name 'games.'2. Create a new query for the games table by clicking 'Create>Query Design,' right-clicking on the 'Query1' text in the query's tab, and clicking 'SQL View.'Enter the following statement in the SQL code window:SELECT games.* INTO myqueryresFROM gamesWHERE (((games.game)...
How to Create a Simple Budget in Excel 2003
1. Gather your budget information. Have your monthly statements and expenditure amounts ready.2. Double-click on the 'Excel' icon on your computer. If you can't find it on the desktop, open the program using the 'Start' menu on your toolbar.3. Decide whether you want to make your budget from scratch or use a template. Using a template is the easiest method. If you're going to use a template, skip to Step 5.4. Make your budget from scratch by setting up your worksheet any way you like. Start by naming the rows and columns and then add figures for various incomes and expenditures.5. Click...
Thursday, March 22, 2012
How to Match Destination Formatting in Word
1. Launch Microsoft Word and open your document. Highlight and copy or cut the source material that you want to paste. Click within your Word document where you want to paste the copied text. Right-click or depress the 'Control' button simultaneously with the letter 'v.' Recent versions of Microsoft Word will show a little clipboard icon titled 'Paste Options.' Click on the 'Paste Options' button and a drop-down box will appear. If this is the only material that you intend to copy and paste, select the radio-button to the left of 'Match Destination Formatting' and your content will paste,...
How to Unlock an Excel 2003 Spreadsheet
1. Open the Excel spreadsheet you want to unlock.2. Select 'Tools,' 'Protection' and 'Unprotect Sheet' from the toolbar. If you used a password to lock the spreadsheet, you'll be prompted to enter it in the 'Unprotect Sheet' pop-up box.3. Enter the password and press 'OK.' The spreadsheet will unlock.4. Click on 'File' and 'Save' to save your chang...
How to Merge Cells in Microsoft Excel 2003
1. Select the cells that you want to merge. To select cells for merging, left-click on the first cell and hold the mouse button in as you scroll over the cells that you wish to merge it with. Once you have selected the desired number of cells you can release the mouse button and they will stay highlighted.2. Use the cell merge tool. After you have selected the cells that you want to merge, click on the cell merge icon located on the command bar. The icon looks like a lowercase letter 'a' sitting on top of a small book that is open. Left-click on this icon to merge the cells.3. Set the text...
How to Use Data Tables in Excel
Excel 20031. Open a new workbook.2. Select the 'Data' tab.3. Click 'Table.' Excel will create a table on your worksheet.4. Create the data for your table. For example, you may have a set of values in column A and a formula you want to use in column B1 (there's no need to retype the formula, the table will carry it down for you).5. Open a workbook.6. Click on the 'Insert' tab, and then click 'Table.'7. Provide the source of the data for the table. For example, if your data is in cells A1 through A10, then enter 'A1:A10' in the source box.8. Click 'OK.' Excel will enter a table onto...
Wednesday, March 21, 2012
How to Add a New Workbook in VBA
1. Press the 'Alt' and 'F11' keys together in your workbook to open the Visual Basic Editor (VBE).2. Click 'Insert' and then click 'Module' to open a blank module window.3. Type the following:Sub AddNew()Set NewBook = Workbooks.AddWith NewBook.Title = 'All Sales'.Subject = 'Sales'.SaveAs Filename:='Allsales.xls'End WithEnd Sub4. Press 'F5' to run the program and open a new workbo...
How to Repair an Excel File
Attempt to Open the File1. Repair the file manually. From the menu, click “File,” “Open.” Select the damaged file. Click the arrow next to the “Open” button. Click “Open and Repair.” At the prompt, click “Repair.” The file may open successfully.2. If you have AutoRecover turned on, open the recovery file. Since recovery files are deleted when you quit Excel, try this before restarting. The default file location for AutoRecover files is “C:\\Documents and Settings\\\\Application Data\\Microsoft\\Excel.”3. Revert to the most recently saved version. On the menu, click “File” and “Open.” Select...
How to Create Division Formulas in Cells in Microsoft Excel 2003
1. Choose the cells you wish to divide. Make sure the cells contain divisible dates.2. Select a cell that will contain the formula. Pick an empty cell and left-click on it to activate and select it.3. Create the division formula. In the blank cell, you will create a sum formula. The formula will have to include the cell you wish to divide and the cell that contains that division integer. A division formula for two cells would read like this: =Sum (D1/D2). This tells the spreadsheet to take the data contained in cell D1 and divide it by the data contained in cell D2. The program will place...
How to Insert a File Location in Excel 2007
File Path in a Header or Footer1. Open the saved worksheet.2. Click on the 'Insert' tab on the command ribbon.3. Click on the 'Header and Footer' button in the 'Text' group. The 'Design' tab will appear.4. Click on the header or footer text box.5. Click on the 'File Path' button in the 'Header Footer Elements' group. A code will appear in the text box.6. Click away from this text box. The workbook's file path will display in the text box.File Path in a Hyperlink7. Open the saved worksheet.8. Click on one cell to insert this file location. You can opt to select the cell's text...
How to Convert a PDF File Into an Excel File
1. Download and install PDF-to-text conversion software, or convert your file online. There are several programs that will do this; some are free and some cost money but have free trial periods. See the Resources section for information.2. Process the PDF-to-text conversion, and save the text file.3. Open a blank Excel spreadsheet and import the text file. In Excel 2003, go to Data>Import External Data>Import Data. In Excel 2007, go to Data>Get External Data>From Text. The 'Text Import Wizard' will pop up.4. Choose the type of file that best describes your data. You will see...
How to Create Headers in Microsoft Excel 2003
1. Scroll to the “View” tab on the command bar to access the View menu.2. Under the “View” submenu, select “Header and Footer,” which will open a header-and-footer properties box. Click on the “Custom Header” button to access the Header Properties box.3. Enter the desired header text into the left, right and center sections. Using the icons inside the box, you can insert a picture, date stamp, pages to be included and files to be included, and you can even change the font type. Make sure you have selected a section, then click on the corresponding icon to add the desired features.4. Click...
How to Delineate Commas in Excel
1. Open Microsoft Excel and open the comma-separated list in another program such as Notepad.2. Click and drag with the mouse pointer to highlight each item in the comma-separated list. Right-click the list and then click 'Copy' to copy the list to the Windows clipboard.3. Click cell A1 in the upper left corner of the Excel spreadsheet. Right-click the cell and then click 'Paste' to paste the list from the Windows clipboard into Excel.4. Click the 'Data' tab at the top of the Excel window.5. Click to highlight column 'A' of the spreadsheet and then click the 'Text to Columns' button...
Tuesday, March 20, 2012
How to Make a Simple X
1. Open a blank worksheet in Microsoft Excel 2007.2. In the first cell, enter “X.” Press “Tab,” and enter “Y” in the next cell over. These will be the column headings for your X and Y data.3. Enter a series of X data points in the column under “X.” It’s a good idea to simply enter regular intervals of X to make up the horizontal axis. For instance, enter all the values ranging from “-5” up to “5” to display the line across a negative and positive axis.4. Calculate the Y value that corresponds to each X value. The equation format for a line graph is Y = MX B, where M is the slope of the...
How to Recover an Unsaved Spreadsheet in Excel
1. Open Excel after your computer has crashed or froze. The Document Recovery box will appear on the right side of the application after you open it.2. Scroll over the recovered file from that task pane and then click on the arrow that appears. A menu will then pop up.3. Click on the “Open” option to open the recovered file. You can also click on the “Save As” option to save the file so that you don’t risk losing any data again.4. Close out of the file if it seems to need to be repaired in any way still. Click the “File” option from the top toolbar menu and then click on the “Open” button.5....
How to Convert Excel 2007 Files to Excel 2000
If You Have Excel 20071. Open Microsoft Excel 2007 on your computer. The program file is typically located under the 'Microsoft Office' folder under the 'All Programs' option of the 'Start' menu.2. Open the Excel 2007 workbook you wish to convert. To open a workbook, select the 'Open' option from the Microsoft Office button in the top left-hand corner of the program. Select your file and click the 'Open' button to continue.3. Select the 'Save As' option from the Microsoft Office button. Select the 'Excel 97-2003 Workbook' option under the 'Save a copy of the document' section.4. Name your...
How to Make an Organizational Chart in Excel
1. Open Excel to a new workbook. Choose the 'Insert' tab, and in the Illustrations group, select 'SmartArt.'2. Choose 'Hierarchy' in the Choose a SmartArt Graphic gallery. Then select the organization chart layout and click 'OK.'3. Select a box and type in your text by clicking the [Text] label in the box and either pasting or typing the information to display in this box.4. Add more boxes to complete your organization chart. Click the box closest to where you want to add the box and choose from the following: 'Add Shape Before' adds the box on the same hierarchy level, but just before...
Monday, March 19, 2012
How to Plot Points on a Grid
1. Open Excel or another spreadsheet. Enter headers in cells A1 and B1 that correspond to your variables. For example, if you want to plot age against income, put 'Age' in cell A1 and 'Income' in cell B1. The A column will correspond to the X axis and the B column to the Y axis. (You can enter the data in any cells; just be consistent.)2. Enter the appropriate data. In our example, enter the data for age and income in the appropriate columns, starting in row 2 and continuing with one row per person.3. Plot the data. In a new cell, click 'Insert,' 'Scatter' and then click on the graph...
How to Extract the First Word of a String in Microsoft Excel
1. Click in the cell to the right of the one you want to edit. For example, click in B2 if you want to extract the first word from cell A2.2. Enter the following formula and then press the 'Enter' key:=LEFT(A2,FIND(' ',A2)-1)This tells Excel to find the first word in the word string in cell A2 and to extract it to cell B2 for our example.3. Copy the formula and apply it to all cells in the preceding column. To do that, hover your mouse over the black square in the lower right corner of the cell. When your mouse pointer changes to a plus symbol, click your mouse and drag do...
How to Make Column A the X
1. Select the data you want to use for both the x- and y-axes by clicking and holding the mouse button while selecting the cells.2. Select the 'Line' button from the chart options on the 'Insert' tab. Select the style of the line that you want to use. In the resulting graph, Excel will have drawn columns A and B as lines.3. Right-click the graph. After a menu appears, click the 'Select Data...' option to open the 'Select Data Source' dialog. Both 'Series 1' and 'Series 2' are listed under the 'Legend Entries' list.4. Click 'Edit' under the 'Horizontal Axis Labels' list to open the 'Axis...
How to Restrict Data Entered in Excel to a Specific Length of Text
1. Open the spreadsheet document you'd like to apply the text restriction to.2. Click the 'Data' tab, then click 'Data Validation.'3. Click 'Settings.'4. Select 'Text Length' in the 'Allow' field.5. Select 'Less than or equal to' as the rule type.6. Enter the maximum number of characters you'd like to allow in the text field.7. Select 'Ignore blank' to not count spaces and other blank characters toward the text limit. If spaces should count toward the limit, leave this unchecked.8. Type an optional message that will be displayed to the user if she attempts to enter too much information...
Sunday, March 18, 2012
How to Use Excel's Hyperlink Function
1. Know what the hyperlink function does. Hyperlink creates a shortcut or jump that opens a document stored on your computer's hard drive, the Internet or on a network server.2. Follow the syntax of the Hyperlink function, which is =HYPERLINK (link_location, friendly_name). The parameter link_location refers to the area where the document to which you wish to link is stored. Make sure you include the entire address including http:// when linking to the Internet, or the full path when linking to a document on a drive. The friendly_name parameter is optional. It tells Excel what text to display...
How to Remove a Cell Border
1. Select the cell or cells containing the border that you would like to remove. To select a single cell click directly on it, and to select multiple cells, click the left-mouse button and drag until all of the cells are selected.2. Click the 'Borders' drop-down menu. The 'Borders' drop-down menu is located on Home tab's Font menu on the toolbar at the top of the window. It has an image of a four squares with dotted or solid lines, indicating cells and borders.3. Click 'No Border' from the 'Borders' drop-down menu to remove the boarder from the cell or cells select...
How to Use Multiple Consolidation Ranges in Excel 2007
1. Launch Microsoft Excel from the 'Programs' or 'All Programs' menu. Click the 'Office Button' in the upper left corner of the program window and select 'Open.' Navigate to the file you wish to consolidate the ranges of, and select it. Click 'Open.'2. Click the first worksheet of the range that will be consolidated to select it. Click the 'Home' tab. Select 'Insert' and then 'Insert Sheet' to insert a new sheet for your consolidated range. Double click the sheet name tab at the bottom of the program window to select it. Type a name of your choice. Press the 'Enter' key on the keyboard.3....
How to Make a Check Box in Excel
Display Developer Tab1. Click the 'File' tab on the Ribbon and select 'Options' from the left panel.2. Click 'Customize Ribbon' to display two panes.3. Click the box next to 'Developer' to select it in the right pane. Click 'OK.'Make Check Box4. Click the 'Developer' tab on the ribbon.5. Click 'Insert' and select the check box. A cross will appear on the screen.6. Click and drag to insert a box with a check box and text. Select the text next to the check box, delete it and insert your own text.7. Right-click the check box you created and select 'Format Control' to format it. You...
How to Create Links in Excel 2007
1. Click the cell (text box) where you want to insert the hyperlink.2. Click the 'Insert' tab.3. Click 'Hyperlink' in the 'Links' group.4. Click 'Existing File or Web Page' under 'Link to.'5. Enter the full website in the address box. Include 'http://' in the address.6. Type the text that you want to display as the hyperlink name in the 'Text to display' box. This is so the person viewing the document sees the text that you chose, but it appears as a hyperlink. The web address will be hidden.7. Click 'OK' when do...
How to Calculate Tax Gratuity in Excel
1. Label your Excel spreadsheet columns, if desired. For example, you can type 'Sale' into cell A1, 'Tax' into cell B1 and 'Gratuity' into cell C1. You can choose your font type, size and style from the formatting toolbar. You can also choose to center your typing or justify it to the right or left. If the formatting toolbar is not showing, select the 'View' tab from the top then 'Toolbars' from the drop-down menu and select 'Formatting.'2. Format the Excel worksheet to display how you prefer. For example, to format the second row to display a dollar sign and two decimal places, click on the...
Saturday, March 17, 2012
How to Count How Many Times a Word Appears in Excel
1. Open the Excel document containing the text that you want to analyze. Select the first cell that contains the text. Hold down the 'Shift' key and select the last cell that hold your text. This will create a selection box around all of your text. Press 'Ctrl' 'C' to copy this information to the clipboard.2. Click on a new worksheet at the bottom of the spreadsheet. If all the worksheets are currently being used, press the new worksheet button, which is located directly to the right of the last worksheet.3. Select cell 'B1' on the new worksheet. Press 'Ctrl' 'V' to paste the information...
How to Create Checkbook Registers in Excel
1. Launch Excel 2007. Select the Insert tab.2. On the Insert tab, click 'Table.' In the create table dialog box enter =$A$1:$G101 in the 'Where is your data?' field. Check the 'My table has headers' box then click 'OK.' Excel will create a table with seven columns and 101 rows.3. Create labels for your checkbook register on row 1. Beginning with cell A1, replace each generic label with the following labels: Check No., Date, Transaction Description, Statement, Payments, Deposits, Balance. The balance label should be in cell G1.4. Format the check register rows by first turning off the filters,...
How to Make an Amortization Table in Excel
1. Click on the 'Office' button in Excel 2007 and select 'New.'NOTE: If you do not have Excel 2007, click the 'Excel Amortization Schedule Template' link in the 'Resources' section below to download and save the amortization template. Continue to Step 4.2. Scroll and select 'Schedules' on the left side of the template window.3. Double-click to select and open the Amortization Schedule template that you want to use.4. Input the 'Loan amount,' 'Annual interest rate,' 'Loan period in years,' 'Number of payments per year,' and 'Start date of loan.'5. Press the 'Enter' key to complete each...
Friday, March 16, 2012
How to Delete Duplicate Rows in Excel
1. Select the entire spreadsheet by dragging an outline around it with the mouse. Click the 'Data' tab, then click the 'Advanced' button in the 'Sort Filter' box.If you are using Excel 2002 or 2003, click 'Data,' 'Filter,' 'Advanced Filter.'2. Click the radio button labeled 'Filter the List, in Place' and check the box labeled 'Unique Records Only.' Click 'OK.' All of the duplicate rows will be hidden.3. Select the entire spreadsheet, and then press the 'Ctrl' and 'C' keys on the keyboard to copy the selection.4. Press the 'Ctrl' and 'N' keys on the keyboard simultaneously to create a...
How to Create a Simple Report in Excel
1. Launch Excel with a new, blank workbook.2. Click the 'Insert' tab and choose the 'Header Footer' icon in the Text group. Type a name for your report in the text field at the top of the report that appears.3. Click 'Go To Footer' in the Navigation group on the Design tab. Type a report footer or click one of the choices in the 'Header Footer Elements' group such as 'Page Number' or 'Current Date' to insert that information in the footer.4. Click any of the data cells where it says 'Click to Add Data' and then click 'Normal' in the Workbook Views group on the View tab.5. Type some...
How to Print an Excel Spreadsheet With Background
1. Open Microsoft Excel and open the spreadsheet you want to add the background to.2. Select the 'Insert' tab in Excel's main window and select the 'Header Footer' option.3. In the new view, position you mouse under the word 'Header' and click within that box.4. Click the 'Picture' button located on the now showing 'Design' toolbar. Navigate to and select the picture you want to use as a background.5. You will see text similar to '[Picture]' in the header box. This is the place holder for your image.6. Select the 'Format Picture' button on the Design toolbar. In the window that appears,...
How to Calculate Percents in Excel 2003
1. Determine the part and whole of the percentage you want to calculate. For example, assume a student receives 37 out of 75 points on a test.2. Type the smaller number in cell A1. In the example above, you would type '37' in A1.3. Type the larger number in cell A2. In the example above, you would type '75' in A2.4. Type '=A1/A2' in cell A3. This is your percentage in decimal form. In this example, you will see 0.4933. Keep your cursor on this cell.5. Press the '%' on the top of the window to convert the decimal to percentage. In the example, '0.4933' will change to '49.33...
How to Use Sumif MS Excel Together
1. Decide which cells you would like to sum. For example, you might want to sum cells in the range A1:A10.2. Choose a criteria to sum by. For example, you might want to sum numbers that are less than 100. Other relevant criteria might be more than or equal to.3. Place the SumIf statement in an empty cell:=SUMIF(A1:A1...
Thursday, March 15, 2012
How to Apply Formatting Across Multiple Cells in Excel
1. Open the Excel 2010 file that contains the cells where you want to change the formatting. Right-click one of the cells you want to change and choose 'Format cells' from the pop-up menu.2. Select the tab at the top of the 'Format Cells' window that corresponds to the type of formatting you want to do. You can choose from 'Number,' 'Alignment,' 'Font,' 'Border,' 'Fill' and 'Protection.' Once you select a tab, change the options in the window to get the cell formatted as you like it. Once all of your formatting changes are done, click 'OK.' You will see the cell change according to your formatting.3....
How to Write a Macro in Word That Reads Data From Excel
1. Open Excel, then type any value in the first cell of the first spreadsheet. Click the Office button's 'Save' command, then type 'c:\ReadFromExcel.xlsx' for the filename. Click 'Save' to save the workbook, then close Excel.2. Click the Office button in Word, then click the 'Options' button. Click the 'Developer' checkbox to display the 'Developer' tab, which holds Word's macro commands.3. Click the 'Developer' tab's 'Visual Basic' button to enter the Visual Basic development environment.4. Click the 'Tools' menu, then click the 'References' command. Click the 'Microsoft Excel objects'...
Wednesday, March 14, 2012
How to Create a Basic Inventory Spreadsheet With Excel
1. Launch Microsoft Excel and click on 'Create Header' to create a header or title for your inventory spreadsheet.2. Click inside cell 'A1.' Type the word 'Items.' You have now created a column for your items to be listed. If you want the column to stand out, you can also highlight the text, make it boldface or underline it.3. Create columns that show the date of inventory and the quantity of items on that specific date. Click inside cell 'B1.' Type the text 'Date.' Click inside cell 'C1' and type the text 'Quantity.' Move to cell 'D1' and type the text 'Date.' Continue this pattern as...
How to Create a Break Even Chart in Excel
Label Data1. Type 'Fixed Costs' into cell A1.2. Type 'Unit Expense' into cell A2.3. Type 'Unit Revenue' into cell A3.4. Type 'Units' into cell C1.5. Type 'Revenue' into cell D1.6. Type 'Expense' into cell E1.Input Data7. Input your fixed costs of a production into cell B1. For example, if rent for the factory is $25 per month, then type '25.'8. Input the cost of each unit you produce into cell B2. For example, if each widget costs you $5 to manufacture, type '5.'9. Input the revenue you make for selling each unit into cell B3. For example, if each widget sells for $10, type '10.'10....
How to Extract Data Onto a Table Using Excel 2007
Create a Table1. Open the spreadsheet and highlight the data you want to put into a table.2. On the 'Insert' tab, click 'Table.' If you want to include the headers in your data, choose the box next to 'My Table Has Headers.' Click 'OK.'3. Your data will appear as a table. If you've excluded any data, it will still appear. If this isn't what you had in mind, try creating the table again, this time including all your data. Then, when your table is created, choose in the next step to exclude and hide any additional data.4. Format the table by using the drop-down boxes that appear next to...
How to Change the Color of a Cursor in Excel
1. Click the 'Start' button in Microsoft Windows.2. Click 'Control Panel.'3. Click the 'Ease of Access' category.4. Click 'Ease of Access Center.'5. Click the 'Make the Mouse Easier to Use' link under 'Explore All Settings.'6. Select the color scheme you want to use for the mouse cursor. Choose from a selection of white, black or inverting, in different sizes.7. Click 'Apply.' Click 'OK.'8. Open Microsoft Exc...
Tuesday, March 13, 2012
How to Make Excel Calculate Division With Remainders
1. Launch Microsoft Excel and open a new, blank worksheet.2. Enter the top number of a fraction or the number to the left of the division sign (the dividend) into cell A1. For the division problem '9/4,' type '9' (no quotes) and press 'Enter.'3. Enter the bottom number of a fraction or the number to the right of the division sign (the divisor) into cell B1. If you are solving '9/4,' type '4' (no quotes) and press 'Enter.'4. Use the Quotient function to find the integer portion of the result. Type the following into cell C1:=QUOTIENT(A1,B1)Press 'Enter.' For the problem '9/4,' you see a...
How to Compare Two Excel Spreadsheets
1. Open Excel and use 'File,' and then 'Open' to open the first file to use for comparison.2. While the first document is still open, use 'File,' and then 'Open' to open the second file.3. Select the 'View' tab, and then select 'New Window.'4. Select 'View Side by Sid...
How to Make a Timeline in Excel
1. Plan your timeline ahead of time. Sketch it out on a piece of paper so you can refer to it as you design the timeline in Excel.2. Open a new worksheet in Excel.3. Go to 'File' then 'Page Setup' and change the page orientation to landscape. This will turn your worksheet horizontal.4. Drag your cursor from the first cell to the length you expect your timeline to take up. Click 'Format,' 'Cells,' 'Alignment' and then check the box next to 'Merge Cells' and click 'OK.'5. Place your cursor in the merged cell you just created and type the title of your timeline. Click the center alignment...
Monday, March 12, 2012
How to Use a Subroutine in Excel Visual Basic
1. Open the Visual Basic Editor. In Excel 2003, from the menu, select 'View,' 'Toolbars,' 'Visual Basic.' From the new toolbar, click the Visual Basic Editor icon. In Excel 2007, on the Developer tab, in the Code group, click 'Visual Basic.'2. Insert a new module. From the menu, click 'Insert,' 'Module.' You will see the new module listed in the Project Explorer with the name 'Module1.'3. Insert a new procedure. From the menu, click 'Insert,' 'Procedure.'4. In the 'Add Procedure' pop-up box, type a name for your new subroutine. Use underscores, rather than spaces, to separate words.5....
How to Use the Excel Monthly Household Budget Template
1. Open Excel. If you are using Excel 2007, click the 'Office Button' and then click 'New.' In Excel 2003, click 'File' and then 'New.' Type 'Family Monthly Budget' in the 'Search Online For' box and click 'Go.'2. Click 'Family Monthly Budget Planner' to select that template. Click the 'Download' button. The template will open as a new Excel worksheet.3. Start in the 'Projected Cost' column. Type in what you expect the costs to be for each item listed that month. You can round off your costs to whole numbers, or put in exact numbers and the template will round them off for you. If you are...
How to Darken the Selection in Microsoft Excel
1. Select the column or columns of data with your mouse. If you want to select all of the columns, position your mouse on top of the first column letter (usually column A), click, and drag it across to the last column. This will select everything in your spreadsheet.2. Click the 'Home' tab and select the 'Cell Styles' option. You will see a listing of various cell themes.3. Choose one of the cell themes that are listed at 20 percent opacity from the list of cell style options. From now on, when you select the cells in your worksheet, the selection box will be darker and easier to distinguish....
How to Merge Excel Spreadsheets to Find Duplicates
Complete the Merge1. Identify the duplicate values you are searching for. Identify the data type and make sure it is the same in both spreadsheets. For example, if you would like to identify duplicate dates, make sure the dates are recorded in the same format (9/12/2034) across spreadsheets.2. Confirm the spreadsheets have the same number of columns. To merge spreadsheets, make sure they have the same number of columns. If not, make sure the column headers match until it no longer matters. For example, if spreadsheet A has four columns and spreadsheet B has six columns, make sure the first...
How to Open Excel 2007 Files With Office 2003
1. Visit Microsoft's online Download Center. Search for 'Microsoft Office Compatibility Pack for Word.'2. Click on the 'Microsoft Office Compatibility Pack for Word, Excel and PowerPoint File Formats' link.3. Click on the 'Download' link. Click on 'Save' to save the file to your computer.4. Click on the downloaded FileFormatConverters.exe file. It should be located in the downloads file on your computer.5. Click on 'Run' to start the installation process. Once the installation is complete, you will be able to open and edit files made in Excel 2007 in your Office 2003 suite of applic...
How to Enter a Series of Dates in an Excel 2007 Spreadsheet
1. Open the worksheet.2. Click on a cell that will contain the first date. Enter this date. For example, 5/12/11.3. Point on this selected cell's lower right corner. A black ' ' symbol will appear over the tiny square in this corner. This symbol is the fill handle.4. Drag the fill handle across the adjacent cells to fill them with the sequence of dat...
Sunday, March 11, 2012
How to Use Excel to Subtract From the Total
1. Open the document you would like to make the changes to in Microsoft Excel.2. Locate the cell that contains the total and make note of the cell number --- for example, 'E2' or 'F15.'3. Select the empty cell in which you would like to have the subtracted result shown.4. Type '=X - Y' where X is the number of the cell containing the total and Y is the number or cell you would to subtract from the total.5. Press 'Enter' and the cell will now show the subtracted tot...
How to Make the Cursor Stop Highlighting
1. Launch the Microsoft Excel project in which you wish to stop the cursor highlighting.2. Select a single cell with your cursor. The cursor highlights a range of cells only if extended-selection mode is turned on.3. Turn extended-selection mode off by pressing the 'F8' key.4. Select a single cell with your cursor. The cursor will highlight only the individual cell. Extended-selection mode has been turned off.5. Restart your system if the problem persis...
How to Combine Worksheets Inside an Excel Spreadsheet
1. In Excel 2007, click on the 'Data' tab and choose 'Consolidate' from the Data Tools group. For earlier versions of Excel (97-2003), click on 'Data->Consolidate.'2. Define the reference ranges using the Reference box. Specify the first range you want to consolidate.3. Click 'Add.' The reference you defined in Step 2 will appear in All References.4. Repeat Steps 2 and 3 until you have defined everything you want to consolidate. Then click 'O...
Subscribe to:
Posts (Atom)