July 2011 - Excel

Browse » Home » Archives for July 2011

Thursday, July 28, 2011

How to Publish Excel Web Pages

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

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 “Move” from the resulting menu and drag the mouse across the screen. Note that the Field List changes from a box as tall as the spreadsheet display to a shorter box when you drag it away from the right side of the window.
4. Moving the Field List or any task pane or toolbar to its original location with a set of toolbars or attached to one side of the screen is called “docking” it. Double click the PivotTable Field List title bar to return it to its previous docked position in the Excel window.
5. To dock the Field List on the left side of the window, click and drag the title bar across the screen until the box again becomes as tall as the spreadsheet display, and release the mouse button.

How Do I Link to Cells That Move in an Excel Spreadsheet?

Merge Cells in Excel 2003
1. 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 and Center” option. The cells will then be automatically linked together.
4. Change the text alignment in the merged cells by clicking on the “Align Left” or the “Alight Right” option from the “Formatting” tool bar menu.
5. Click on the merged cells and then click on the “Merge and Center” option at any time to split the merged cells. The data will still appear in the upper-left cell of the range.
Merge Cells in Excel 2007
6. Open the Excel 2007 that contains the cells that you want to link. Make sure that data you want to show is in the upper-left corner of the data range.
7. Click on the “Home” tab and then click on the “Merge and Center” option from the “Alignment Group.” The cells will then be automatically centered and linked.
8. Merge the cells without centering by clicking on the “Merge Across” or “Merge Cells” option from the “Alignment Group.” Click on the “Alignment” buttons in the “Alignment Group” section to change the alignment of the data in the cells.
9. Click on the “Merge and Center” button in the “Alignment Group” area at any time to split the linked cell back to the way it was before.

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

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 the Office button and select 'Excel Options.' Click 'Advanced' and scroll down to the 'Display options for this workbook' section.' Click the 'All' radio button under 'For objects, show' to get rid of the 'Cannot shift objects off sheet' error message when you try to insert or hide rows or columns. Click 'OK.'
3. Open Windows Explorer. Double-click the 'C:' drive to expand its directory. Double-click 'Program Files' or 'Program Files (x86).' Expand the 'Microsoft Office' directory. Expand either the 'Office,' 'Office10' or 'Office12' folders. Double-click the 'Startup' folder to open it. Right-click on 'pdfmaker.dot' and click 'Cut.' Navigate to your 'My Documents' folder. Right-click the folder and select 'Paste.'Repeat the process to remove the 'pdfmaker.xla' file from the 'Xlstart' folder---located in the 'Office' folder under the 'Microsoft Office' directory---and place it into the 'My Documents' folder. Moving these Adobe Acrobat files gets rid of the 'Compile error in hidden module: Autoexec' and 'DistMon' error messages when opening Excel 2007.
4. Open the Control Panel. Go to the 'Add or Remove Programs' section in Windows XP and the 'Uninstall a Program' section in Vista and Windows 7. Click Microsoft Excel or Microsoft Office from the list of programs. Click 'Change.' Select 'Repair' and click 'Continue' to run the 'Detect and Repair' tool to automatically have Microsoft find and repair problems with your Excel program.

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 easy to organize, filter and search through large amounts of data.
3. Plan and manage a project or event with Excel. Whether you are planning a large work project or a wedding or holiday party, use Excel to keep track of multiple tasks and deadlines, and the schedules of other participants or collaborators--and as a central database of all information and files you need to execute the project or event.
4. Create lists through Excel. Excel's convenient tabular layout makes it easy to format many different types of lists. Create checklists to make traveling and packing easier. Create an emergency phone list for your babysitter. Manage your book, wine or DVD collections via lists. Run your household better by using Excel lists to manage your groceries, chores and holiday gift-giving.
5. Create an address book to manage your mailing labels. Excel's extensive 'filter,' 'sort' and 'search' functions make it easy to create and manage a large contact database. You will be able to find and sort contacts by city, state, street, last name or birthday, or by whatever other criteria and detail you enter. When you need to create mailing labels to send greeting cards or announcements, use Excel's mail-merge feature to quickly format and print out the names and addresses of your contacts.

How to Lock Cells in Microsoft Excel

Microsoft Excel 2007
1. 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 Sheet” option from the “Changes” group.
6. Enter a password for your worksheet in the “Password to unprotect sheet” field. Click on the “OK” button, and your cells will be locked.
Microsoft Excel 2003
7. Open the Microsoft Excel 2003 application on your computer. Make sure you have the worksheet with the cells you want to lock open.
8. Use your mouse to select all of the cells in the worksheet that you want locked. Make sure the cells are highlighted.
9. Click on the “Format” option from the top toolbar menu and then click on the “Cells” option.
10. 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.
11. Click on the “Tools” option from the top toolbar menu and scroll over the “Protection” option. Click on the “Protect Sheet” option and then click on the “OK” button.

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 text you want to combine and type ' ' -- be sure to include a space between the two quotation marks if you want the text to be separated by a space.
6. Click the next cell that contains the wrapped text you want to combine with the first cell contents and type ')' to complete the merge formula.
7. Press 'Enter' to view the merged contents in the cell. Make any cell width adjustments desired. Note that the wrapped text positioning will adjust the cell height automatically as you make any column width adjustments.

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 and drag the margins on the page to change the page layout. Click 'Print.'

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

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 total will show in the specified cell.

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

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 returning anything, it's a subroutine.
4. Type an apostrophe on a new line, followed by a description of what the program is for. For example: ''this macro italicizes a highlighted cell.'
5. Type the body of your code starting on a new line. For example, to italicize a cell, type:'Selection.Font.Italic = True.'
6. Type 'End Sub' or 'End Function' on a new line, depending on if you defined a Sub or Function in Step 3.
7. Run the code by pressing the 'F5' key.

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

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' field, depending on the orientation of your table. Click 'OK.'

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 from the 'Styles' group.
6. Select the 'Normal' option below the 'Good, Bad, and Neutral' field and all of the links you selected will be removed.

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

How to Remove Grid Lines

For Excel 2003
1. 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 grid lines are now gone.
For Excel 2007
10. Open your spreadsheet.
11. Click 'View' on the taskbar.
12. Look for the 'Show/Hide' group.
13. Clear the checkbox near 'Gridlines' to uncheck the box. The gridlines disappear.
For Excel 2010
14. Select your spreadsheet.
15. Click 'File' in the taskbar. Click 'Options.' A dialog box will appear.
16. Click 'Advanced' in the right pane. Scroll to find 'Display Options for this Worksheet,' and click on it.
17. Disable 'Show Gridlines' by clicking on the check box to remove the check. The gridlines disappear.

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 = GetAttr(PathName)'Check if error exists and set response appropriatelySelect Case Err.NumberCase Is = 0FileOrDirExists = TrueCase ElseFileOrDirExists = FalseEnd Select'Resume error checkingOn Error Goto 0End FunctionSub TestItWithWindows()'Macro Purpose: To test the FileOrDirExists function with Windows'Only included to demonstrate the function. NOT required for normal use!Dim sPath As String'Change your directory heresPath = 'C:\Test.xls''Test if directory or file existsIf FileOrDirExists(sPath) ThenMsgBox sPath ' exists!'ElseMsgBox sPath ' does not exist.'End IfEnd SubSub TestItWithMacintosh()'Macro Purpose: To test the FileOrDirExists function with a Macintosh'Only included to demonstrate the function. NOT required for normal use!Dim sPath As String'Change your directory heresPath = 'HardDriveName:Documents:Test.doc''Test if directory or file existsIf FileOrDirExists(sPath) ThenMsgBox sPath ' exists!'ElseMsgBox sPath ' does not exist.'End IfEnd Sub
2. Open Excel and press 'Alt-F11' to enter the Visual Basic Editor.
3. Click 'Insert' and then click 'Module.'
4. Paste the code into the right-hand pane by pressing 'Ctrl-'V.'
5. Change 'text.xls' to the file name you are searching for.
6. Press 'F5' to run the procedure. The procedure will return a pop-up window telling you whether the file exists.

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 security settings.
3. Distribute the Excel workbook master file to those co-workers with whom you want to share the macro. When a user bases a workbook on this master file, the macro remain embedded.

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

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 'Consolidate' dialog box, select your entire table and click 'OK'. This produces a new row with summarized totals for each unique customer in Column A. Delete the 'Date' field in the summary results.
3. Create a Pivot Table. Select a blank cell to the right of your data. Click 'Data' and 'Pivot Table and PivotChart Report.' Select your entire data table and click 'Finish.' Choose to add 'Customer' and 'Total' to the new pivot table. Excel sorts the summarized data by unique customer.

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 chart.
8. Right-click inside a blank area of the chart and select 'Chart Type.'
9. Click 'Pie' from the 'Chart Type' and click 'OK.'

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:Price
2.00
3.00
4.00
3. 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 alphanumeric numbers in the parenthesis are the range of fields. The 'a' represents the column and the number represents the row.
4. Use the CountBlank function to count the number of blank cells. This tells you how many cells contain no data in your list of fields. To use the CountBlank function in conjunction with the test data, type the formula below into a cell at the bottom of the spreadsheet:=countblank(a1:a5)
5. Use the CountIF function to display a count only if a particular cell contains the specified data. For instance, suppose you want to count how many cells contain '2.' Use the CountIF function to determine the output. The code is below:=countif(a1:a4, 2.00)The first parameters are the range of cells to check. The second parameter (2.00) is the value to count.
6. View the results. The Count function returns the value of '3.' This is because only three out of the four cells populated have numerical values. The CountA function returns '4.' CountA displays a count of all cells regardless of the data type. For the CountBlank function, the range has only one blank cell, so the return value is '1.' Finally, for the CountIF function, there is one cell with the matching value, so the return value is '1.'

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

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. Use the 'sum' icon on the command bar to easily implement the formula. The sum icon is located on the top of the command bar in the middle and looks like an 'M' lying on its side. A drop-down menu will open when you click on the sum icon. Select “sum” to create the formula.
4. Save changes to your spreadsheet. You will want to make sure that you save the changes you just made by clicking on the “file” tab and selecting “save.”

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

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

How to Change the Vertical Axis Numbers on an Excel Bar Chart

Using Chart Tools
1. 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 Change
5. Click on the number you want to change in the Excel worksheet (not on the graph). For example, if you want to change a vertical axis number from 2,000 to 2,001 on the chart, click on '2,000' cell on the worksheet.
6. Enter the number into the cell.
7. Press 'Enter.' Excel automatically updates the chart for you.

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 tag width. Type the width of your tag in inches, centimeters or millimeters by entering the appropriate units after the value. For example, to make a tag that is two inches wide, input '2in' without the quotation marks.
4. Right-click the row header to the left of the screen and select 'Row Height' to input the height of your tag. Type the height of your tag followed by the appropriate units. Create duplicate cell parameters in the adjacent cells so that when you print, you can fold your tag in half or cut and reverse it to create a double-sided tag.
5. Highlight your cells and click the 'Home' tab of the Ribbon. Click the 'Borders' button from the Font area and select 'All Borders.' Borders will show you the alignment of each tag after you print your document.

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. Edit pictures by selecting 'Color,' 'Crop,' 'Resize,' 'Compress' or 'Auto Correct.'

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. For example, you might append the file name using the employee in charge of the new workbook.
3. When you are ready to merge the two workbooks, open the original workbook (saved to your desktop or some other location). Under 'Tools' select 'Merge Workbooks.' In the pop up window, select the workbook to be merged. At this point, the new workbook should be accessible (either uploaded to your desktop from an email or located in a shared drive folder). Save when prompted.
4. Repeat Step 3 for each additional file you wish to merge, saving when prompted.

Thursday, July 21, 2011

How to Update Links in Excel

How to Update Links in Excel
1. 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 Values'.

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 wish to merge into the original, and click 'OK.' You can merge multiple copies of the workbook by pressing 'Shift' while you are selecting the various workbooks.

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 cells allows you to perform actions on all the cells at once. For instance, you can select a group of cells and then press 'Delete' to delete the data they contain.
6. Select a range of cells containing data, click 'Insert' and then 'Chart' to insert a graph. A chart creation wizard will appear. Select the chart type you want from the list, click 'Next' several times and then 'Finish.'
7. Select a cell by pressing the '=' button, type in a formula and then press 'Enter' to have the cell display the result of the formula. You can reference the values in other cells while entering a formula by clicking on the desired cell while entering the formula.
8. Hold down the 'Control' key and then press 'S' to save the current workbook. You will have to enter a name for the workbook and then click the 'Save' button when saving the project for the fist time.

Tuesday, July 19, 2011

How to Send a Mass Email From an Excel Spreadsheet

Send a Mass Email from an Excel 2003 Spreadsheet
1. 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 Document Type,' click 'Email Messages.' Click 'Next.'
4. Select 'Use Current Document' and click 'Next.' Click 'Browse,' select the Excel spreadsheet you created, click 'Open' and click 'OK.' Sort the recipients list if desired and click 'OK.' Click 'Next.'
5. Click 'More Items' to enter the fields from your Excel spreadsheet. Insert the fields in the appropriate places. The email address field should go at the top of the document and the 'First Name' or similar field should go into the salutation.
6. Click 'Next' to preview your email message. Click 'Next' again to complete the merge. Click 'Electronic Mail' under merge. Enter a subject for your mass email in the 'Subject Line' field and click 'OK.'
Send a Mass Email from an Excel 2007 Spreadsheet
7. 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.
8. Open Outlook and minimize it. Open Word and type your email as desired.
9. Go to the 'Mailings' tab of the ribbon and click the 'Start Mail Merge' button. Select 'Email Messages' in the drop-down menu.
10. Click on 'Select Recipients' in the 'Start Mail Merge' group. Find the Excel spreadsheet you created, click 'Open' and click 'OK.' Select fields from the 'Write Insert Fields' group on the 'Mailings' tab of the ribbon. Click 'Greeting Line' to enter a salutation. Click the 'Match Fields' button to match a heading from the Excel spreadsheet to each field you insert.
11. Go back to the 'Mailings' tab and click the 'Finish Merge' button in the 'Finish' group. Click 'Send Email Messages' to send your mass email.

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 A2 for the entry date, 'office supplies' in B2 for the account name, 'D' in C2 because the transaction is a debit and '$50' in D2 for the amount. Continue entering all your transactions in chronological order.
4. Click the 'Insert' tab, click 'Pivot Table' and then select 'Pivot Table' again if you're using Excel 2010. If you're using an older version of Excel, you'll need to click 'Data,' then 'Pivot Table Pivot Chart Report' and click 'Next.'
5. Left-click on B1 and continue holding down the button to highlight all cells through to the end of the list. For example, if you had 100 transactions added to the general journal, you'd highlight B1 through D101. Click 'Next.'
6. Click 'New Worksheet' and then click 'Finish.'
7. Left-click the 'Name of Account' field and drag and drop it into the 'Row' field area.
8. Left-click the 'Debit/Credit' field and drag and drop it into the 'Column' field area.
9. Left-click the 'Amount' field and drag and drop it into the 'Data' field area.
10. Click the 'Pivot Table' button located in the toolbar and select 'Table Options.'
11. Remove the check mark from the 'Grand totals for rows' check box. Click OK.

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 side of the VBA editor. A blinking cursor appears. Enter the following code into the editor:Sub Name()Change 'Name' to whatever you want to name your subroutine. Press Enter to complete the line and the VBA editor will automatically place the 'End Sub' command on a line beneath the cursor. This is how you will start all macros that you create in VBA.
5. Create your program's variables on the next few lines. Start each line with the word 'Dim,' which signifies that you are creating a variable. Type the name of the variable followed by the word 'as,' then the type of variable. For example, both 'Dim x as Integer' and 'Dim y as Variant' are acceptable. If you have multiple variables of the same type, place them on the same line separated by a comma, such as 'Dim x, y as Integer.'
6. Enter the actual code for your program beneath the variables. Enter your code in lowercase, as VBA will automatically capitalize commands that it recognizes, which can help you write your code. VBA will help you complete certain commands. For example, when you enter 'Range(' to begin a line of code that will select a range of cells on the worksheet, VBA will display a small box next to your cursor telling the correct format to complete the argument. When you get to a point in your code where it is obvious that you will need to call certain functions, VBA will automatically display a list of all the functions, letting you select from the list instead of typing it in.

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

Monday, July 18, 2011

How to Add Cells in Excel

Add Blank Cells to an Excel Spreadsheet
1. 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 cells above.
Create Totals on a Worksheet
5. Enter data in your worksheet. Type in the figures you want to calculate in a column or a row.
6. Click on the cell where you want the sum total to appear. This is usually the blank cell beneath, or on the side of, the last cell containing data.
7. Click the 'Autosum' button on the toolbar to add the numbers. This will display a formula for the calculation that corresponds to the cells in the range. You can manually adjust the formula if you want to remove some of the cells or include others.
8. Press the 'Enter' key to accept the formula and proceed with the calculation. The sum of the figures will appear in the cell.
9. Find more help on how to add cells on Excel at Microsoft.com. The 'Help and How-to' home page lists the different versions of Microsoft Office products (see Resources below). You can search for information that is specific to your version of Excel.

How to Draw Separating Lines in Excel

Draw a Border Line
1. 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 Border
6. Open the Excel worksheet.
7. Click and drag on the range of worksheet cells that will contain the new border or line.
8. Click the “Home” tab on the command ribbon.
9. Click the arrow next to the “Border” button in the “Font” group to display a list of border styles.
10. Click the preferred pre-designed border style, such as “Left Border,” “Thick Box Border” or “Top and Double Bottom Border.” The selected cells display the lines.

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 on the Quick Access Toolbar.

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

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 border and click 'Format Cells...'
4. Go to the 'Border' tab and under 'Style' choose the border you'd like to use. Choose the color under the 'Color' drop down menu.
5. Click the small boxes around the big box under 'Border.' These small boxes have darkened lines that emphasize the part of the border to be created like the left and right outer borders, upper and lower borders, and the inner borders.
6. Click 'OK' to finish creating the border.

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 the person's age as of today. If you want to calculate age based on a specific date, enter that date in another cell and use that cell number in the formula instead of 'TODAY().' For example, if you want to know a person's age on July 1, 2010, you would enter 7/1/2010 in cell K2 for example, and enter =DATEDIF(C2,$K$2,'Y') in your age column cells. The '$' sign fixes the reference to a single cell so that when you copy the formula, the cell reference will not change.
5. Copy this cell down the column to calculate age for all your cases.

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 show up on the right.
6. Enter the following text, exactly as shown here, in the pane on the right, without the quotes. The line breaks are important -- this should show up on three lines in the window.'Private Sub Workbook_Open()Range('a1').Value = Range('a1').Value 1End Sub'
7. Save and re-open the file. Every time the Invoice.xls file is opened, the number in cell A1 will have 1 added to it.

How to Make Frequency Tables

Make Frequency Data Ranges
1. 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. Look over your spreadsheet after all of the data have been entered, then determine the range of the data. If you have 100 respondents in your data set, you will have 100 rows of data, ending on row 101. (Remember, the first row is the question number.) So your first column's data range will be A2:A101. Your second question will be data range will be B2:B101.
3. Use the simple formula for counting. Say you have six possible responses to your first question. The formula would read as follows:=countif(a$2:a$101,1)This formula tells Excel to count the times that the number 1 occurs in the data range found in column A from row 2 to row 101.The formula to count all the 2's in column A would read as follows:=countif(a$2:a$101,2)The formula for the 3's would be countif(b$2:b$101,3), and so on through all your possible responses to the question.
4. Simplify the process by pasting the first counting formula--countif(a:$2:a$101,1)--into the cells for the number of possible responses you have. For example, if you have six possible responses, copy that formula into the first six cells in the area of your spreadsheet where you are doing your counting. Change the criteria manually from 1 in the second cell to 2, and the third to 3, and so on. Once you have made all the changes to 1 through 6, put in the formula for calculating the percent distributions.
5. Total the count of column results in the first cell below your count. For example, if you are using A105 through A110 to do your counting, you would either use the sum button on the formula toolbar in Excel to sum the column, or this formula: =sum(a105:a110). You would use cell A111 to put in the formula.
6. Use the following formula to calculate the frequency distributions of the results in A105 through A110, starting it in cell A112: =a105/a$111). This will give you a decimal response, which you can reformat into a percentage for easier viewing. Simply copy the formula in A112 and apply it to the five cells that fall below A112 to get the percentage distribution of all the responses.
Make a Frequency Table Using Data Ranges
7. Create or find the data that you want to summarize.
8. Determine the ranges you want. For example, if your data set goes from 1 to 100, you would probably want to break it into 10 segments, 1 to 10, 11 to 20, 21 to 30, and so on. Let's assume your data are in column A, and rows 1 to 100.
9. Type in the following numbers in B1 through B10, in a column next to the data series: 10, 20, 30, 40, 50, 60, and so on, with each number in a separate cell.
10. Select 10 cells with the mouse in the column C next to the data range (column B).
11. Position the mouse in the function bar above the spread sheet (where it says 'fx'), then type in your formula. The formula for counting frequencies is pretty easy: =frequency(b1:b100b1:b10). Since this is an array function, you have to hold down control shift while you hit enter. Otherwise you will get an error like '=NAME?' or something like that. If you have entered your formula correctly, the results will be shown in Column C1 through C10.
12. Total the results of C1through C10 as discussed in Section 1, Step 5, but using the cells in C1 through C10.
13. Use the following formula to calculate the frequency distributions of the results in C1 through C10, starting it in cell C11: =c1/b$b11). This will give you a decimal response, which you can reformat into a percentage for easier viewing. Simply copy the formula in C1 and apply it to the nine cells that fall below C1 to get the percentage distribution of all the ranges.

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 2010
3. 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 = (7,900/5000)^(1/3)-1CAGR = .164 = 16.4 percent annually

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 automatically be entered into the field.
4. Implement the advanced filter. Check the box labeled “Unique Records Only” if you do not wish to see duplicate records. Click on the “OK” button to implement the advanced filter.

Saturday, July 16, 2011

How to Use Subtotals and Totals in an Excel Spreadsheet

Creating Labels for Your Spreadsheet
1. 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 Totals
7. 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 the 'Add subtotal to' option.
10. Select OK.

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 drag the picture to resize it as desired.
4. Resize the picture using the “Format Picture.” To access this toolbar menu, right-click on the picture and select “Format Picture.”
5. Use the format picture menu to resize. Once in the format picture menu, left-click on the “Size” tab. Under “Size and Rotate” you can specify the height and width in inches by typing them into the corresponding boxes. Under the “Scale” section, you can specify the height and width by percentages by typing them into the corresponding boxes.
6. Implement size changes. To implement the size changes that you just made, click on the “OK” button.

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 order by changing the 'Order' setting to 'Z to A.'

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

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 styles in the menu. A chart will appear in Excel.
6. Click on the row of numbers running down the left-hand side of the chart. Right-click and select 'Format Axis.' The Format Axis menu will appear.
7. Click on the 'Axis Options' tab of the Format Axis menu. Click on the tick box next to 'Logarithmic scale'.

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 Excel 'File' tab and the 'Options' link. Click the 'Customize Ribbon' button. Click the 'Main Tabs' option in the 'Customize the Ribbon' text box on the right.
8. Click the 'Cells' group under the 'Home' tab section. Click the 'Remove' button between the left and right panes. Click 'OK.' The 'Cells' group and the 'Format' button that contains the 'Unhide Sheet' option disappears from the command ribbon.

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 the same section each time in order for the macro to work. That is, the best way to run a graphing macro is to use it on a report where the formatting stays the same, but the numbers change. This means the numbers which are being graphed are always in the same place.
3. Go to the Tools menu. Click on 'Macros' and 'Record New Macro'.
4. Create the macro name and keyboard short-cut. You can choose anything you want, but let's go with 'Graph' and 'ctrl g' for this example.
5. Begin creating the graph. Once you create the name the macro will begin to record your commands. There are many ways to create a graph, but the easiest is to click on the graph icon in the standard toolbar. This icon looks like a chart. Walk through the wizard, step by step. Each choice will also be chosen in your macro command. When finished click 'OK' and 'Stop Recording'.
6. Run your macro. You can access your graphing calculator in two ways: 1) go to the Tools menu and then click on 'Macros' to see a list of the macros created by name, choose 'Graph' and the select 'Run'; or, 2) hold down the 'ctrl' key and press 'G'.

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

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 your mouse to the 'Orientation' section of the dialog box. Click the red diamond to the right of the word 'Text' and drag it to rotate the text to the angle you desire. Click 'OK.'

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. Name the series to display a description on the legend or select the cell that contains the series title to automatically update the chart when you update the cell. Add all of your series and click 'OK' on each screen.
4. Select the 'Layout' tab of the ribbon and click the 'Chart Title' option box. Choose whether you want to display the main title above or on the first lines of the chart. Type in the chart title in the text box.
5. Click the 'Axis Title' drop-down box and select both a horizontal axis label and vertical axis label. Type the descriptions of your data into the new axis text boxes. Save your Excel spreadsheet by pressing 'Ctrl-S' or the save 'Save' icon at the top of the screen.

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 to the right of the 'Quarter' cell. Type in your data's header here. If you only have one set of sales numbers for each quarter, you can simply type 'Sales' into this cell. If your data is broken down by different regions, stores, channels, or in any other way, enter in a header for the first data series into this cell, then enter the next header into cell 'C1,' and continue until each series of data has a header.
5. Enter your numerical sales data into the cells where the rows representing the quarter intersect the columns representing the data fields.
6. Click cell 'A1.' Press 'Ctrl' and 'A' to select the entire table. Click the 'Insert' tab at the top of the screen. Select the 'Column' button, and choose one of the 'Clustered' column chart options. Excel will create a chart based on your table data. The column headers will automatically be listed to the right of the chart, and the quarters will be listed beneath it.
7. Select the first empty cell to the right of your column headers. Type 'Total' into this cell. Select the cell just beneath this one and enter this formula: '=sum(B2:XY)' where 'XY' is the column letter and row number of the cell just to the left of the current one. Press 'Enter' to complete the formula. Select the cell and move your mouse over the bottom-right corner. Click and hold the mouse button while you drag the mouse down to the last row in the data field. Release the button to copy the formula all the way down the table, making it easy to read the total sales for each quarter.

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

How to Restore Microsoft Excel to Its Defaults

Option One
1. 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 Two
4. 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 it appeared when you first installed it).

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. Select 'Repair.' If you previously attempted this method and the process failed, try to extract only the values and formulas by selecting 'Extract Data' instead. Regardless of the method you choose, your data will appear on the screen within a few seconds if the process completes successfully.

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 will enter your financial information in cells B1 through B4.
4. Key in the following formula in the cell under your financial data to determine the interest rate of the investment or loan [=Rate (B4,B3,B1)]. Then click the Enter button on the formula bar.
5. Make your decision. When complete, the formula will tell you the interest rate you will be paying on your loan or earning on your investment. If the terms of the investment change during your negotiations, simply re-enter the new information to see if the interest rate is good for 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 do this, click on one cell, hold the mouse button down, and drag the cursor across the rest of the data until it's highlighted.
4. Click 'Charts,' which can be found under the 'Insert' tab near the top of Excel 2007. Click 'Bar Chart.' A bar chart will be embedded on your Excel worksheet. To move the chart to a different location, click on it, then click the 'Design' tab, then 'Move Chart.' You can also change the name of your chart by clicking the 'Properties' tab.

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. For instance, click the 'Scatter with Only Markers' icon to create a scatter plot without lines. The chart will appear in your workbook.
5. Click in the chart area to display the 'Chart Tools' tabs.
6. Click the 'Design' tab to select a chart style.
7. Select the chart title and type a new name for the scatter plot. Right-click the title. Use the context menu to change the font and font size of the title.
8. Click the chart area. Click the 'Layout' tab. Click 'Axis Titles' in the 'Labels' group to add both horizontal axis and vertical axis titles, and choose the type you want. Select each title, type the text and press 'Enter.'
9. Select the plot area, and use the 'Format' tab to select the shape style you want to use for the plot.
10. Click the 'Microsoft Office' button, and click 'Save As' to save the scatter plot in your workbook.

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

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

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) > 1
2. 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.' Press and hold 'Ctrl' and then press the spacebar at the same time to select the entire column. Click the 'Edit' menu again and select 'Paste Special.' Choose the 'Formats' option in the Paste Special dialog and click 'OK.' Any duplicates found in the column will be highlighted with the selected color.

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 tabs. Press enter or click 'OK' after you make your choices. You can also change the look of a table by turning elements on and off from the Table Tool tab. This allows you to include or omit headers, rows, row banding, column banding, and emphasis on first or last columns.
6. Vary the colors of the table. Look in the Table Styles gallery to choose among sixty different color schemes.
7. Check the formatting changes you made by Previewing. Adjust your changes as needed, and then save.

Subscribe to: Posts (Atom)