July 2012 - Excel

Browse » Home » Archives for July 2012

Saturday, July 28, 2012

How to Do a VLOOKUP With Two Spreadsheets in Excel

1. Select the cell in which you want the returned value.
2. Click on the formula bar, the long empty box at the top of the screen.
3. Type =VLOOKUP(“text you want to match”,Be sure to include the double quotes.
4. Click the tab of the other spreadsheet, the one holding the searchable table.
5. Highlight the table.
6. Click back to the original spreadsheet. Now the location will be in the VLOOKUP function.
7. Click the formula bar again, add a comma after the table location and type in which column you want the value returned from. For example, if you want to return two columns to the right of the search column, type 3.
8. Type a closing parenthesis and press “Enter.”

How to Add Radio Buttons to Microsoft Excel for Data Collection

1. Define the mutually exclusive data to be collected. For example, if the spreadsheet is used to track clothing by size, the option buttons might be for small, medium and large sizes. This prevents mistyping information.Select the 'Developer' tab; from the 'Controls group,' press 'Insert > Option Button.' Place the cursor into the position where the upper left corner of the button and label are desired, and then drag a square, creating the frame. Do the same for the remaining option buttons to be created. Excel will name these 'Option Button 1,' '2,' and '3.'
2. Select 'Option Button 1,' and in the 'Developer' tab, 'Controls' group, select 'Properties.' A new window opens allowing the option button to be customized. From this pane, the font, color, status, control, caption and important functions can be controlled. Define the caption, select fonts, colors and size, and then create the functions for the button. Do the same for the remaining option buttons to be grouped.
3. Select the option buttons. Using the 'Drawing Tools>Format' tab in the 'Align' group, use the 'Align' button to position the option buttons as desired, then use the 'Group' button to create the Option Button Group.
4. Add the appropriate commands and cell links as needed for the option buttons, and then protect the cells over which the buttons are located. When the spreadsheet is completed, protect the Worksheet in the 'Review' tab, 'Changes' group, and the option buttons will be functional.

How to Insert Auto Numbering in a Header for Excel 2003

1. Run the Microsoft Excel 2003 application, and open the workbook that you want to edit.
2. Click 'View' in the menu bar, and then click 'Header and Footer' in the drop-down list.
3. Click the 'Custom Header' button.
4. Click the section of the header that you would like to add automatic page numbering to.
5. Click the '#' icon to add automatic numbering to the specified section.
6. Type any additional text that you want to display in the header, and then click 'OK' twice to save your header changes.

Friday, July 27, 2012

How to Use Multiple Regression in Excel

Excel for Multiple Regression
1. Enter the data you will use to conduct your regression analysis into an Excel spreadsheet. You can enter the data by hand or import a data file from another source, such as an ASCII file or another spreadsheet, into Excel.
2. Unlock the Data Analysis tool from the add-ins menu and install it. Open Excel, click “Tools” and select “add-ins” from the drop-down menu that appears. A smaller window opens that displays a set of options. Check the box next to “Analysis ToolPak” and click “OK.” The Data Analysis option appears in your Tools menu, ready for use. If you are using Excel 2007, you can access the Data Analysis add-in by clicking the Microsoft Office button in the top left corner of an open Excel workbook. Click the button and then click “Excel Options.” A new window opens, displaying a set of options on the left side. Choose “Add-ins,” select “Analysis ToolPak” and click “OK.”
3. Click the Tools menu in Excel and select Data Analysis (in Excel 2007, click the “Data” tab and click the Data Analysis button). A window opens that displays a menu of analysis tools. Scroll to “Regression” and click “OK.”
4. Enter the values for dependent variable (Y) and independent variables (X) by clicking on the applicable cells and columns in your Excel data sheet. After selecting the ranges of data to be entered for analysis, click 'OK.' Excel runs the procedure and display your results on a new worksheet.
5. Examine your summary output, starting with the regression statistics at the top of your output. Note the value of R-square, which tells you what percentage of the variability in the dependent variable (for example, average salaries) is explained by your regression model. Then note the values of the coefficients and corresponding t-statistics and significance levels. A t-statistic of 2 or greater indicates statistical significance, meaning that the relationship between that independent variable and the dependent variable is likely not due to random chance.

How to Compare Two Columns in Excel 2007

1. Activate Excel and open a new worksheet. Set up three different columns entitled 'A,' 'B' and 'C'. Allow for five data entries within each column so that you are working with a 3x5 grid.
2. Enter the data for the two known columns in 'A' and 'C'. Leave column 'B' blank for the time being, and make sure not to enter zeros in the 'B' column. Column 'B' needs to remain blank because this is where the formula will be entered and function.
3. Select the first cell in column 'B' and enter the following formula therein; =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),'',A1)
4. Navigate to 'Fill' from the 'Edit' menu and click 'Down'. The duplicate data will appear in column 'B.' Rinse and repeat as needed with new data sets.

How to Merge Columns From Two Tables

1. Open the Excel file that contains the two tables with the data you want to merge.
2. Select the cell where you want the merged column to start and type in a header for the column. Select the cell directly beneath the header. If you want the merged call to be part of one of the tables, right-click on the top of the column where it lists the column letter, directly to the right of where you want the new column to go. Choose 'Insert.'
3. Enter in the following: =CONCATENATE(XX,' ',YY) Make 'XX' equal to the first cell that you want to merge, and make 'YY' equal to the cell in the second column you want to merge with the first. Both 'XX' and 'YY' need to follow Excel's standard naming rules, which list the column letter followed by the row number, like 'A1' or 'C23.' Press 'Enter' when you are done typing in the formula. The two cells will merge in your new column.
4. Click on the cell that you just entered the formula into and move your mouse over the lower-left corner of the cell. When the mouse cursor turns into a plus sign, click and hold the mouse button. Drag the mouse down until you have reached the last row of the column and release the mouse button. The entire column will fill with data merged from the two original columns.

How to Make a Bar Line Graph in Excel

1. Launch Excel. Click the 'File' tab and select the 'New' option from the File menu to create a new Excel spreadsheet.
2. Enter the data for the graph into the cells. Enter the data you want to appear in the bar graph in one row and the data you want to appear in the line graph in an entirely separate row. Each piece of data must be entered into its own cell. For the bar graph, each cell will become its own bar. For the line graph, each cell will represent a specific point on the line.
3. Click on the first data-containing cell (upper-left corner) and drag the mouse cursor to the last data-containing cell (bottom-right corner) to highlight all of the cells that contain data.
4. Click the 'Insert' tab, followed by the 'Bar' option beneath the 'Charts' heading. Select the 'Stacked Bar' option from the Bar menu to transform the data into a bar chart. By default, the data entered in the first row appears as a blue bar, while the data entered in the second row appears as a red bar.
5. Click on the data series that you want to change to the line portion of the graph (the red bar, for example). Click the 'Design' tab, followed by the 'Change Chart Type' option beneath the 'Type' heading. Click the 'Line' option and select the type of line chart you want to use from the Line menu. The data series you selected will now be transformed into a line chart, giving you a bar and line graph.

How to Copy Column Values to Rows in Excel 2007

1. Copy all of the blocks in the column that you want to convert to a row. You can copy multiple blocks at once by holding down the 'CTRL' button as you click on each block in the column.
2. Right click anywhere within the highlighted blocks and select 'Copy.' Another method for copying the information in the columns is to press the 'CTRL' and 'C' buttons at the same time.
3. Choose the row where you want to paste the information, right click on the first block and select 'Paste Special.' A menu with several options will pop up.
4. Place a mark next to 'Transpose' in the bottom right section of the menu and click 'OK.' The information in the column will paste into the row you chose.

How to Use the LINEST Function in Vista and Excel

1. Highlight a set empty cells in your spreadsheet which composes 5 rows and 3 columns. Click on the 'Formulas' tab, and then select the 'More Functions' menu. Select 'Statistical' sub-menu, and then select the 'LINEST' function. The LINEST 'Function Arguments' dialog will open.
2. Click on the red arrow next to the 'Known_y's' argument, and highlight the column or row of data on your spreadsheet that represent the y-axis of your argument. Click on the downward pointing red arrow in the 'Function Arguments' dialog when done.
3. Click on the red arrow next to the 'Known_x's' argument, and highlight the column or row of data on your spreadsheet that represent the x-axis of your argument. Click on the downward pointing red arrow in the 'Function Arguments' dialog when done.
4. Type 'TRUE' without the quote marks in both the 'Const' and 'Stats' boxes. Click 'OK' to close the 'Function Arguments' dialog.
5. Highlight the formula in the formula bar, and hold down Control, Shift, and Press Enter. Starting with the top left cell and going from left to right, the following information will be calculated: slope, intercept, the standard error of all values, the standard error of all values except the last, the R-squared value, the standard error for the y estimate, the F statistic, degrees of freedom, the regression sum of squares, and the residual sum of squares.

Thursday, July 26, 2012

How to Print the Gridlines of an Excel Worksheet

1. Start Microsoft Excel, and open a spreadsheet that you would like to print, having the gridlines appear on the printed copy of the spreadsheet.
2. Choose the 'File' menu and click on 'Page Setup...' to open the 'Page Setup' dialog box. Within the 'Page Setup' dialog box you can choose settings that apply to your whole Excel spreadsheet.
3. Click on the 'Sheet' tab at the top of the 'Page Setup' dialog box to display the sheet settings for the Excel spreadsheet you have open.
4. Find the 'Print' section of the 'Sheet' tab in the 'Page Setup' dialog box, located in the middle of the dialog box.
5. Use your mouse to click the check box in front of the option 'Gridlines' to add a check mark to the box. This will make sure that the gridlines will print when you print your current spreadsheet.
6. Press the 'OK' button with your mouse to close the 'Page Setup' dialog box and set your current spreadsheet to print with gridlines.
7. Print your spreadsheet by choosing the 'File' menu, clicking on 'Print' and then selecting the settings you wish to apply to your printed spreadsheet. When your spreadsheet prints, you will notice that the gridlines have printed, as well.

How to Chart Cells From Two Different Worksheets in Microsoft Excel

Create the Chart
1. Enter data in rows by using row headers with data under them. You can also choose to enter data in columns by using column headers with data under them.
2. Select the cells containing the data you will use for the chart.
3. Select the 'Insert' tab on the top menu, and click the arrow in the bottom-right corner of the Charts group to open the Insert Chart window.
4. Choose the type of chart in the Templates column of the Insert Chart window.
5. Select the chart using the images in the right box in the Insert Chart window, and click the 'OK' button.
6. Move your embedded chart by clicking anywhere in the chart to activate the Chart Tools option on the top menu.
7. Click 'Chart Tools' on the top menu, select the 'Design' tab, and click the 'Move Chart' button in the Location section of the menu.
8. Embed the chart in any sheet in the workbook using the pull-down menu in the Object In option in the Move Chart window, if you choose to. Click the 'OK' button when you are done.
9. Move the chart to a new sheet that you can name using the box next to the New Sheet option in the Move Chart window, if you want to. Click the 'OK' button when you are done.
Add Data from a Second Worksheet to the Chart
10. Click anywhere in the chart to activate it.
11. Click 'Chart Tools' on the top menu, and select the 'Design' tab.
12. Click the 'Select Data' button in the Data section of the Design tab to open the Select Data Source window.
13. Click the 'Add' button in the Legend Entries (Series) box of the Select Data Source window to open the Edit Series window.
14. Name the series in the Series Name box. You can choose to type in a name or link to a cell that contains the name.
15. Select the square box next to the Series Name box in the Edit Series window to link to the name of the series. If it is on another worksheet, click the tab of that worksheet, select the cell containing the name, and click the square box in the Edit Series window.
16. Select the square box next to the Series Values box in the Edit Series window to select the data range you want to include in your chart.
17. Click the tab for the sheet containing the data, and select the range of data you want to include on your chart, and then click the square in the Edit Series window. Click the 'OK' button to include the new data from a second worksheet in your chart.

How to Rotate a Pie Chart in Excel

1. Open the Excel worksheet containing the pie chart you wish to rotate.
2. Click on the pie chart. Doing so will display the 'Chart Tools' menu at the top of the Excel window. Within this menu you will see tabs labeled 'Design,' 'Layout' and 'Format.'
3. Select the 'Format' tab. Navigate to the 'Current Selection' group. Locate the 'Chart Elements' box and click on the arrow next to it. Click on the desired data point or series.
4. Return to the 'Format' tab. Select 'Format Selection' from the 'Current Selection' group.
5. Locate the 'Angle of First Slice' box. Move the slider to the position corresponding to the desired degree of rotation. Alternatively, indicate the angle at which you wish the first slice to appear by entering a value between zero and 360.

How to Change to R1C1 Cell Reference in Excel 2007

1. Open Microsoft Excel 2007.
2. Click on the 'Office' button in Excel.
3. Click on 'Excel Options.'
4. Click on 'Formulas,' in the left-hand pane of the 'Excel Options' box.
5. Check the 'R1C1 reference style' check box under 'Working with formulas' to enable 'R1C1' style referencing. Clear this check box to use 'A1' style referencing.

Wednesday, July 25, 2012

How to Insert a Check Box on an Excel Spreadsheet

1. Click the 'Developer' tab.
2. Click 'Insert.'
3. Select 'Check Box' under the Active X controls.
4. Click where you want the check boxes to appear on the spreadsheet.
5. Go back to the 'Developer' tab and select 'Design Mode.' This allows you to design the check boxes.
6. Go back to the 'Developer' tab and select 'Properties' to change any properties for the check boxes.

How to Enable Excel Data Analysis in Office 2007

1. Open the Microsoft Excel 2007 application on your computer and then click on the 'Microsoft Office' button.
2. Click on the 'Excel Options' button from the bottom of the application and then click on the 'Add-ins' button.
3. Select the 'Excel Add-ins' option from the 'Manage' box and then click on the 'Go' button.
4. Click on the box next to the 'Analysis ToolPak' field so that it's selected and then click on the 'OK' button.
5. Click on the 'Yes' button if you are prompted to install the add-in for your computer. Once the add-in is loaded, click the 'Data Analysis' button from the 'Data' tab.

How to Enter 17 Digits Into Microsoft Excel 2003

1. Enter your 17-digit number in a cell in an open spreadsheet. If the number displays in scientific notation, you have to adjust the cell format to have it display all 17 digits.
2. Click on the 'Format' pull-down menu at the top of the spread sheet, and select 'Cells' to format how numbers will appear within an individual cell.
3. In the 'Format Cells' box that appears, select 'Numbers' from the tabs. You'll be presented with a series of choices for formatting numbers, such as currency, dates or percentage. Select 'Numbers' from the list. This will change the appearance of numbers in cells, removing scientific notation and displaying all digits as originally entered.
4. You may also want to check the box labelled, 'Use 1000 separator (,)', so that your 17-digit number is displayed with commas, as this makes the numbers much easier to read.

Tuesday, July 24, 2012

How to Make a Series the Same Color Across Multiple Charts in Excel 2010

1. Launch Excel and open the spreadsheet that contains the charts for which you want to create a common color for a data series. Click the chart to display the “Chart Tools” menu in the ribbon.
2. Click one of the data series in a chart for which you want to create a common color. Click the “Format” tab and locate the “Current Selection” section. Click the “Chart Elements” menu and select the “Data Series” option. Click the “Format Selection” button, which will open a separate window.
3. Click the “Patterns” tab and click a color in the “Area” section. The series will automatically take on the selected color. Repeat steps 2 and 3 for the same data series in each chart, selecting the same color each time.

How to Move Columns in Excel 2007

1. Open the Excel 2007 spreadsheet you wish to modify.
2. Select the top of the column you wish to move, such as column A. This highlights the entire column.
3. Right-click the column and click 'Cut.'
4. Place the column in the location you prefer. Right-click the location and click 'Paste.' Repeat these steps for other columns you wish to change. You have now moved columns in Excel 2007.

Monday, July 23, 2012

How to Remove Print Preview Lines in Excel 2007

1. Click the 'Page Layout' tab on the Ribbon at the top of the Excel screen.
2. Find the 'Gridlines' section inside the larger 'Sheet Options' section.
3. Uncheck the 'View' box to remove gridlines from the Print Preview screen. If you want to prevent the gridlines from appearing on a printed sheet as well, uncheck the 'Print' box.

How to Move Columns in Excel

1. Choose the column heading (1, 2, 3 and so forth) that you want to move and highlight it. You can also select several columns by holding down your left mouse button and sliding it across the columns which you would like to select, in case you want to move more than one column at a time.
2. Leave your mouse placed on the highlighted column(s) and right click your mouse button. This will bring up a pop up menu with several options to choose from.
3. Pick 'Cut' from the menu that pops up. This will make the column you want to move disappear, but your computer will store it in its temporary memory. As long as you don't cut anything else or close the program, your information will not be lost.
4. Select the column that is directly to the right of where you want your selected column to be placed, so that it is highlighted. This is how you let your computer know where you want to place the row that you are moving.
5. Go to the 'Insert' menu and click on the 'Cut Cells' option. The column(s) that you chose to cut will be pasted into your selected area.

How to Sort Columns in Excel 2007

1. Open your spreadsheet file in Microsoft Excel.
2. Press 'Ctrl' and 'A' to select all the columns in your spreadsheet.
3. Click 'Sort Filter' near the right side of the ribbon.
4. Choose 'Custom Sort.'
5. Select the first column you want to sort on, such as Column A, in the first drop-down box.
6. Select the criterion on which you want to sort this column, such as the value of the cell or the cell color, in the second drop-down box.
7. Select your desired sort order, such as A-to-Z or smallest-to-largest, in the third drop-down box.
8. Click the 'Add Level' button if you want to sort by additional criteria.
9. Check 'My Data Has Headers' if your spreadsheet has a header row. If you check this box, Excel will leave the header row as the very top row. Otherwise it would sort your column headings as though they were data.
10. Click 'OK.' Your Excel spreadsheet is now sorted by columns according to the criteria you set.

How to Make a Double Bar Graph on Microsoft Excel

1. Open a new Excel 2010 document.
2. Enter the names of the items you want on your double bar chart, starting in cell B1 and continuing to the right of that cell. These headers will become the labels printed at the base of each of the bar sets on the graph.
3. Type the names of the two categories that the double bar graph will track into cells A2 and A3. These labels will be located to the right of the bar graph. Each of these two categories will be assigned a color bar, which will appear next to each of the items on the graph.
4. Input your numerical data into the cells, starting with cell B2 and continuing down and to the right. When you're done entering information, select cell A1 and hold down the mouse button. Move the mouse to the bottom-right cell and release the button.
5. Select the 'Insert' tab at the top of the screen. Click the 'Bar' button located in the 'Charts' area of the ribbon. Choose any of the 'Clustered' bar chart options, as opposed to the 'Stacked' options. Your double bar chart will appear on the spreadsheet.

How to Create a Frequency Table in Excel Using Pivot Tables

1. Highlight the cells containing the data, if you’re starting the pivot table from scratch.
2. Open the Data drop-down menu and select “PivotTable Report…”. A new frame will open. Click the two “Next” buttons that you see. Click “Existing Worksheet” and select a cell to be the upper-left corner of your pivot table.
3. Click “Layout.” A new panel will open. Drag the field name to the square center of the table that you want to know the frequency of. Double-click it and select “Count.” Drag the same field from the far right into the left column where the row labels go. Click “OK” and then click “Finish.” You will be sent back to the Excel spreadsheet, which will now have a pivot table starting in the cell where you requested it be created.
4. Group continuous data by right-clicking your mouse on the column with the field labels. (An example of continuous data is 'revenue,' while an example of discrete or nominal data is “highest degree achieved.”) Select “Group and Outline” in the menu that pops up and then select “Group.” The range of data will be displayed for you. Select the interval width you want the data grouped into, and enter it in the “By:” field. Select “OK.” The table will now collapse into a more-compressed form and the frequencies listed will go up as data points are added together. If an interval width has no data in it, it won’t be given a row in the table.

Sunday, July 22, 2012

How to Change the Text Box Size in Excel 2003

Inserting a Text Box
1. Click on 'View' on the main toolbar, then 'Toolbars' and ensure there is a check mark next to the 'Drawing' toolbar.
2. Locate the 'Drawing' toolbar on your screen.
3. Click on either the 'Text Box' or 'Vertical Text Box' commands on the 'Drawing' toolbar. Your cursor changes to the 'Text Box' tool.
4. Click and drag your mouse to create a text box.
Resizing a Text Box
5. Locate the text box that you wish to resize, then click anywhere in the text box to select it.
6. Change the width of the text box by clicking and dragging from the central anchor point on either side of the text box.
7. Click and drag from the central anchor point on the top or bottom of the text box to change the height of the text box.
8. Change the width and height of the text box simultaneously by clicking and dragging from any of the text box's corner anchor points.

How to Format an 'If Statement' in Excel for a Blank or Filled Reference

1. Open the Microsoft Excel 2010 worksheet. Press 'Alt F11' to open the VBA console.
2. Select a module from the list on the left side. If no module exists, right-click on a worksheet in the list, move the mouse pointer over 'Insert' and choose 'Module.' Select the module that appears.
3. Type 'sub testing()' into the first line of the module and press 'Enter.' Change 'testing' to whatever word you want as it is simply the name of the subroutine. Excel VBA automatically adds the 'End Sub' command to the last row of the module.
4. Type 'Dim x as range' into the next line. This will establish 'x' as a variable, which you will need in the next statement.
5. Type 'For each x in range ('XX:YY')' into the next line. Change 'XX' to the top-left cell in the range you want to work with and 'YY' to the bottom-right cell. This will create a loop that will go through each cell in the range one by one.
6. Type 'If IsEmpty(x) Then XXX Else YYY' into the next line. Change 'XXX' and 'YYY' to whatever code you want to run. The IsEmpty command will check each cell and perform the action after 'Then' if the cell is empty. 'Else,' and the code after it, are optional.
7. Type 'Next' on the next line. This tells Excel to loop back to the 'For' statement and move on to the next cell.

Friday, July 20, 2012

How to Determine an MS Excel File Version

1. Click on the Windows “Start” button then on “Computer” to open Windows Explorer.
2. Browse your computer to find the Excel file.
3. Check the file extension. If it is '.xlsx' then the file was created with Excel 2007. The extension '.xls' indicates older program versions.
4. Right-click on the Excel file and choose “Properties” from the pop-up menu.
5. Select the tab “Details” (in Windows Vista or 7) or “Summary” then “Advanced” (in Windows XP).
6. Read the Excel version in the line “Type,” for example, 'Microsoft Office Excel 2003.'

How Can I See Excel 2003 Commands in Excel 2007?

1. Click the 'Office' button, and select 'Customize Quick Access Toolbar.' This action opens a pop-up window.
2. Select 'All Commands' from the 'Choose' menu. This action displays a list of available Excel commands that can be added to the Quick Access Toolbar.
3. Select a command from Excel 2003 that you want to view independent of the ribbon interface. Click the 'Add' button. Repeat this process for each Excel 2003 command that you want to view in the Quick Access toolbar. Click the 'OK' button when you have finished adding Excel 2003 commands.

How to Remove Hyperlinks in Microsoft Excel 2007

1. Open Excel using the 'Start' menu or another shortcut. You may also type 'Excel' in the Start menu search box and click on the resulting link.
2. Click the 'Open' option and select the spreadsheet you want to work with. Locate the cell containing the link.
3. Right-click the link you want to delete and click the 'Remove Hyperlink' option. Repeat for each link you want to remove.

How to Calculate Percentage Increases in Excel 2007

1. Type 'Beginning Number' in cell A1, and then type your beginning number in cell B1. For example, type '50' in cell B1.
2. Type 'Ending Number' in cell A2, and then type your ending number in cell B2. In the example, type '100' in cell B2.
3. Type 'Percent Increase' in cell A3.
4. Type '=((B2-B1)/B1)' in cell B3. In the example, your result will be 1, or 100%.

How Can I Disable All Double Clicking in Excel 2003?

1. Open the document you wish to disable double-clicking on in Microsoft Excel 2003.
2. Press the 'Alt' key on your keyboard and 'F11' key simultaneously. The VBA editor will appear.
3. Double-click 'Workbook' in the upper left window pane in the VBA editor. A new window will appear.
4. Copy and paste the following code into the new window:Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ByVal Target As Range, ByVal Cancel As Boolean)Cancel = TrueEnd Sub
5. Press 'Alt' and 'F11' simultaneously again to save the code and return to Excel. The string of code disables all double-click actions in the Excel workbook.

How to Create a Stacked Bar Graph in Excel

1. Click 'Start,' then 'All programs,' then 'Microsoft Office,' then 'Microsoft Excel.'
2. Press 'Ctrl' 'O,' then locate and open the Excel document which contains the data to be turned into a stacked bar chart.
3. Highlight the data that you want to create the chart from.
4. Click 'Charts,' then 'Bar,' then 'Stacked Bar.' The stacked bar chart will automatically appear in the worksheet. You have now inserted a stacked bar chart into your Excel document.

Thursday, July 19, 2012

How to Allow a Shared Workbook

Microsoft Excel 2003
1. Launch Excel 2003, then open the Excel workbook you want to share with other users on the network.
2. Click the 'Tools' option on the menu bar, then click 'Share Workbook.' Wait for the 'Share Workbook' options dialog window to appear.
3. Click and enable the checkbox next to the label that reads 'Allow Changes by More Than One User at the Same Time. This Allows Workbook Merging.' Click the 'OK' button.
4. Click the 'File' > 'Save' option on the menu bar to save the permission changes to the workbook.
Microsoft Excel 2007 or 2010
5. Open Microsoft Excel, then open the workbook you want to share.
6. Click the 'Review' tab on the 'Office Ribbon,' then click 'Share Workbook.'
7. Click the 'Editing' tab, then enable the checkbox next to 'Allow changes by more than one user at a time. This also allows workbook merging.'
8. Click the 'Advanced' tab, then select options for tracking and updating changes to the shared workbook. Click 'OK.'
9. Save the workbook to save the sharing and permission changes for the file.

How to Keep Track of Changes in Excel 2003

1. Open Excel from the Start menu on your toolbar.
2. Open the file for which you'd like to track changes.
3. Go to Tools > Track Changes.
4. If you want to see the changes while you are editing, select 'Highlight Changes.' Then select the boxes labeled 'Track Changes While Editing' and 'Highlight Changes on Screen.' When making a lot of changes, you might not want this option on because it can be distracting.
5. Decide who gets to make changes on your worksheet. Select the 'Who' tab and choose the users who can make changes. If you choose 'Everyone,' your file will be shared on the network.
6. Select the 'When' option to decide when changes are tracked. The most popular is option is 'All.'
7. Click 'OK' to save your options.

Wednesday, July 18, 2012

How to Convert XML Files Into an Excel Spreadsheet

1. Open Excel.
2. Click the Microsoft Office symbol in the upper left-hand corner. Select 'Open' from the menu.
3. Find the XML document you want to convert and click 'Open.' If the document does not seem to be present where it should be, you may need to select 'XML Files(*.xml)' from the 'Files of type:' drop-down box.
4. A small dialog box will open titled 'Open XML.' In this box select the radio button titled 'As an XML table,' then click 'OK.'
5. If another dialog pops up that has a message relating to creating a 'XML schema,' simply click 'OK.' The XML file should be opened as a spreadsheet.
6. To complete the conversion, save the spreadsheet as an Excel file by clicking the 'Office' button, then 'Save.'

How to Print Grid Lines in MS Word

Show Gridlines
1. Open the Word document that contains your table or labels.
2. Select 'Table' and 'Show Gridlines' from the toolbar to make gridlines visible. You will see the grid lines on your page.
3. Select 'File' and 'Print Preview' from the toolbar. The grid lines will not be visible.
4. Press the 'Escape' key to return to your page.
Add and Print Borders
5. Select the table or labels containing the grid lines you want to print.
6. Click on 'View,' 'Toolbars' and 'Tables and Borders' in the toolbar to open the 'Tables and Borders' toolbar.
7. Expand the 'Border Color' button by clicking on its arrow. Then select 'Gray-25%' (a color similar to that used in grid lines) for the border color.
8. Expand the 'Border' button by clicking on its arrow. Then select 'All Borders' from the list. A border will be applied to your entire table.
9. Select 'File' and 'Print Preview' from the toolbar. The borders you just applied will now be visible. Press the 'Print' icon on the Print Preview page to print your table or labels with the borders.

Tuesday, July 17, 2012

How to Use Paste Special in Microsoft Excel 2007

1.
Select the data you would like to work with and choose 'Copy.' Copying can be performed by right clicking your mouse on the area or clicking the copy icon in the Home Tab of the Ribbon. The Ribbon is the group of icons and tools located directly above the work area.
2.
Click the 'Paste' drop-down menu located in the Home tab on the Ribbon. Select 'Paste Special.'
3. Click on the cell or range of cells you would like your data to affect and click the
'Paste' drop-down menu again. Choose 'Paste Special.'
4.
Choose the function you would like to perform from the 'Paste Special' dialog box and click 'OK.' Your function will be performed as directed. There are numerous options available. For example, you can transpose a list of data to appear in reverse order. You can paste the copied value into another sheet or workbook and link them so that it automatically updates each time you edit the original cell.

How to Change Positive Data to Negative on Microsoft Excel

1. Click 'Start,' 'All Programs,' 'Microsoft Office' and then 'Microsoft Excel 2010.' Click 'File' and 'Open.' Locate your file and click 'Open.'
2. Double-click the cell containing the number you wish to change from positive to negative. Enter the negative symbol before the first number and press 'Enter.'
3. Select 'Number' from the drop-down list in the Number group of the Home tab of the ribbon bar. Click the expander arrow in the lower right of the Number group or 'Alt,' 'H,' 'F' then 'M' to show all the formatting options.
4. Select one of four options for displaying negative numbers on the Number tab. Click 'OK.'
5. Click 'Conditional Formatting' > 'Highlight Cell Rules' > 'Less Than' to set conditional formatting. Enter '0' (zero) in the first box and select an option from the second. Click 'OK.'

How to Add Numbers in Microsoft Excel if they Meet Certain Criteria

1. Select 'More Functions' from the 'Sum' drop-down menu in the 'Editing' section on the 'Home' tab. This opens the 'Insert Function' window.
2. Type 'sumif' in the 'Search For A Function' field and click the 'Go' button. Verify that 'SUMIF' is highlighted on the 'Select a Function' pane and click the 'OK' button. This opens the 'Function Arguments' window.
3. Click in the 'Range' field and enter the range of the data that needs to be tested; for example A1:A25 will test each cell starting with A1 and ending with A25 to see if it matches the criteria. The ':' represents 'through.'
4. Click in the 'Criteria' field and either type the criteria or enter a cell value. The cell value should contain the criteria.
5. Click in the 'Sum_Range' field and enter the range of cells that need to be added; for example, B1:B25 will sum any values that match the criteria. The results are displayed in the lower-left corner of the window next to 'Formula result =.' Click the 'OK' button.

Monday, July 16, 2012

Animate Objects in Microsoft Excel

Download and Install Excel 3-D Animation Engine
1. Open a browser and go to Microsoft's Download Center.
2. Locate the 3-D Animation Engine under Office products add-ons.
3. Click 'Download' and 'Run' to begin the program download.
4. Click 'Yes' to download and run the program on your computer.
5. Click 'Unzip' to open the file. Save the file to your desktop for easy access.
6. Click 'OK' to complete the program's installation.
Open and Configure Excel 3-D Animation Engine
7. Double-click on the 3-D Animation Engine file to open it. The program will automatically open in Excel 2007.
8. Click on the Macro warning box to enable Macros in this program. Click 'OK.'
9. Delete the animation example that is in the work space by clicking on the shape and hitting the backspace key. The program configuration is complete.
Animate an Excel Object
10. Click the Insert tab on the tool ribbon on the top of the spreadsheet.
11. Click 'Shapes' and choose a shape or object to animate. Pick an area on the work screen to place the animated object and click on that area. The shape will appear on the screen.
12. Left-click on the shape and choose 'Format Shape' to adjust the size, shape, color and angle of the shape. Close the 'Format Shape' box when finished.
13. Change the size of the shape by clicking on one of the handles on the box surrounding the shape and dragging the handle to increase or decrease the shape's size.
14. Click the 'Animate!' button to animate the object when you have finished formatting.
15. Click 'Save' to save the image. Type in a name for the image in the dialog box and click 'OK.' The image can accessed through Excel to be used in a spreadsheet or exported to a PowerPoint presentation.

How to Create Footers in Microsoft Excel 2003

1. Open the view menu. Scroll to the “View” tab on the command bar.
2. Access the page header/footer menu. Select “Header And Footer” from the “View” menu. A header /ooter properties box will open.
3. Select the footer. To add text to the footer, click on the “Custom Footer” button.
4. Create text for the footer. Enter the desired text for all sections: left, right and center.
5. Insert text into the footer. Click on the “Okay” button when you have finished entering the desired text. This will bring you back to the header/footer properties box.
6. Insert the footer into the spreadsheet. Click on the “Okay” button to add the footer into the spreadsheet.

How to Unhide Columns in Microsoft Project

Unhide Columns in Microsoft Project
1.
'Click' the column header to the right of the position in which the new column should appear, as in Image 1. This action will select and darken the entire column.
2.
'Click' the 'Insert' option from the menu bar, as displayed in Image 2. This will cause a drop-down menu to appear.
3.
Click 'Column' from the drop-down menu. This will bring up a dialogue box entitled 'Column Definition,' as in Image 3. This feature allows you to select the column to be displayed.
4.
Click the down arrow to the right of the 'Field Name' text box. This will cause a list of fields to display, as in Image 4. Select the name of the column you wish to unhide from the list.
5.
Click the 'Best Fit' button at the bottom of the Column Definition dialogue box to display the hidden column as in Image 5. You may also add a column by clicking 'OK,' but that will require manual column-width adjustment.

How to Crop Images in Excel

1. Start Microsoft Excel 2007 and open a workbook from your files that contains an image that you want to crop.
2. Select the image you want to crop by clicking on it. The image will be surrounded by a thin, black border and light blue sizing handles once it is selected.
3. Choose the 'Format' tab at the top of the Excel 2007 screen to display the Format ribbon. Locate the 'Size' section of the Format ribbon. This section is on the right side of the Format ribbon.
4. Click the 'Crop' button and thick, black lines should appear on the corners and sides of the selected image.
5. Hover your mouse over one of the black borders on a corner or a side and your cursor will change to two borders overlapping each other.
6. Crop the image by clicking your mouse on the border and dragging inwards while holding the mouse button down. The part of the image you are moving over will disappear.
7. Release your mouse button and view the effects. Continue to use this same process to crop other parts of the image.

How to Remove Fractions in Excel

1. Launch Microsoft Excel 2010 and open the spreadsheet that contains or will contain the numbers that need to be displayed without fractions.
2. Click a cell or highlight (Select) a range of cells by clicking on one cell and holding the mouse button down while dragging the cursor across a range of cells. Release the mouse button to select the highlighted range. You can also click directly on a column letter or row number to select the entire column or row. Add or remove from the current selection by holding down the 'CTRL' key while selecting the cells you do not want changed.
3. Right-click any of the selected cells and click 'Format Cells' from the context menu.
4. Click the 'Number' tab in the 'Format Cells' dialog box.
5. Click the desired format for the display and storage of the numbers in the selected cells. Make preference adjustments on the right side of the dialog box using the instructions at the bottom of the dialog box. The 'Sample' box will display a preview of the format for any data in the selected cell.

How to Change Interval of Category Axis in an Excel Chart

1. Start Microsoft Excel 2007 and open a spreadsheet from your files that contains a chart to which you want to change the interval of the category axis.
2. Click in the white area of the chart so the whole chart is selected. You can tell the entire chart is selected because a light blue outline will surround the entire chart.
3. Select the 'Format' tab at the top of the Excel screen to display the 'Format' ribbon. Locate the 'Current Selection' group-it is the first group in the 'Format' ribbon.
4. Click the 'Chart Elements' arrow to display the drop-down list. This is the top option in the 'Current Selection' section of the 'Format' ribbon. Choose '(Category) Axis' from the drop-down list.
5. Press the 'Format Selection' button right underneath the 'Chart Elements' drop-down list. The 'Format Axis' dialog box will appear on the screen. Click 'Axis Options' if necessary to display the options for changing the interval of the category axis.
6. Type the number of units you have to use in the 'Interval between tick marks' text box at the top of the 'Format Axis' dialog box.
7. Click the 'Close' button to close the 'Format Axis' dialog box and return to your Excel chart. You will now see that the interval for the category axis has changed.

How to Create a Bar Chart in Microsoft Excel 2003

1. Make a table that contains all of the data you want represented in your bar chart. Be sure to include column and row labels and numerical quantities when making your table.
2. Highlight all of the data in your table--including the column and row labels--and click the “Chart Wizard” button on the toolbar. The “Chart Wizard” button looks like a tiny column chart.
3. When the Chart Wizard appears, select “Bar” from the “Chart Type” menu, then select the subcategory of bar chart you would like to create. Click the “Next” button.
4. Use the “Data Range” box if you need to modify the data set. Select “Rows” from the “Series In” option to sort your data by row or “Columns” to sort your data by column. Click “Next.”
5. Enter titles and labels for your chart to make it easy to read. Excel will offer you a preview of how the chart will appear while you work.
6. Use the “Axes,” “Gridlines,” “Legend,” “Data Labels,” and “Data Table” tabs as additional tools for modifying the appearance of your bar chart. Each tab modifies a specific part of your bar chart. Click “Next” when you are finished modifying the appearance of your chart.
7. Select “As New Sheet” to place your bar chart in a new Excel spreadsheet or select “As Object In” to place the bar chart in the same spreadsheet that you built your data table. Click “Finish” to complete the creation of your chart and place it on a spreadsheet.

How to Synchronize Spreadsheets in Excel

Linking Separate Excel Workbooks Together
1. Open the workbook with the data you expect to be shared in Excel. This is the source workbook.
2. Open the workbook you want the data to be replicated in. This is the destination workbook.
3. Select the cell in the source workbook you want the data to come from, and enter 'Ctrl C' to copy it.
4. Switch to the target workbook, and select the cell you want the data to appear in.
5. Enter 'Ctrl V' to paste the reference in. If you examine the formula, you'll see that prior to the row and column entry, there will be a reference to the drive location (or network location) and the file name of the source workbook. An example would look like this: [C:\Book1.xlsx]Sheet1!A1. This means that Excel will pull the value from the linked file.
Synchronizing Worksheets In The Same Workbook
6. Open your workbook in Excel.
7. Hold down the 'Ctrl' key while clicking on multiple worksheet tabs. Be sure to leave one tab unselected when you do this. This will ensure that any edits you make on one tab will be replicated across all selected tabs, with data entered in the corresponding cells.
8. Click the tab that's not part of the group to undo the tab group selection. This will get Excel to revert to 'normal mode,' where changes in one tab only affect that tab.

How to Add an Analysis Toolpack in Excel 2003 GPO

1. Launch Excel 2003. To install the Analysis Toolpak on a version of Excel that was installed through a GPO (Group Policy Organization), you need a privileged account, or you must talk to your network administrator to unlock the procedure.
2. Click on the 'Tools' menu item. Navigate through the drop-down menu and select 'Add-Ins'. A dialog box will appear; this will show all the add-ons currently available.
3. Check the check box that says 'Analysis Toolpak.' You may need to click on the 'Browse' button to load it; it will be listed as Analysis.xla in the Add Ons folder for Excel. If you do not see it, you may need to reinstall Excel 2003 from your installation media. If prompted 'Do you want to install the Analysis Toolpak?', click 'Yes' to install it.
4. Click the 'Tools' menu item; there will now be a 'Data Analysis' entry added to the drop-down menu.

How to Insert a Dynamic Date in Excel

1. Start Microsoft Excel 2007, and open a spreadsheet from your files into which you want to insert a dynamic date. Or, you can start a new, blank spreadsheet.
2. Select the cell you want to insert the dynamic date into by clicking on it. The cell will be surrounded by a thick, black outline once it is selected.
3. Type the formula '=today()' (without the quotation marks) into the selected cell. This formula will instruct Excel to insert the current date into the cell where the formula is entered.
4. Press the 'Enter' key on your keyboard or the green check mark on the formula bar to input the formula into Excel. Excel will then accept the formula and display the current date into the cell where you typed the formula. The date will be updated every time the 'Enter' key is pressed when you are working in the workbook where the formula has been inserted.
5. Use the procedure above to insert the dynamic date into any other cells in the open spreadsheet that you would like to display the current date.

Sunday, July 15, 2012

How to Adjust the Color of Excel Bars in a Bar Chart

1. Open the formatted workbook and double-click the specific bar element you wish to change.
2. On the 'Patterns' tab in the 'Area' section of the 'Format Data Series' dialogue box, click your preferred color. You can preview it in the 'Sample' box in the lower left corner.
3. Click the 'OK' button. The chart will show the new color choice.
4. Save the changes to the chart and workbook.

How to Import a Word Doc Into an Excel Spreadsheet

1. Open Excel and click the 'Insert' tab in the toolbar at the top of the page. You can import a picture, chart or file here.
2. Click the 'Object' button and then click the 'Create from File' tab.
3. Click 'Browse.' A list of your folders and files will open. Select the Word document you want to import and click 'Insert.'
4. Click 'OK.' The Word document will now appear in your Excel spreadsheet. Click on the newly created object to move it to the desired position in the spreadsheet.

How To Use the List Feature In Excel

Excel 2003
1. Open Excel 2003. Create a new spreadsheet or open an existing spreadsheet.
2. Enter data headers in the first row. Enter the data below them.
3. Click and drag to select your data including the headers.
4. Open the 'Data' menu. Point to the 'List' menu and click 'Create List.'
5. Check the 'My list has headers' check box. Click 'OK.'
6. Click a header to sort the data based on that column.
7. Type in the bottom row containing an asterisk to add a row to your list.
8. Click and drag the handle at the bottom right corner of your list to expand the range.
9. Click outside the list cells to deselect the list.
Excel 2007 and 2010
10. Open Excel 2007 or 2010. Create a new spreadsheet or open an existing spreadsheet.
11. Enter data headers in the first row. Enter the data below them.
12. Click and drag to select your data including the headers.
13. Click the 'Home' ribbon and click 'Format as Table.' Select a table style from the drop-down menu.
14. Check the 'My table has headers' check box. Click 'OK.'
15. Click a header to sort the data based on that column.
16. Select the bottom right corner and press 'Tab' to insert a new row.
17. Click and drag the handle at the bottom right corner of your list to expand the range.
18. Click outside the list cells to deselect the list.

Saturday, July 14, 2012

How to Make a Time Sheet

Microsoft Excel 2010
1. Select a Microsoft Excel 2010 template. Open Excel 2010 and select the 'File' tab. Click 'New.' Select the 'Time Sheets' category. Review the available time sheets. Preview the time sheet by clicking the image. A small preview and description appears in the right task pane. Download the template by clicking the 'Download' icon beneath the image and preview.
2. Customize the template by adding personal details. These details include the time worked, your company name, address, manager name and the current date.
3. Save the changes to the time sheet by clicking the 'Save' icon on the Quick Access Toolbar.
OpenOffice
4. Select an OpenOffice template. Access the OpenOffice website. Type 'Time Sheets' in the search box. Review the available time sheets that appear. Click 'Use This' to download the template to your computer.
5. Open the downloaded file in OpenOffice Calc. Customize the template by adding personal details. These details include the time worked, your company name, address, manager name and the current date.
6. Save the changes to the time sheet by clicking the 'Save' icon.
Google Documents
7. Select a Google Documents template. Access the Google Documents website. Type 'Time Sheets' in the search box. Click 'Search Templates.' Review the available time sheets that appear. Click 'Use This Template' to download the template to your computer.
8. Open the downloaded file in Google Documents. Customize the template by adding personal details. These details include the time worked, your company name, address, manager name and the current date.
9. Save the changes to the time sheet by clicking the 'Save' icon.

How to Open a Wb3 File in Excel 2003

1. Open Excel 2003, and select 'Open' from the 'File' menu.
2. Locate the WB3 file that you wish to open in the 'Look in' list.
3. Click the name of the file, and click 'Open.'

How to Send Email Using Excel 2007 VBA

1. Start Microsoft Office 2007 and select the 'Developer' tab. Click on 'Visual Basic' and select 'Tools.' Click on 'References' check 'Microsoft Outlook 12.0 Object Library' and select 'OK.'
2. Type the following to create a new sub:Sub sendEmail()Press 'Enter.'
3. Type the following to create your variables:Dim oLookApp As Outlook.ApplicationDim oLookMail As Outlook.MailItem
4. Type the following to assign the outlook object and create a new email:Set oLookApp = New Outlook.ApplicationSet oLookMail = oLookApp.CreateItem(0)
5. Type the following to define the variables for your email:With oLookMail.To = 'john.doe@yahoo.com'.Subject = 'My Workbook'.Body = 'I'm e-mailing this workbook..'.Attachments.Add ActiveWorkbook.FullName.sendEnd With
6. Execute your sub to email the current Excel workbook.

How to Insert Hatching in Microsoft Excel Shapes

1. Right-click on the Excel 2010 spreadsheet shape in which you want to insert hatching.
2. Click 'Format Shape' to display an options box.
3. Click 'Fill' to display fill options.
4. Click 'Pattern Fill' from the list to display available fill patterns.
5. Click the hatching pattern you want to insert in the shape and then click 'Close.'

Friday, July 13, 2012

How to Create Rounded Borders in Excel

1. Open the Excel worksheet.
2. Click the “Insert” tab on the command ribbon.
3. Click the arrow under the “Shapes” button in the “Illustrations” group.
4. Click the preferred shape, such as “Rounded Rectangle.” The cursor will convert to a “ ” sign.
5. Click and drag the cursor on the worksheet where you wish to insert the rounded border. For example, click and drag inside a cell to create a row or column header.
6. Click the sizing handles on the shape to resize the rectangle.

How to Remove Protection From Cells in Excel 2003

1. Click 'Start,' then 'All Programs,' then 'Microsoft Office,' then 'Microsoft Excel 2003.'
2. Click 'File,' then 'Open.' Locate, select and open your workbook that has the cells you wish to unprotect.
3. Locate the protected cell.
4. Click the cell, then click 'Format,' followed by 'Cells.'
5. Click 'Protection.' Click the checkbox next to 'Locked' to remove the cell protection. Click 'OK' to close the Format Cells dialog. You have now removed protection from the cell.

Thursday, July 12, 2012

How to Insert and Size Diagrams in Microsoft Excel 2003

1. Access the diagram menu. Scroll to the “Insert” tab on the command bar and select “Diagram.”
2. A diagram selection box will open where you can select six different types of diagrams: organization chart, cycle diagram, radial diagram, pyramid diagram, Venn diagram and target diagram. Choose the desired diagram by left-clicking on it and then click the “OK” button to insert the diagram.
3. Place the diagram. To move the diagram around, left-click on the gray border and hold as you drag the diagram where desired on the spreadsheet.
4. Set diagram attributes. You can add levels to your diagram and size it by clicking on the “Insert Shape” button on the diagram properties box. You can easily label each level and shape by left-clicking on “Click to Add Text” field and entering the desired text. Finally, you can even change the type of the diagram by clicking on the “Change To” drop-down menu and selecting a different diagram shape.
5. Insert the diagram. To insert the diagram, left-click on the “X” in the right corner of the diagram properties box to close it; the diagram will be inserted into your spreadsheet when you do this.

How to Merge Data From Several Excel Workbooks

1. Click the Microsoft Office button on the screen's top-left corner, and select 'Excel options.'
2. Under 'Customize,' click 'All Commands' in the 'Choose commands from' list.
3. Check the box next to 'Compare and Merge Workbooks.' Click 'Add,' and then click 'OK.' The 'Compare and Merge Workbooks' icon will now appear in the quick access bar at the top of the window,
4. Move all workbooks to a single folder. If they share a name, rename them as necessary.
5. Open one workbook. Click the 'Compare and Merge Workbooks' icon.
6. Select the other workbooks whose date you want to merge. To select multiple workbooks, press the 'control' or 'shift' keys as you select them.
7. Click 'OK.'

How to Calculate Negative Times in Excel

1. Open your Excel 2010 worksheet. Click on an empty cell anywhere on the worksheet.
2. Type '-'1:30'' into the cell. The number '-.0625' should appear. Right-click the cell and choose 'Format Cells.' Choose 'Time' from the list on the left and click 'OK.' The number changes to '#####.'
3. Click on the 'File' tab at the top of the screen. Choose 'Options' from the list that appears.
4. Click 'Advanced' on the list on the left side of the Options window. Scroll down to the bottom of the 'Advanced' screen and look for the 'When calculating this workbook' heading. Click the box next to 'Use 1904 date system.'
5. Click 'OK' to close the Options window. The time that you entered now reads '-1:30.' You can perform normal mathematical operations on the number and it will behave exactly like a negative number would.

How to Make an Exponent on Excel

Insert Exponent into Formula
1. Open the Microsoft Excel spreadsheet that contains the formula where you want to add an exponent.
2. Click on the cell that contains your formula. The formula will appear in the bar above the Excel spreadsheet. Click on this bar and place your cursor where you want to add the exponential number.
3. Enter the following into the formula bar, without quotes: 'power(x,y)' where 'x' is the desired number and 'y' is the exponent. If needed, you can wrap the entire 'power' function in parentheses. Additionally, you can write this in shorthand as 'x^y', without quotes.
Have Numbers Display as Exponents
4. Open your Microsoft Excel 2010 spreadsheet where you want to make your numbers display as exponents.
5. Click the top-left cell of the area that you want to change, then hold shift and select the bottom-right cell of that area. If you want to change the entire spreadsheet, you can just click the small box that sits above the '1' and to the left of the 'A' in the top-left corner of the spreadsheet.
6. Right-click anywhere on the selected cells. Choose 'Format Cells' from the pop-up menu.
7. Click on 'Scientific' from the list on the left side of the Format Cells window. On the right side of the window, click the up and down arrows to determine how many decimal places you want to display, then click 'OK.' Excel will now display numbers in those cells like '1.25E 02,' which is the same as '1.25^2' or '125.'

Wednesday, July 11, 2012

How to Open a Password Protected Microsoft Office Excel Sheet

1. Browse to the location where the password-protected Excel document is saved using Windows Explorer.
2. Double-click the document you wish to open. Excel will launch and display a dialog box explaining that the document is protected and requires a password.
3. Enter the password in the space provided, and hit 'OK.' This will open the document and allow you to view and edit the contents.

How to Do an Excel Chart With Multiple Legends

1. Open your Microsoft Excel spreadsheet that already has your data entered into the cells.
2. Click on cell A1, followed by 'Control' and 'A.' This is a shortcut that highlights your complete set of data.
3. Click 'Insert' on your toolbar, followed by 'Bar,' 'Pie' or another form of chart that you like. The chart will appear in your spreadsheet.
4. Click on the chart. Click 'Layout' and then the Legend icon. Click the button that specifies the your legend's orientation such as 'Show Legend at Right.' The legend will appear in your chart.
5. Click on the legend table. Click 'Control' and 'C,' followed by 'Control' and 'V.' A second legend will appear in your graph.

How to Make a Graph From CSV

1. Launch Excel 2010, click 'File' in the top menu and select 'Open.' Click on the file types drop-down menu at the bottom right of the File Open dialog box. You will need to select either 'All files (*.*)' or 'Text files (*.prn, *.txt, *.csv)' because by default, Excel filters your file choices to show only its native file types. Navigate to your file and click 'Open.'
2. Find an empty spot in your spreadsheet and click a cell. The exact location is not important, as you are selecting a place to put your chart while you work on it.
3. Click 'Insert' in the top menu and select any one of the chart types you feel will best represent your data. For instance, click 'Line' then select '2D line.' A blank chart area appears in your spreadsheet.
4. Right click in the blank chart area and click 'Select data.' When the 'Select Data Source' window appears, use you mouse to click and drag over the cells that contain the data you want in the graph. When you release the mouse, you will see the chart automatically updated with your data.
5. Modify the data as required for your purposes. You can modify the text around the X and Y axes by double-clicking on them and modifying the text in the dialog boxes.
6. Save your file when your modifications are complete. If you wish to use this chart in a presentation, or include it in a document, you can copy it to that document: clicking on your chart and press 'Ctrl' plus the letter 'C' to copy it, then switch to the other document and press 'Ctrl' plus the letter 'V' to paste it into the document. You will need to make sure that the other application is capable of displaying the graph or chart, but Microsoft's other major applications, such as Microsoft Word and PowerPoint, can do this.

How to Make a Watermark in Excel

1. Open the Excel workbook in which you want to create a watermark.
2. Click View on the Excel toolbar, or use the keyboard shortcut 'Alt' plus 'V.'
3. Click the View menu and select Header and Footer.
4. Choose Custom Header in the Page Setup dialog box that appears. Click the 'Center Section' so your cursor is active in the corresponding field.
5. Click the 'Insert Picture' button on the right; this button has a picture of mountains and the sun. Navigate to the picture that you wish to use as a watermark and double-click the graphic to insert it.
6. Click the 'Format Picture' button on the far right of the Header dialog box. Resize the picture to fit your page using the options on the Size tab.
7. Click the Picture tab. Under Image Control in the drop-down menu next to 'Color' choose Washout. This will give your graphic a watermark appearance.
8. Click 'OK' on the Format Picture dialog, and again on the Header and Page Setup dialogs.

Subscribe to: Posts (Atom)