Excel

Browse » Home » All posts

Thursday, February 16, 2012

How to Save a Workbook With a Password in Excel

Password Protect Workbook
1. Double-click on the Excel document to open it up.
2. Click on the 'File' tab at the top of the screen. Select 'Info' from the menu that covers the left side of the window.
3. Click on the 'Protect Workbook' button in the 'Permissions' area of the screen. Choose 'Encrypt with password' from the list of options. Enter in your desired password and click 'OK.' Enter the password again to confirm it, and press 'OK.'
Protect Document Elements
4. Double-click on the Excel file to open it up. Select the worksheet that you want to protect from the list of worksheets at the bottom of the Excel window.
5. Select the 'Review' tab at the top of the screen. Locate the 'Changes' area of the ribbon and select either 'Protect Sheet' or 'Protect Workbook.' Protecting the sheet will allow you to lock down the particular worksheet you are on from any changes. Protecting the workbook will prevent anyone from making changes to the overall design of the document, like adding new worksheets or altering the name of a current worksheet.
6. Place check marks next to the items that you want to allow people to alter. Anything without a check mark will be locked down. If you chose 'Protect Workbook,' do the opposite, as a check mark will signify to lock down that aspect of the workbook.
7. Type in a password into the field at the top of the window. Press 'OK' to continue. You will have to enter the same password again, and press 'OK' again to lock down the document.

How to Move a Column in Microsoft Excel

1. Open the Excel workbook to the worksheet containing the column you want to move. If you are moving it to a different workbook, open the second workbook to the target worksheet as well.
2. Click in the column header for the column you want to move. Use the built-in Excel column header with the pre-assigned letter, not a user-entered column header. Clicking the built-in header will select the entire column.
3. Press the 'Ctrl' and 'X' keys simultaneously to cut the column from its original location.
4. Click the column header to the right of where you want the column inserted, whether it is in the same worksheet or elsewhere. This will highlight the column you just clicked.
5. Press 'Ctrl' and 'V' simultaneously. This will paste the column to the left of your highlighted area, moving it to your target location.

Wednesday, February 15, 2012

How to Create a Curved Line Graph in Excel or Word

Microsoft Excel
1. Open Microsoft Excel. Highlight the data you want to insert in a graph.
2. Click the 'Insert' tab from the menu at the top of the page. You can insert pictures, clips and graphs here.
3. Look to the 'Charts' section on the drop-down menu. Select 'Line' and then choose either a 2-D or a 3-D line chart depending on your preferences. Your data will appear in a line graph.
4. Right-click on the line in the graph and select 'Format Data Series.' This is where you will choose options to make the line in the graph curved.
5. Select 'Marker Line Style' from the options at the right of the box that opens. Check the box that says 'Smoothed line' and click 'Close.' You will now have a curved line graph on your spreadsheet.
Microsoft Word
6. Open Microsoft Word. Click 'Insert' from the toolbar at the top.
7. Select 'Chart' and when the charts options box opens, choose 'Line' and click 'OK.'
8. Enter the data for the chart in the spreadsheet that appears. After you enter the data, close out the spreadsheet. The data will be applied to the graph on your document.
9. Right-click on a line in your graph and select 'Format Data Series.' Select 'Marker Line Style' and check the box that says 'Smoothed line.'
10. Click 'Close.' You will now have a curved line graph on your Word document.

How to Zip an Excel File

1. Open 'My Documents' in Microsoft Windows XP or earlier. If you are running Windows Vista, open 'Documents.' Both of these are usually found on the 'Start' menu.
2. Navigate through the folders until you locate the Excel file you want to zip.
3. Right-click on the file icon or name you want to zip and select 'Send To' from the pop up menu that appears.
4. Select 'Compressed (zipped) folder' from the sub-menu. A new zipped file will be created in the same folder. It will keep the same file name, but with a '.zip' extension, as your Excel file. This is the default, but you can change it just like any other file name.

How to Use Excel for Correlation of Data From Three Sensors

1. Arrange the data from the three sensors into columns in the Excel spreadsheet. To illustrate how to find the correlation, dummy numbers will be used as an example.
2. In column A, in cells A1 to A10, enter the following numbers:
0.33
0.05
0.77
0.90
0.25
0.66
0.30
0.12
0.05
0.85
3. In column B, in cells B1 to B10, enter the following numbers:
0.54
0.18
0.29
0.01
0.60
0.05
0.01
0.89
0.02
0.36
4. In column C, in cells C1 to C10, enter these numbers:
0.32
0.95
0.50
0.52
0.91
0.75
0.83
0.01
0.11
0.15
5. Make sure the Data Analysis add-in is switched on. Go to the 'Tools' setting on the menu bar, click 'Add Ins,' then check the box next to 'Analysis ToolPak.'
6. Click on the 'Tools' menu selection again, then scroll down to 'Data Analysis.'
7. Click on 'Data Analysis,' scroll down to 'Regression,' then click 'OK.'
8. Put the cursor in 'Input Y Range' then shade cells A1 to A10.
9. Put the cursor in 'Input X Range' then shade cells B1 to C10.
10. Click on 'OK' and the spreadsheet will produce a regression output data table automatically.
11. Look under regression statistics. The third number down is called 'Adjusted R Square.' This number represents the correlation of these three columns of data, which in this case are slightly negatively correlated with a value of -.16.

How to Copy and Paste Multiple Cell Contents in Microsoft Excel 2003

1. Highlight the cells that you wish to copy. Simply left-click on a cell and hold the mouse button as you drag the cursor over the multiple cells you wish to copy. Then let go of the mouse button and the cells will stay highlighted.
2. Copy the cells. To do this, scroll to the “Edit” tab on the command bar and select “Copy.”
3. Highlight the cells you wish to paste into. Find the group of cells that you wish to paste into; they must number the same as the cells you copied. Highlight the cells that you wish to paste into by following the directions in Step 1.
4. Paste into the cells. To paste your cells into the new cells, scroll to the “Edit” tab on the command bar and select “Paste.”

Tuesday, February 14, 2012

How to Create a Mirrored Image in Excel

1. Select or create the image you wish to manipulate in just about any software program you want. The image can be simple or intricate, homemade or an object that you found in an obscure photo album.
2. Click the image so that the entire object is highlighted. This will ensure that whole image will be copied to Excel.
3. Right click and select 'Copy.' Open or go to the Excel window and click on the cell where you want the image to be.
4. Pick 'View' from the top of the Excel window. If there is not a check mark next to the Drawing toolbar option, click 'View,' 'Toolbars' and 'Drawing.' A separate toolbar along the side, bottom or top of your window will appear.
5. Choose the 'Draw' option on the Drawing toolbar. In most versions of Excel it has an arrow pointing up or down next to it. A pop up window will appear next to it. Click 'Rotate or Flip' and then 'Flip Horizontal' or 'Flip Vertical' depending on how you want or image to look.
6. Drag the outer corner of your image inward or out to change the size.

How to Construct a Categorical Frequency Table in Excel 2007

1. Enter your data into one column. For example, if you have a list of 10 house prices that you want to categorize, enter them into cells A1 to A10.
2. Enter your bin values into the next column. In our example, you would enter your bin values (for example, 100,000, 200,000, 300,000) into column 2. Bin values are optional: If you do not enter values for bins, Excel will choose bins for you.
3. Click on 'Data Analysis' from the 'Data' tab.
4. Click on 'histogram' from the list box.
5. Enter the range for your chart data into the 'Input Range' box. For example, enter 'A1:A10.'
6. Enter the range for the bin values into the 'Bin Range' box. For example, if you have four bins in cells B1 to B4, enter 'B1:B4.' If you also want to display a histogram, check the 'Chart Output' box.
7. Press 'OK.' Excel will enter the frequency table into the Excel worksheet.

How to Change Decimal Formatting Placement Within Cells in Microsoft Excel 2003

1. Open and access your spreadsheet file by clicking on the “File” tab and scrolling to “Open.”
2. Activate the cell or cells in which you wish to change the decimal formatting placement. To activate the cells that you wish to change, either left-click on a single cell or left-click and hold as you drag the cursor over multiple cells to highlight them.
3. Open the cell properties box. To do this, right-click on the cell or cells that you wish to change the properties for. A sub-menu will open. Select “Format Cells.”
4. Change the cell decimal formatting placement. Under the cells properties box, left-click on the “Number” tab. Under the category drop-down menu left-click on the “Number: selection; a “Decimal Places” box will open inside of the cells properties box to the right. Use the up/down arrows to change the decimal places. Click “Okay” to implement changes.

Monday, February 13, 2012

How to Calculate a Weighted Average in Excel 2007

1. Enter your two sets of values that you wish to find the weighed average for into your spreadsheet, separating them into two columns. For the sake of this example, assume that the values in the first column stretch from A1 to A5 and the values in column B stretch from B1 to B5.
2. Type the following formula into a blank cell, cell C1, without quotes: '=SUM(B1:B5)' This formula will calculate the sum of the second column of numbers.
3. Type the following formula into another blank cell, cell C2, without quotes: '=SUMPRODUCT(A1:A5,B1:B5)' This formula calculates the total amount paid into the first and second columns.
4. Type the following formula into a final blank cell, cell C3, without quotes: '=C2/C1' This calculates the total weighted sum for the first column when compared to the second column.

How to Use Percentages in MS Excel

1.
Use MS Excel to display decimals as percentages.
Format a cell to display a percentage. In Excel, type a decimal value into cell A1, such as 0.3. Press 'Enter.' On the 'Home' tab of the ribbon, in the 'Number' section, click '%' to display 30%.'
2.
Calculate percentages by dividing an amount into the total.
Calculate a percentage. If you have 100 boxes and 12 of them are red, find the percentage of red boxes by dividing the amount,12, by the total, 100. In Excel, enter the following:Cell A1: 12
Cell A2: 100
Cell A3: =A1/A2In cell A3, press 'Enter' to view the result, 0.12. Format cell A3 as a percentage---as in Step 1---to display '12%.'
3. Figure the amount of a percentage. If you are buying a camera for $150 and you want to find how much a 5 percent sales tax will be, multiply the total, 150 by the decimal that represents the percentage (divided the percent, 5, by 100), 0.05. In Excel, enter the following:Cell B1: 150
Cell B2: .05
Cell B3: =B1*B2In cell B3, press 'Enter' to see the result, 7.5. To show the value as a dollar amount, format cell B3 as currency. On the 'Home' tab of the ribbon, in the 'Number' section, click '$' to display '$7.50.'
4.
Use Excel to calculate a discount or an original price.
Find the original amount from a total that has been reduced by a percentage. If you are shopping for shirts at 30 percent off and the reduced price is $21, calculate the original price by dividing the discounted price, 21, by the difference between 100 percent and the 30 percent discount (1 minus 0.3), in this case 0.7. In Excel, enter the following:Cell C1: 21
Cell C2: .7
Cell C3: =C1/C2In cell C3, press 'Enter' to see the original price, 30. Format cell C3 as currency to display '$30.00.'
5.
You can use percentages to figure dollar amounts.
Increase a total by a percentage. If you get a 3 percent raise and your salary is $48,000, figure your new salary by multiplying your current yearly wages, 48,000, by 1 plus the percentage of the increase (1 plus 0.03), 1.03. In Excel, enter the following:Cell D1: 48000
Cell D2: 1.03
Cell D3: =D1*D2In cell D3, press 'Enter' to see your new salary, 49,440. Format cell D3 as currency to display '$49,440.00.'
6.
MS Excel can help with budgets and accounting tasks.
Decrease a total by a percentage. If your monthly entertainment expenses are $100 and you want to reduce expenditure by 5 percent, multiply your current budget, 100, by 1 minus the percentage of the decrease (1 minus 0.05), or 0.95 in this example. In Excel, enter the following:Cell E1: 100
Cell E2: .95
Cell E3: =E1*E2In cell E3, press 'Enter' to view your revised entertainment budget, 95. Format cell E3 as currency to display '$95.00.'

How to Get to VBA in Excel 2007

1. Click the Microsoft Office icon at the top-left corner of the Excel spreadsheet. Select the 'Excel Options' button at the bottom of the drop-down menu.
2. Navigate to the 'Popular' tab at the left side of the window. Locate the 'Top Options For Working With Excel' heading.
3. Check the check box labeled 'Show Developer Tab In The Ribbon' underneath the 'Top Options' heading. Click 'OK.'
4. Click the 'Developer' tab at the top of the screen. Select the 'Visual Basic' option underneath the Microsoft Office button to bring up the VBA screen.
5. Double-click the 'Sheet1' entry at the left side of the VBA window to bring a blank screen. Type your VBA code into the blank area. Click on the other sheet options if you want to add VBA code to the other sheets in your Excel workbook.

How to Center a Worksheet Horizontally in Excel 2007

1. Select the Office Button and click 'Open.' Double-click the file you want to modify in the 'Open' dialog box. It loads on your page.
2. Select the worksheet you want to align in the workbook. To center each worksheet in your file, right-click on any worksheet tab (at the bottom of your page) and choose 'Select All Sheets' from the pop-up list.
3. Select the 'Page Layout' tab. Click the arrow on the right of the 'Page Setup' group.
4. Select the 'Margins' tab in the 'Page Setup' dialog box.
5. Go to the 'Center on page' section and check 'Horizontally.' Click 'OK.'

Sunday, February 12, 2012

How to Put a Picture in the Background on an Excel Spreadsheet

Microsoft Excel 2003
1. Open your spreadsheet. Click on the tab labeled 'Format.'
2. Scroll over 'Sheet,' and then select 'Background.'
3. Browse through your pictures to find the one you want to add. Click on the file name, then click 'Insert.'
Microsoft Excel 2007
4. Open your spreadsheet. Click on 'Layout' in the top menu bar.
5. Click 'Background,' and then browse through your pictures.
6. Select the picture you want to set as the background and click 'Insert.'

How to Create a Drop

1. Click in the cell that you want to have a drop-down 'pick' list. Select the 'Data' tab. In the Data Tools group, click 'Data Validation.' The Data Validation dialog box appears.
2. Select the 'Settings' tab. In the Allow box, select 'List.'
3. If you have the items typed in a range (one word per cell, either across or down) and in the same worksheet, then enter the range in the Source box (or click the icon to select the range from the worksheet). Alternatively, type the items in the Source box, separating them by a comma. When you are finished, click 'OK.'
4. Navigate to your worksheet. You will see a blank cell with a drop-down arrow. When you click the drop-down arrow of the box, a drop-down will display where you can select the desired response.

Subscribe to: Posts (Atom)