Friday, March 15, 2013

How to Learn MS Excel Macros


Learn How to Record a Macro
1. Access the 'Macro' menu from the 'Tools' menu and click on 'Record New Macro.' This will open a dialog box in which you will type a name for the macro. When you click the 'OK' button, the macro will automatically begin to record and a small window with a 'Stop' button will appear.
2. Perform the set of functions that you would like to have in your Macro. If you would like to average a set of numbers, add a dollar sign and put a black border around the cell, carry out all of those tasks. The functions and commands you perform will record in the order in which you complete them.
3. Press the stop button to cease recording. You now have a macro that can average numbers, add a dollar sign and put a black border around the call with just one click.
Learn How to Run a Macro
4. Get to the 'Macro' option through the 'Tools' menu. When you click on 'Macro,' it will bring up a list of all available macros saved in your Excel.
5. Make a button for the macro to add to your toolbar. To create the macro button, go to the 'Tools' menu and select 'Customize' from the list. Go to the 'Commands' tab and click 'Macros' under the 'Categories' section. Drag the custom button of your choice to the toolbar. Go to 'Modify Selection' in the 'Customize' window and click on 'Assign Macro'. Choose the name of your macro from the list of macros that comes up and press the 'OK' button. The button will now stay on your toolbar and with one-click will perform all of the recorded functions.
6. Create your own keyboard shortcut. This will allow you to run the macro by pressing a combination of keys on the keyboard. To make a shortcut, click on 'Macro' under the 'Tools' menu. Choose your macro from the list and go to 'Options'. A window will come up with a place for a 'Shortcut key'. Pick a key to use as the shortcut for your macro, keeping in mind that some keys are already shortcut keys in Excel, such as Ctrl P for paste and Ctrl X for cut. After you have entered your key, click 'OK.' Your macro will now run automatically when you press the Ctrl key and the key that you assigned for your macro.
Read more ►

How to Insert a Comment Box in Microsoft Excel


Microsoft Excel 2007
1. Open your spreadsheet in Microsoft Excel.
2. Click on the cell where you want the comment to appear. Click on the 'Review' menu, and then 'New Comment' in the 'Comments' section. You can also right-click on the cell and click 'Insert Comment.' A text box will appear with your user name in it.
3. Type your comment. When you are finished, click anywhere in the spreadsheet outside of the text box. The comment box will disappear, but will reappear when you roll the mouse pointer over the cell.
4. Click on the cell, and then the 'Edit Comment' button to edit the comment or 'Delete Comment' to delete it. Use the 'Previous' and 'Next' buttons to jump from comment to comment, the 'Show/Hide Comment' button to display that cell’s comment without having to keep your mouse on it and the 'Show All Comments' button to show all of the comments on the sheet. Click it again to hide them.
Microsoft Excel 1997-2003
5. Open your spreadsheet in Microsoft Excel.
6. Click on the cell where you want the comment to appear. Click on the 'Insert' menu, and then 'Comment.' You can also right-click on the cell and click 'Insert Comment.' A text box will appear with your user name in it.
7. Type your comment. When you are finished, click anywhere in the spreadsheet outside of the text box. The comment box will disappear, but will reappear when you roll the mouse pointer over the cell.
8. Click on the cell and then the 'Insert' menu and 'Edit Comment' to edit the comment. You can also right-click on the cell and click 'Edit Comment' to edit it, or 'Delete Comment' to delete it or 'Show/Hide Comment' to display the comment without rolling your mouse over it. Click it again to hide the comment.
Read more ►

How to Embed Flash Game in Excel Spreadsheet


1. Download free flash game online or use one you already have
2. Open Excel and go to the top left option button. Choose excel options in the bottom right hand corner.
3. Click show developer tab in the ribbon.
4. Go to the developers tab and click insert. Choose insert shockwave object.
5. Create an area on the spreadsheet as large as the window you are working on.
6. Right click the area you just created and choose properties.
1. Make the embed movies option 'True'
2. Find the file you want on your computer and paste the path into the movie option. (exit properties)
7. Click design mode and you now have an active game.
8. Right click the game area on the screen and choose play.
9. Save normally and send it to yourself at work. Have fun.
10. Tell me if it works, I have a game or two at http://best-roulette-strategy.org/free-casino-games.html
Read more ►

How to Use the Freeze Pane Command to Create Static Headings in Excel 2003


1. Open your Excel worksheet once you have logged on to your computer. 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. Highlight the column to the right of the column to be frozen. Go to 'Window'. Select 'Freeze Panes'. A bold line will appear to the right of the frozen column indicating that those panes are frozen.
3. Freeze rows by highlighting the row below the row to be frozen. Repeat Step 3 as needed.
4. Freeze both by highlighting the cell to the right of the column and below the row and repeating Step 3.
5. Enjoy scrolling through your worksheet while your titles stay in place.
Read more ►

How to Use ActiveCell in Excel Using a VBA


1. Launch Microsoft Office Excel, click the 'Developer' tab, and click 'Visual Basic.'
2. Click the 'Insert' menu and click 'Module' to insert a new code module. Type the following code to start a new sub procedure:Private Sub usingActiveCell()
3. Copy and paste the following to activate 'Sheet1:'Worksheets('Sheet1').Activate
4. Copy and paste the following to add a value to 'A1,' 'A2,' and 'A3:'Range('A1').SelectActiveCell.Value = 3.5Range('A2').SelectActiveCell.Value = 10Range('A3').SelectActiveCell.Value = 20
5. Copy and paste the following to highlight the cells with data:With ActiveCellRange(Cells(.Row, .CurrentRegion.Column), Cells(.Row, .CurrentRegion.Columns.Count .CurrentRegion.Column - 1)).Interior.ColorIndex = 8Range(Cells(.CurrentRegion.Row, .Column), Cells(.CurrentRegion.Rows.Count .CurrentRegion.Row - 1, .Column)).Interior.ColorIndex = 8End WithApplication.ScreenUpdating = True
6. Copy and paste the following to display the values added through the 'Immediate Window:'Range('A1').SelectDebug.Print ActiveCell.ValueRange('A2').SelectDebug.Print ActiveCell.ValueRange('A3').SelectDebug.Print ActiveCell.Value
7. Copy and paste the following to end the sub procedure:End Sub
8. Press 'Ctrl' and 'G' to display the 'Immediate Window' and press 'F5' to run the procedure.
Read more ►

Thursday, March 14, 2013

How to Create a Two X Axis Chart in Excel


1. Create a chart that measures two data series against a set of variables using the Chart Wizard. Click 'Finish' and the chart will appear in your worksheet.
2. Click anywhere on the chart. In the top navigational menu, click the 'Format' tab. Click the arrow in the 'Chart Elements' box, and then select the data series you wish to be the secondary plot.
3. Click on 'Format Selection' in the 'Current Selection' section of the 'Format' tab.
4. Click on the 'Series Options' tab in the pop-up window, and then select 'Secondary Axis.' Click 'Close'. You will see a secondary vertical axis appear in the chart.
5. Click anywhere in the chart, then click the 'Layout' tab in the top navigational menu. Click on 'Axes'.
6. Click 'Secondary Horizontal Axis' and then choose your desired layout from the display options. Your secondary plot will change to a horizontal one.
Read more ►

How to Insert an Excel Drop Down Menu Box Into a Word Document


1. Select the Excel cell with the drop-down box by left-clicking it with your mouse.
2. Click 'Ctrl' and 'C' to copy the contents of the box to the clipboard.
3. In Microsoft Word, click on the 'Home' tab.
4. Click the down arrow beneath 'Paste' at the far left of the ribbon (the toolbar). Select 'Paste Special' from the options list.
5. Choose 'Microsoft Office Excel Worksheet Object.' This inserts the object into Word.
Read more ►

Wednesday, March 13, 2013

How to Insert a Calendar Into an Excel Spreadsheet


1. Download a calendar template from Microsoft Office Online. Search through the available templates and, after you find one you like, click the calendar link and select 'Download.'
2. Click 'Accept' to agree to the service agreement and click 'Save' to save the template to your computer.
3. Open the Excel workbook that you want to insert a calendar into. Click the 'Insert' button at the top of the page and select 'Object' from the menu that drops down.
4. Select 'Create from file' in the Object box that opens and click 'Browse' to search for the calendar template. When you find it, select the template and click 'Insert.' The calendar will then appear in your spreadsheet.
Read more ►

How to Convert XLXS to XLS in Microsoft


Convert With Excel
1. Launch Microsoft Excel 2007 or Excel 2010. Open the '.xlxs' file that you want to convert to '.xls'.
2. Click 'File' from the main program menu. Select 'Save As' from the drop-down menu.
3. Select 'Excel 97-2003 Workbook' from the 'Save As' context menu. This converts and saves the '.xlxs' file to an '.xls' file.
Convert With Office Compatibility Pack
4. Launch your Web browser. Navigate to the Microsoft Office Compatibility Pack download page.
5. Click the 'Download' link toward the top of the page. Select 'Save' from the on-screen download prompt to save the 'FileFormatConverters' installation file to your computer's hard drive. Wait for the download to finish.
6. Locate the file in your computer's 'Downloads' or 'My Downloads' folder. Double-click the file to install the Microsoft Office Compatibility Pack. If prompted, restart your computer during the installation process.
7. Open Microsoft Excel. Click 'File' followed by 'Open' to open the '.xlxs' Excel file that you want to edit or view. Excel now converts the workbook document into a format that is compatible with the older version of Excel.
Read more ►

How to Add Hours in Microsoft Excel


1. Create or open an Excel workbook that has hours that need to be totaled. The hours are formatted as h:mm.
2.
In the example at the left, the sum of the column results in an inaccurate number since Excel ignores hours that exceed 24. By reformatting the cell that holds the sum formula, we can correct this situation.
3. Right click in the cell that holds the sum formula, and then click on Format Cells.
4. On the Format Cells dialog box, click on the Number tab if it's not already displaying.
5.
Edit the custom setting, placing brackets around the 'h' as: [h]:mm .
6.
Click OK and you will see that the revised formatting has corrected the display value.
Read more ►

How to Hide Password Protect a Sheet Within Excel


1. Open the Excel worksheet you want to password protect and hide.
2. Place your cursor on the worksheet tab at the bottom of your spreadsheet.
3. Right click over the tab and choose 'Protect Sheet' from the pop-up menu. The “Protect Sheet” dialog box will open.
4. Add a check mark to 'Protect worksheet and contents of locked cells.' Insert the password you want to use into the 'Password to unprotect sheet' text box.
5. Choose the options you want to allow and press 'OK.' Re-enter your password in the “Confirm Password” dialog box and press “OK.”
6. Right click on the worksheet tab again and select 'Hide' from the options. The worksheet will be hidden from view.
Read more ►

How to Use Excel to Find Duplicates in a Long List


1. Open Microsoft Excel and the file that contains your list with duplicates.
2. Highlight the entire column (or columns) that contain the list(s) you want to examine for duplicates by clicking on the column letter(s) ('A,' 'B,' etc.) at the top of your data.
3. Click the 'Conditional Formatting' command in the 'Styles' group under the 'Home' tab, choose 'Highlight Cells Rules' and then choose 'Duplicate Values.'
4. Choose the color scheme you want the duplicate values to be highlighted with using the drop-down menu on the right of the 'Duplicate Values' dialog box that opens, or just click 'OK' to accept the default color scheme and highlight all duplicates.
Read more ►

Monday, March 11, 2013

How to Copy Excel Row Numbers Into Word


Copying a Picture of Excel
1. Click on the 'Page Layout' tab at the top of the screen. Look for the 'Sheet Options' area and place a check mark next to 'Print' under 'Headings.' This will make the row numbers and column letters show up in a printed document.
2. Select the top, left cell from the area that you want to copy. Navigate to the bottom, right cell. Hold 'Shift' and select this cell, highlighting the entire range of cells.
3. Click on the 'Home' tab at the top of the screen. Locate the 'Clipboard' area and select the drop-down arrow next to the 'Copy' button. Select 'Copy as Picture' from the menu, which will open up a small window.
4. Select 'As shown when printed' from the window. This will copy the cells as they would look if you were to print the selected area, including the row numbers and column letters.
5. Open the Word document to which you want to paste the Excel information. Place your cursor where you want to add the cells and press 'Ctrl' 'V' to paste the information.
Read more ►

How Do I Hyperlink to a Hidden Worksheet?


Insert the Hyperlink
1. Open the Microsoft Office document.
2. Click and drag the cursor over the text or image where you wish to insert the embedded hyperlink.
3. Right-click the selected text or image to show a list of commands, including 'Hyperlink.'
4. Click 'Hyperlink.' The 'Insert Hyperlink' dialog window opens. You can also click the 'Insert' tab on the command ribbon and click the 'Hyperlink' button in the 'Links' group.
5. Click the 'Existing File or Web Page' button in the 'Link to' list.
6. Click the 'Recent Files' button to the left of the text box.
7. Click the Excel file from the list.
8. Click 'OK.' The hyperlink appears on the document.
Unhide the Excel Worksheet
9. Point the cursor over the hyperlink. The ScreenTip will display instructions for following the link.
10. Press the 'Ctrl' key and click the hyperlink. The Excel workbook opens.
11. Right-click any sheet tab near the bottom of the screen. For example, 'Sheet 1' or a named worksheet tab. A list of commands appears.
12. Click 'Unhide.' A small dialog box opens with a list of hidden worksheets.
13. Click to select the worksheet in the 'Unhide sheet' text box.
14. Click 'OK' to unhide the worksheet. The dialog window closes. The Excel worksheet appears on the screen.
Read more ►

How to Learn the Basics of Microsoft Excel Free Without a Class


1. Open a new Excel 2010 spreadsheet window. Then open a Web browser window. Click and hold on the top of the Excel window and drag it to the left side of your Windows 7 desktop. The window will automatically adjust its size to fill exactly half the screen. Do the same thing with your browser on the right side of your desktop.
2. Direct your browser to the 'Get to know Excel 2010: Create Your First Spreadsheet' course at the Microsoft Office website (http://office.Microsoft.com/en-us/excel-help/get-to-know-excel-2010-create-your-first-spreadsheet-RZ101773335.aspx). Click the 'Start this course' button.
3. Watch the video that plays in your browser. During the video, click the 'Pause' button in the lower-left corner of the video player to stop the video and test out what you have learned in your spreadsheet window. After the six-minute introductory video is done, select another video from the list on the left side of the browser window. Each of the next six videos will give you information about one basic aspect of Excel 2010.
4. Select 'Practice' from the left side to bring up a practice spreadsheet that will walk you through a few practice routines. Once you feel comfortable, click 'Test yourself' on the left side to bring up a multiple-choice test regarding basic Excel commands. Finally, select 'Quick Reference Card' to open up a printable page that contains a number of basic Excel tasks and instructions to complete them.
5. Visit the Microsoft Excel 2010 Help and How-to page at the Microsoft Office website (http://office.Microsoft.com/en-us/excel-help/CL010253675.aspx?CTT=97). This page gives you a long list of selectable Excel commands and concepts. Select your desired entry to go to a help page surrounding that topic. You can also use the search box at the top of the screen to search the Microsoft Office website for a specific item. Be sure to include 'Excel 2010' in your search query, as the Office website holds articles for all Office products dating back to Office 2000.
6. Use the built-in help system in Microsoft Excel 2010 to address specific areas where you need assistance. Click the blue question mark at the top-right corner of the screen to open up the help window. Select the item from the list on the left, or enter in a search query into the box at the top of the window and press 'Enter,' then select the article you want to look at.
Read more ►

Blogger news