How to Create a Histogram in Microsoft Word

1.
Open a blank Word document, or open the document in which you want to create a histogram. Insert a table onto the document. In Word 2007, go to the 'Insert' tab, click the 'Table' dropdown and select 'Insert Table.' In earlier versions of Word, go to the 'Table' menu, point to 'Insert' and click on 'Table.' Choose the number of columns you need in your chart, including a column to label the rows, and enough rows to present your data.
2.
Enter the y-axis data for the histogram into the first column of the table. For example, if you are making a histogram showing how much money employees earned annually, you would put a number range for employees in each cell of the first column. Enter the x-axis data in the bottom row.
3.
Highlight the cells in the first column to show the number of employees who earn that salary (as in the example). Right click these cells and select 'Borders and Shading.' Go to the 'Shading' tab.
4.
Select a fill color for the first column. Select the correct number of cells for the second column, right click it, select 'Borders and Shading,' and choose a different fill color for that column. Repeat with the remaining columns, filling each one with a different fill color.
5.
Insert a text box to the left of the table. The text box should be the same height as the column, but very narrow. To insert a text box in Word 2007, go to the 'Insert' menu and click 'Text Box.' In previous versions, select 'Text Box' from the 'Insert' menu. Type the label for the y-axis column into the text box. Type the label for the x-axis data under the table and the histogram is complete.

Read more ►

How to Create an Amortization Table in Excel

1. Use the loan APR, amount and term in Excel's 'PMT' function to calculate the monthly payment. If you have a 30-year mortgage for $200,000 at a 6 percent APR, type the following in cell A1.
=-PMT(0.06/12,30*12,200000)
2. Type in column headings for the amortization table. In cell A3, type 'Unpaid Balance.' In cell B3, type 'Principal.' In cell C3, type 'Interest.' Adjust the column widths as necessary.
3. In cell A4, type the initial loan value. For the previous example, you would type '200000.'
4. Calculate the interest on the first month's payment. In cell C4, based on the previous example, type the formula:
=A4*(.06/12)
This multiplies the unpaid balance by the monthly portion of the interest rate.
5. Calculate the principal on the first payment. In cell B4, type the formula:
=$A$1-C4
This subtracts the calculated interest from the calculated monthly payment.
6. Calculate the second month's unpaid balance. In cell A5, type the formula:
=A5-B4
This subtracts the principal from the previous month's payment from the loan balance.
7. Copy the formulas down each column until the table is complete. A 30-year mortgage, for example, will have 360 rows.

Read more ►

How to Do Multicolor Column Charts in Excel 2010

1. Open the Excel worksheet that contains the data for the column chart.
2. Click and drag to select the categories and data values for the column chart.
3. Click the “Insert” tab on the command ribbon.
4. Click the arrow for the “Column” chart in the “Charts” group.
5. Click the preferred column chart to add a chart representing the selected worksheet data.
6. Click in the chart area to display the Chart Tools ribbon.
7. Click the “Format” tab in the Chart Tools ribbon.
8. Click one column in the plot area. All the columns will appear highlighted.
9. Click the same column once again. This selected column appears outlined.
10. Click the “More” arrow in the “Shape Styles” gallery. A larger gallery of visual styles appears.
11. Mouse over the color samples to preview the effect on the selected column. For example, “Moderate Effect -- Purple, Accent 4.”
12. Click the preferred color sample. The column converts to the selected color. The legend also displays the updated color.

Read more ►

How to Compare Columns With VBA

1. Click the 'Developer' tab in Excel, and then click 'Visual Basic.'
2. Click 'Insert.' Click 'Module' to open a new module window.
3. Cut and paste the following code into the window:Sub CompareColumns()Dim Column1 As RangeDim Column2 As Range'Prompt user for the first column range to compare...'----------------------------------------------------Set Column1 = Application.InputBox('Select First Column to Compare', Type:=8)'Check that the range they have provided consists of only 1 column...If Column1.Columns.Count > 1 ThenDo Until Column1.Columns.Count = 1MsgBox 'You can only select 1 column'Set Column1 = Application.InputBox('Select First Column to Compare', Type:=8)LoopEnd If'Prompt user for the second column range to compare...'----------------------------------------------------Set Column2 = Application.InputBox('Select Second Column to Compare', Type:=8)'Check that the range they have provided consists of only 1 column...If Column2.Columns.Count > 1 ThenDo Until Column2.Columns.Count = 1MsgBox 'You can only select 1 column'Set Column2 = Application.InputBox('Select Second Column to Compare', Type:=8)LoopEnd If'Check both column ranges are the same size...'---------------------------------------------If Column2.Rows.Count
Column1.Rows.Count ThenDo Until Column2.Rows.Count = Column1.Rows.CountMsgBox 'The second column must be the same size as the first'Set Column2 = Application.InputBox('Select Second Column to Compare', Type:=8)LoopEnd If'If entire columns have been selected (e.g. $AA), limit the range sizes to the'UsedRange of the active sheet. This stops the routine checking the entire sheet'unnecessarily.'-------------------------------------------------------------------------------If Column1.Rows.Count = 65536 ThenSet Column1 = Range(Column1.Cells(1), Column1.Cells(ActiveSheet.UsedRange.Rows.Count))Set Column2 = Range(Column2.Cells(1), Column2.Cells(ActiveSheet.UsedRange.Rows.Count))End If'Perform the comparison and set cells that are the same to yellow'----------------------------------------------------------------Dim intCell As LongFor intCell = 1 To Column1.Rows.CountIf Column1.Cells(intCell) = Column2.Cells(intCell) ThenColumn1.Cells(intCell).Interior.Color = vbYellowColumn2.Cells(intCell).Interior.Color = vbYellowEnd IfNextEnd Sub
4. Press 'F5' to run the routine and compare the columns.

Read more ►

How to Unlock Excel 2007 Password Protection

Unlock Document Password
1. Open the Excel file in Excel 2007. You will need to enter the correct password to open the file.
2. Click on the Microsoft Office Button, located in the upper right corner of the Excel 2007 program. Move your mouse over 'Prepare' on the left hand side of the window. Choose 'Encrypt Document' from the list of options on the right.
3. Place your cursor to the right of the password in the box. Use the backspace key to erase the password. Click 'OK' when you are done. The Excel 2007 document is no longer password protected.
Unlock Worksheet Password
4. Open the Excel 2007 file that you need to work on. If needed, select the correct locked worksheet from the list of sheets at the bottom of the Excel document.
5. Select the 'Review' tab at the top of the screen.
6. Locate the 'Change' area and click on either 'Unprotect Sheet' or 'Unprotect Workbook.'
7. Type in your password into the pop-up window. Click 'OK' when you are done. The worksheet, or workbook, is no longer protected.

Read more ►

How to Change the Color Scheme in an Excel Chart

Change Chart Colors in Excel 2007
1. Open the spreadsheet containing the chart you want to edit.
2. Single-click the chart once to select it.
3. Single-click the individual piece of the chart you want to change to select the piece.
4. Right-click the selected piece and choose the 'Format Data Point' option.
5. Click 'Fill' in the left-hand menu, and then choose the fill option that meets your design needs. Select your desired color, gradient, picture or texture and the selected chart shape is updated immediately. Click 'Close' to close the Format Data point window.
Change Chart Colors in Excel 2003 and earlier
6. Open the spreadsheet containing the chart you want to edit.
7. Single-click the chart once to select it.
8. Single-click the individual piece of the chart you want to change to select the piece.
9. Right-click the selected piece and choose the 'Format Data Point' option.
10. Click the 'Patterns' tab. Choose the fill option that meets your design needs by selecting your desired color, or by clicking the 'Fill Effects' button to view additional options. Click 'OK' to confirm your change and close the Format Data Point menu.

Read more ►

How to Remove Security From an Excel Spreadsheet

1. Open the Excel spreadsheet and enter your password.
2. Click the 'Review' tab on the Excel toolbar.
3. Click the 'Unprotect Sheet' button to remove security from the spreadsheet. Make sure you do not click 'Unprotect Workbook' unless you want to remove security from the entire workbook.
4. Enter the password for the spreadsheet if prompted and click 'OK'.

Read more ►

How to Create a Distribution List to Import Into Outlook 2007

1. Open a blank Excel worksheet. Enter 'First Name' into cell A1. Enter 'Last Name' into cell B1. Enter 'Email Address' into cell C1.
2. Enter the first name of the first person you want to add to the distribution list into cell A2. Enter that person's last name and email address into B2 and C2. Enter the names and email addresses of all of the contacts you want to add to the distribution list into the cells in the appropriate columns.
3. Click the 'File' tab in Excel 2010. Click 'Save As.' Enter a name for the file, such as 'Distribution List,' into the File Name field. Change the 'Save as Type' to 'CSV (Comma Delimited).' Click 'Save.'
Import the List
4. Open Microsoft Outlook and select 'Contacts' in the navigation pane on the left. Right-click your contact list and select 'New Folder.' Enter a name that relates to your distribution list and click 'OK.' Double-click the new folder to open it.
5. Click the 'File' tab, select 'Open' and choose 'Import.'
6. Select 'Import from Another Program or File' and choose 'Next.' Select 'Comma Separated Values (Windows)' and click 'Next.' Click the 'Browse' button. Find and select the Excel file you created and click 'Open.'
7. Choose whether you want duplicates allowed and click 'Next.' Select the folder you created in your contact list and click 'Next.' Click 'Finish' to import your distribution list.
Create the Distribution List
8. Select the folder containing the list you imported.
9. Click on 'New' and select 'Distribution List' or 'Contact Group.'
10. Type a name for the distribution list into the Name field.
11. Click 'Select Members' or 'Add Members.' Select the new folder in the Show Names From list.
12. Click on the contact at the top of the list. Press and hold the 'Shift' key and select the contact at the bottom of the list to select all of the contacts. Click 'Members,' click 'OK' and click 'Save and Close' to save your new distribution list.

Read more ►

How to Split a Cell Diagonally

Microsoft Word
1. Click anywhere within the table to make the 'Design' tab visible in Microsoft Word.
2. Click the 'Design' tab under 'Table Tools.'
3. Go to the 'Draw Borders' group and select 'Draw Table.'
4. Place your cursor at the top corner of the cell you want to split.
5. Click, hold and drag your mouse from the top, right corner of the cell to the opposite corner to split it diagonally.
6. Click the 'Draw Table' button to deactivate the 'Draw Table' option.
Microsoft Excel or PowerPoint
7. Click the 'Insert' tab in Microsoft Excel.
8. Go to the 'Illustrations' group and click 'Shapes' to expand its options.
9. Select the 'Line' option from the 'Lines' section.
10. Place your cursor on the top, left corner of the cell you want to split.
11. Click, hold and drag your mouse towards the opposite corner to split the cell diagonally.

Read more ►

How to Add a Second Y

1.
Create an Excel graph for all the rows and columns. At the end of this step you will have a graph with some very high lines and the rest flat along the bottom.
2.
Select one of the lines which are causing the skewing of the graph. If more than one line is the culprit, select the highest.
3.
With the lines selected, right click on the graph and select 'Format Data Series.' This will open another box. In the new box, select 'Secondary Axis.' Then click the Close button.
4.
Your graph will now display the second Y-axis on the right of the graph and your lines will be spaced out.

Read more ►

How to Disable Cell Drag in Excel

1. Open Excel and click the 'File' menu tab in the top-left corner of the screen.
2. Click 'Options' on the bottom-left of the menu to open the Excel Options window.
3. Click 'Advanced' in the left window pane.
4. Click the box next to 'Enable Fill Handle and Cell Drag-and-Drop' to clear the check mark.
5. Click 'OK' to close the window and save the changes.

Read more ►

How to Move Data on an Excel 2007 Worksheet

1. Open the worksheet you want to change. Find the document in your saved documents and select it--double click twice on it. The worksheet will then open in Excel.
2. Decide what actions you want to do. For example, you may have made a file that is your address book. When you first put the file together, you put in columns in the following order: first name, last name, phone number, address, city, state and zip code. You now see that it would be easier if you had arranged the data this way: last name, first name, address, city, state, zip code and phone number.
3. Add a column. Put your cursor anywhere in column A. Go to 'Insert' and choose 'Column.' A new column will appear and your data will now start in column B.
4. Select the column with last names in it by clicking on the letter that has your data in it. This will select the whole column of data--for example, if your last names are in column C by selecting C at the top of the column all the data will be highlighted.
5. Right click your mouse and a menu will come up where you can 'Cut' the data. This will make the data disappear from column C. Move your cursor to column A row 1 and right click. Select 'Paste' and your data will be moved to that column.
6. Delete column C to reposition the data. Your data now appears this way: last name, first name, phone number, address, city, state and zip code.
7. Repeat Steps 4 to 6 to move phone number from column C to column G and the data will be in the proper columns.
8. Save your data by either selecting 'Save' or 'Save as.'

Read more ►

How to Retrieve Values From Another Excel 2007 Workbook

1. Determine the location of the workbook you want to retrieve values from. For example, the workbook might be in the same directory as the one you want to display the values in, and it might be named 'other_workbook.xlsx.'
2. Find the the row and column of the value you want to bring in from the other workbook. For example, you may be looking for a 'total' that is sitting in cell G4 on Sheet1 in other_workbook.xlsx.
3. Type the following formula into the cell you want to display the value from the other workbook on:
=[other_workbook.xlsx]Sheet1!A1
4. Change 'A1' in the formula from Step 3 to the cell you determined in Step 2. In our example you'd change 'A1' to 'G4'.

Read more ►

How to Extract Pivot Table Data

1. Type the '=' sign, without the quotes, into an empty cell.
2. Click on a cell in the pivot table. A formula will appear next to the equal sign.
3. Press the 'Enter' key. The cell will now display the data from the Pivot table.

Read more ►

How to Wrap Text in a Cell

1. Highlight the cells you want the text to be wrapped in. Use your computer's mouse to left-click in the first cell you wish to select and continue to drag it over the desired cells. Release it when the cells are highlighted.
2. Click on 'Format' in the Excel task-bar or right-click on the cell block you have highlighted.
3. Click on 'Format Cells' and a pop-up window will appear.
4. Click on the 'Alignment' tab and check the 'Wrap Text' box on the left-hand side.
5. Click 'OK' to close the 'Format Cells' pop-up window. The highlighted cells should now wrap when you resize your cell box.

Read more ►