Excel

Browse » Home » All posts

Tuesday, February 12, 2013

How to Add a Second Y

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

How to Disable Cell Drag in Excel

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

How to Move Data on an Excel 2007 Worksheet

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

Monday, February 11, 2013

How to Retrieve Values From Another Excel 2007 Workbook

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

How to Extract Pivot Table Data

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

How to Wrap Text in a Cell

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

Monday, January 28, 2013

How to Set 'Do Not Print Cell' in Excel

Hiding blocks of cells
1. Launch Microsoft Excel by pressing your 'Start' button and selecting the application from your list of available programs, or click on the icon if you have one saved on your desktop. Open an existing file that contains data rather than a blank worksheet. Create a backup file if necessary to protect yourself from inadvertently deleting data on the spreadsheet.
2. Highlight a row, column or specific cells in your spreadsheet. To select an entire row or column, point to the row or column headings (numbers for rows and letters for columns) until the regular mouse pointer turns into a right arrow (for row headings) or a down arrow (for column headings). Click the left mouse button to select the row or column. To select a block of cells, go to the first cell you want to highlight, click the left mouse button and drag the cursor until the desired block is selected. Then release the left mouse button.
3. Right-click your selected row, column or block of cells. Select 'Hide' from the pop-up menu that appears. If the pop-up menu does not appear, try holding the Ctrl key and right-clicking on the selection. Once you select the 'Hide' option, you will see that the selection is no longer visible on screen.
4. Print the worksheet. Select 'File' from the menu bar or press Ctrl P to print your worksheet. Verify that the selected row, column or block of text you designed to be hidden does not appear on the printed worksheet.
Hiding individual and non-adjacent cells
5. Left-click the cell you wish to hide. To select multiple non-adjacent cells, hold down the Ctrl key and left-click the desired cells.
6. Right-click your selection and select 'Format' from the pop-up menu. In the 'number' category, select 'Custom' and manually type in ';;;' without the quotation marks in the 'Type' box. Then click 'Okay.' You will notice that the cell(s) or block of cells are now hidden.
7. Print the worksheet. Select 'File' from the menu bar or press Ctrl P to print your worksheet. Verify that the selected cells you designed to be hidden do not appear on the printed worksheet.

How to Create Maps in Excel 2007

1. Create your data in an Excel spreadsheet. For example, if you want to map sales figures by zip code, you could put the sales data in one column and zip codes in another. In a third column, write the name of the city that corresponds with the zip code. For example:
$50,000_32206_Jacksonville, FL
$43,000_12801_Glen's Falls, NY
$19,000_80537_Loveland, CO
2. Select the data that you would like to include in the map. Highlight the data by holding down the left mouse key while you mouse over the area. When the data is selected, hit CTRL C to copy the data.
3. Open a blank map in MapPoint. Paste the data you copied from Excel into MapPoint by pressing CTRL V. This will open the Data Import Wizard.
4. Choose the type of map you would like to create and which columns you want to show. Click Finish.
5. Click Copy Map (located on the MapPoint Edit menu). Return to Excel and press CTRL V to paste the map into your spreadsheet.

How to Remove File Properties in Excel 2007

1. Launch Excel 2007. Click the 'Microsoft Office' menu button in the upper left corner of the window and select 'Open.' Locate the file with the properties you wish to remove and then click 'Open.'
2. Click the 'Microsoft Office' button again, click 'Prepare' and then select 'Properties.' Excel then displays your document's properties in its 'Document Information Panel.' Use your mouse to highlight any field that has text, such as the 'Author,' 'Title,' 'Subject' and 'Category' fields. Press your keyboard's 'Backspace' key to delete the information for that specific property.
3. Click the 'Document Properties' button above the property fields and select 'Advanced Properties' if you want to view additional file properties. Click the 'Summary' tab and use your mouse to highlight information in any of the listed fields. Press your 'Backspace' key to delete information in the field and then click 'OK' when you finish clearing the fields.
4. Click the 'Microsoft Office' button and select 'Save' to save your document with its removed file properties.

How to Format a Chart Fill in Excel

1. Start Microsoft Excel 2007 and open a workbook from your files that contains a chart to which you would like to format one of the fills. You can alternatively create a new chart from data in an existing workbook.
2. Select the item in the chart you would like to apply a fill to by clicking on it. When the item is selected, it will be surrounded by white round sizing handles.
3. Right-click on the selected item, and click on 'Format (Item Selected).' The name in parenthesis will be of the item you selected to fill.
4. Choose 'Fill' from the list of format categories on the left side of the 'Format' dialog box. The settings for the fill attribute will be displayed on the right side of the dialog box.
5. Click on a radio button to add a 'Solid fill,' 'Gradient fill' or 'Picture or texture fill,' depending on what type of fill you would like to add to the selected object. Once you have made your choice, more settings will be displayed at the bottom of the 'Format' dialog box.
6. Set the settings that you want to apply to your fill using the options that have appeared. The solid fill will ask you to specify the color and transparency of fill. The gradient fill will want to know the type of gradient, angle, stop positions and transparency. If you have chosen a picture or texture fill you will specify the type of texture or picture, stretch options and transparency.
7. Use the 'Close' button when you are finished setting your options. The 'Format' dialog box will close and you will return to your chart with your newly filled item.

How to Copy From One Worksheet Paste to Another Using VBA

1. Click the 'Developer' tab and then click 'Visual Basic' to open the Visual Basic Editor (VBE) with which you can create VBA.
2. Click 'Insert' and then click 'Module' to open a blank module window.
3. Cut and paste the following code into the module window:Sub Copy_Data()Application.ScreenUpdating = FalseWorksheets('Sheet1').Range('A1:A10').Value = Worksheets('Sheet2').Range('B1:B10').ValueApplication.ScreenUpdating = TrueEnd Sub
4. Tailor the code to your specific needs; as written, the code copies the data in cells A1 to A10 in worksheet 1 to cells B1 to B10 in worksheet 2. For example, if you wanted to copy data from cells C1 to C100 in sheet 4 to cells D1 to D100 in sheet 5, you would rewrite the third line of the code to read:Worksheets('Sheet4').Range('C1:C100').Value = Worksheets('Sheet5').Range('D1:D100').Value
5. Press 'F5' to run the routine.

Sunday, January 27, 2013

How to Copy an Excel Chart to Word

1. Start Microsoft Excel 2007 and open a workbook from your files that contains a chart you want to copy into Word 2007.
2. Click to select the chart. The chart should now be surrounded by a light blue border. Right-click on the chart and choose 'Copy' from the shortcut menu.
3. Start Microsoft Word 2007 and open an existing document that you would like to copy the Excel chart into or create a new, blank document for the chart.
4. Point and click to place the insertion point in the place in the document where you want to insert the Excel chart.
5. Choose the 'Home' tab, if necessary, and click the 'Paste' button from the 'Clipboard' group to display a drop-down list of options. Choose 'Paste Special' to open the 'Paste Special' dialog box.
6. Select 'Microsoft Office Excel Worksheet Object' from the 'As' list to paste the chart that will continue to be updated if you change the original chart in Excel. Choose 'Microsoft Office Graphic Object' from the 'As' list to paste the chart as it is without leaving it to be updated in Excel in the future.
7. Use the 'OK' button to close the 'Paste Special' dialog box and insert the copied Excel chart into Word.

How to Set Auto Correct Options in Microsoft Excel 2003

1. Open the auto correct options properties box. To open the auto correct options properties box you will need to scroll to the “Tools” tab on the command bar and select “AutoCorrect Options.”
2. Set the “Auto Correct” tab options. Under this tab you can check the boxes for the features that you would like Auto Correct to perform. You can check or uncheck the following features: correct to initial capitols, capitalize the first letter of sentences, capitalize names of days, correct accidental usage of CAPS keys, and replace text as you type. You can also add commonly mistyped words into the replace text fields, which will tell the program to replace x-word with the proper word.
3. Set the “Auto Format As You Write” tab options. You can check two boxes under this tab: internet and network paths with hyperlinks (which will automatically add links to URLs you type) and include new rows and columns in list (which will add rows and columns as you type).
4. Set the “Smart Tags” tab options. Under this tab you can set the smart tag options you want by checking the corresponding boxes: label date with smart tags (will enable this feature), date (will add proper date format to all dates entered), financial symbol (will add desired financial symbol to all currency entered) and person name (will look up names and email addresses from outlook and implement when recognized).
5. Implement the changes. To implement the changes click on the “Okay” button.

How to View VBA Code in Excel 2007

1. Open the workbook in Excel that you would like to view VBA code for. VBA code is workbook specific, meaning it is only attached to one workbook. In order to view the code for a specific workbook, you must have that workbook open.
2. Click on the 'Developer' tab, then click on 'View Code.'
3. Click on a module in the left sidebar. This will open the module and display the VBA in a box on the right side of your screen.

Saturday, January 26, 2013

How to Learn Microsoft Excel Online

1. Visit the official Microsoft Excel training site for Excel 2007 and Excel 2010 (see Reference section). The site offers a wide array of topics from creating your first workbook to creating macros to automate some of the features in the program. If you're new to Excel, there are a variety of basic topics plus step-by-step video tutorials. The best way to find what you are looking for help on is to use the search box on the website to type in a help topic, like 'Creating charts in Excel 2007.'
2. Click on the 'Free Training Tutorial' link in the Reference section. This free website walks you through all the Excel basics you need and includes step-by-step instructions and video tutorials. The site is broken up into easy-to-navigate subject areas like Format Cells or Edit Cells. The site offers you a wide array of basic subject videos--there are several videos on each basic topic area--and delves into more complicated formulas and functions with clear instructions.
3. Navigate to Baycon Group's Microsoft Excel 2007 tutorial (see Reference section). The on-screen tutorial is grouped by topic in an easy-to-follow list format. The tutorial starts with a description of every aspect of the Excel window. The tutorial was created with the novice computer user in mind. If you want the basics but don't know where to start, then this could be the tutorial for you.

Subscribe to: Posts (Atom)