Wednesday, October 16, 2013

How to Use the Transpose Function in Excel


1. Highlight the range of cells where you want the transposed data to appear. For example, if you want to transpose cells A1:A10 into a row in cells B1:K1, you would highlight B1:K1.
2. Go to the 'Formulas' tab and click on 'Insert Function.' Type 'Transpose' in the 'Search for a function' field and click on the 'Go' button. Select 'Transpose' from the 'Select a function' section and click 'OK.'
3. Highlight the range of cells you would like to transpose. In our example, we would highlight cells A1 through A10. The range will appear in the 'Array' field of the Function Arguments window.
4. Hit 'Ctrl,' 'Shift' and 'Enter' on your keyboard simultaneously. The transposed data will appear in the new location, which is B1 through K1 in the example.
Read more ►

Tuesday, October 15, 2013

How to Delete Shading From Alternate Rows in Excel 2003


1. Open the spreadsheet which contains the unwanted shading.
2. Click on the row number of one of the shaded rows. This will highlight the entire row. Ctrl-click on each row number where there is shading you want to delete.
3. Click on 'Format' on the menu then 'Format Cells.' Click on the 'Patterns' tab. Click the bar above the color palette that says, 'No Color.' This will delete the shading from alternate rows in Excel 2003.
Read more ►

How to Draw a Histogram in Windows Excel


Load the Analysis ToolPak
1. Open a new Excel spreadsheet. Click on the 'File' tab at the top of the screen, and then click 'Options' on the menu that appears.
2. Click 'Add-Ins' from the list on the left side of the Excel Options menu. Click the drop-down arrow next to the 'Manage' box at the bottom of the window. Choose 'Excel Add-Ins' from this drop-down menu, and click 'Go.'
3. Click the small box next to 'Analysis ToolPak' to place a check in the box. Click 'OK' to close this window.
Create the Histogram
4. Click on cell 'A1' and enter the title for the data that you want to use with the histogram. Then select cell 'A2' and enter your first value. Continue to enter values using the cells in column 'A' until you have added all of your data.
5. Click cell 'B1' and type in 'Bin Range.' This column of cells will contain the numbers that represent the number ranges that will make up the horizontal axis of your histogram. Remember that each bin number represents the top of the range of values that will be counted as part of that bin, so be sure to include the highest possible value for your data as part of the bin numbers. Enter the values starting at call 'B2,' and continue down the column until you have entered all your desired bin ranges.
6. Click the 'Data' tab at the top of the screen, and then click the 'Data Analysis' button on the far right side of the ribbon. Select 'Histogram' from the list of options and then click 'OK.' A small 'Histogram' window will appear.
7. Click in the blank next to 'Input Range' in the Histogram window. Click on cell 'A2' and hold down the mouse button. Drag your mouse down until you reach the last cell that holds data in column 'A'; then release the mouse button. Click on the blank next to 'Bin Range' and do the same thing as you did in column 'A,' but this time do it in column 'B' starting with cell 'B2.'
8. Select the radio button next to the output option that you desire. You can place the histogram somewhere on this worksheet, you can place it on a new worksheet within this workbook or you can place it in an entirely new workbook.
9. Place a check next to any of the bottom three options, if any apply to your desired histogram output. 'Pareto' will sort your data in descending order of frequency, 'Cumulative Percentage' will include another column on the histogram that has the cumulative percentages for your frequency data, and 'Chart Output' will display a chart of the histogram in addition to the standard table.
10. Click 'OK' to close the window and Excel will create your histogram.
Read more ►

Monday, October 14, 2013

How to Remove Multiple Duplicate Rows in Excel 2003


1. Open the spreadsheet and select the cells that may include duplicate rows. The values in the rows can be text or numbers.
2. Click the 'Data' menu, hover over the 'Filter' option and choose 'Advanced Filter' from the submenu.
3. Choose 'Copy to another location,' which leaves the original range of data intact, just in case.
4. Check the prepopulated 'List range' to confirm it's the range of data you want to filter of any duplicate values.
5. Choose, in the 'Copy to' field, the first cell to which you want to copy the filtered cells.
6. Place a tick mark in the box labeled 'Unique records only.' Click 'OK' to close the box and filter duplicates from the list of data.
7. Delete the original data when you're satisfied with the filtered results.
Read more ►

How to Make a 2nd Line on MS Office Excel


1. Click 'Start,' then 'All Programs.'
2. Click the 'Microsoft Office' folder, then click 'Microsoft Office Excel.' This will open a new workbook. To open an existing workbook, click the 'File' tab, then click 'Open' and browse to your file.
3. Click the cell into which you wish to add a second line.
4. Add the data for the first line, then press 'Alt Enter' to create a second line within the cell. You can continue to use 'Alt Enter' to add additional lines to your cell as well.
Read more ►

How to Add Data Labels to an Excel Chart


1. Start Microsoft Excel 2007 and open a workbook from your files that contains a chart to which you would like to add data labels.
2. Select the chart you want to add the data labels to by clicking it. The chart will be surrounded by a light blur border indicating it is selected.
3. Choose the 'Layout' tab at the top of the Excel 2007 screen to display the options in the 'Layout' ribbon. Locate the 'Labels' group in the 'Layout' ribbon.
4. Click the 'Data Labels' button in the 'Labels' group of the 'Layout' ribbon to display a drop-down list of options.
5. Opt for 'Center' from the 'Data Labels' drop-down list to display the data labels centered on the data points of your chart. 'Inside End' will display the data labels inside the end of the data points while 'Inside Base' will display the data labels inside the base of the data points.
6. Watch as the data labels are added to the selected Excel chart in the position you have chosen.
Read more ►

Sunday, October 13, 2013

How to Add a Custom Menu to an Excel Toolbar in Excel 2003


1. Log on to your computer and open your Excel worksheet. You should be able to locate the program by using your 'Start' button. When you cannot find it there, use your 'Search' or 'Find' function.
2. Go to 'Tools' in the toolbar. Hold the mouse over the arrow to expand the menu and select 'Customize'.
3. Add the functions that appeal to you by checking desired toolbars under the 'Toolbars' heading.
4. Click on the 'Commands' tab. Choose a category from the displayed list, then choose a command and drag it off the dialog box to the toolbar. Repeat as desired.
5. Delete a command by dragging the icon outside the toolbar and letting go of your left mouse button.
6. Determine whether you want a command by selecting the command under the 'Commands' heading, then choose 'Description'. This allows you to view a description of the command.
7. Close your 'Customize' window by clicking the 'X' in the left-hand corner of the window.
Read more ►

Saturday, October 12, 2013

How to Multiply in Excel


Single Cell Multiplication
1. Select the cell where the answer will be. You can put a self-contained multiplication problem in one cell anywhere in a workbook and use it just like a calculator.
2. Start with the equals sign in the cell where the answer goes. This is the first step to tell Excel that the cell contains a math function.
3. Use the asterisk sign as the multiplication symbol. Like standard math, insert the asterisk between numbers in the equation with no spaces and hit 'Enter.' The cell should contain the solution to the multiplication problem.
Multiply an Entire Range of Numbers by Another Number
4. Decide what number to multiply the entire data set by and select a random cell in the workbook. This is only a temporary cell entry and is erasable after you complete the function. Enter the multiplier in this cell.
5. Select the cell containing the multiplier, right click and choose 'Copy.'
6. Choose the entire row or column of varied data to multiply. Right click the highlighted row and use the 'Paste Special' function, which brings up a sub-menu window.
7. Click the 'Multiply' option under the 'Operation' section and hit 'OK.' The multiplier now changes all of the data visible in the chosen range within the Excel workbook.
Read more ►

How to Restore the Default Settings in Excel 2007


Tools Menu
1. Log on to your computer and open Microsoft Excel 2007. Open a blank spreadsheet.
2. Click on the 'Tools' menu. Choose 'Customize' from the list of options.
3. Right-click on the menu you want to restore to its default settings. Choose 'Reset' to restore the menu to its original default settings.
Help Menu
4. Open Excel 2007. Click on the 'Help' menu.
5. Choose the 'Detect and Repair' option. Check the 'Restore my shortcuts while repairing' check box.
6. Click 'Start' to begin the repair process. Repairing Excel 2007 will bring it back to its original configuration.
Read more ►

How to Use Visual Basic to Add a Worksheet in Excel 2007


1. Click the Windows 'Start' button and select 'All Programs.' Click 'Microsoft Office,' then click 'Microsoft Excel.' The programming software opens.
2. Click the 'Office' button and click 'Open.' Click your Excel file name to open it in your software. Click the 'Development' tab and 'View Code' to open the VBA coding file for the spreadsheet.
3. Type the following code in your VBA code file:Set newSheet = Worksheet.AddnewSheet.Name = 'New Sheet'newSheet.ActivateThis code creates a new sheet and names it 'New Sheet.' The code then makes it the active sheet.
4. Click the 'Save' button and close the VBA code file. Close the file and reopen it to see a new sheet created in the file.
Read more ►

Friday, October 11, 2013

How to Update Link Workbooks in Excel 2007


1. Close out all of the workbooks in the Microsoft Excel 2007 application. Click on the “Microsoft Office” button and then click on the “Open” option.
2. Select the destination workbook that contains all of the links and then click on the “Open” button.
3. Click on the “Update” button from the pop-up dialog box that appears on the screen to automatically update all of the links for the source workbooks.
4. Click on the “Don’t Update” option if you want to only select links to other workbooks, and then click on the “Edit” option from the top toolbar menu.
5. Click on the “Links” option and then select the object you want to update from the “Source” list. Click on the “Update Values” option.
Read more ►

How to Detect Repair Excel 2007


Microsoft Excel 2007
1. Open Microsoft Excel 2007.
2. Select the 'Microsoft Office' button. Click the 'Excel Options' button.
3. Select 'Resources' located on the left side. Click the 'Diagnose' button and then click 'Continue.'
4. Click the 'Start Diagnostics' button. Office tries to identify your software problem and attempts to repair any problems. Click 'Close' when the diagnostics are complete. If Microsoft Diagnostics cannot fix the problem, repair the software through the Control Panel.
Control Panel
5. Open the 'Start' menu. Choose 'Control Panel' from the menu. Click the link to 'Uninstall a Program.'
6. Select your Microsoft Office Suite from the list of programs. Click the 'Change' button.
7. Choose the radio button to 'Repair.' Click the 'Continue' button. Microsoft Office then repairs the software.
Read more ►

How to Calculate a Date in Excel


1. Populate today's date with the Today function. Type '=TODAY()' in a blank cell to return today's date. The Now function returns the date and the time stamp: '=NOW()'.
2. Calculate a future date by entering a date in a cell. Click on an empty cell and type '=cell reference number of days' where cell reference refers to the date that was entered and the number of days should be substituted for the actual number of days you want to calculate. For example, if we enter 5/22/2009 in cell A1 and want to know what the date will be in 21 days, the formula would be '=A1 21', which returns 6/12/2009.
3. Calculate a past date following the same procedure as in Step 2, but replace the plus sign with a minus. For example, if we enter 5/22/2009 in cell A1 and want to know what the date was 13 days ago, the formula would be '=A1-13', which returns 5/9/2009.
4. Calculate the number of days between two dates with the DAYS360 formula. Enter a start date and end date in two cells. Click on an empty cell and type '=DAYS360(start date,end date)', where the start and end dates would be substituted with cell references. In our example in Step 3, if we had the original date of 5/22/2009 in cell A1 and the calculated end date in B1, the formula would be =DAYS360(B1,A1), which returns 13 days.
Read more ►

How to Learn Basic Microsoft Excel


1. Learn Excel online through the Microsoft Office website, which includes Excel tutorials for all levels. Click the 'support' tab at the top of your page and select 'Excel' from the drop-down list. Select the Excel version you want to learn from the right and click 'Getting started.' Pick a topic -- such as 'Basic tasks in Excel 2010' -- that interests you. (see References)
2. Learn how to complete basic tasks in Excel through video tutorials at Microsoft Office. Click the video that interests you, such as 'Video: Getting Started with Excel 2010,' 'Video: Create a workbook,' or 'Video: Print a worksheet.'
3. Learn basic Excel through books and CDs. You can purchase low-cost books at Amazon.com, Ebay, Craigslist, discount outlets, yard sales or borrow one from your local library. Try books like 'Microsoft Excel 2007 Step by Step,' which also includes a companion CD, 'Excel 2010 in Easy Steps,' 'MS Excel 2007 Training,' or 'Mastering Excel Made Easy Training v. 2010 through 97.'
4. Take a class at your local community college to learn the basics of Microsoft Excel, such as learning about cells, spreadsheets, using formulas and inserting rows/columns.
Read more ►

How to Embed a PDF File in Excel


1. Select the cell in your Excel spreadsheet where you wish to embed a PDF. The PDF file will take up more than just one cell; select the cell you want the top left-hand corner of the file to appear. Click on the 'Insert Object' button in the Text group under the Insert tab. Highlight 'Adobe Acrobat Document' under Object type in the Object pop-up window .
2. Decide how you want your PDF to appear in your Excel document. If you want the PDF to appear as the first page of the PDF, simply click the OK button. If you want the PDF to appear as an icon, click the check mark labeled 'Display as Icon' and choose an icon using the 'Change Icon...' button or click 'OK' to accept the default Adobe Acrobat Document icon.
3. Navigate to the PDF you wish to embed in your Excel workbook in the Windows Explorer pop-up window. Double-click on the PDF file or click the 'Open' button after highlighting the PDF file to embed it in your Excel workbook.
4. Double-click on your embedded PDF or representative icon in Excel to access your embedded file.
Read more ►

Blogger news