September 2012 - Excel

Browse » Home » Archives for September 2012

Friday, September 28, 2012

How to Create Multiple Hyperlinks in Excel

1. Open Microsoft Excel. Click the 'File' tab. Click 'Open.' Browse to the spreadsheet to add hyperlinks into and double-click its file name. The spreadsheet opens in the Excel workspace.
2. Scroll to or click the cell with the first set of data to hyperlink. Right-click the cell and click 'Hyperlink' to open the 'Insert Hyperlink' window.
3. Type the Web address to launch when clicking the hyperlink into the 'Address' box at the bottom of the 'Insert Hyperlink' window. Click the 'OK' button. The cell text becomes blue with an underline signifying a hyperlink.
4. Scroll to or click the next cell to hyperlink and repeat the 'Insert Hyperlink' process.
5. Create multiple hyperlinks going to the same destination by highlighting a cell, right-clicking and clicking 'Insert Hyperlink.' After typing in the website in the 'Address' box, highlight the typed address, right-click it and click 'Copy.' Click the 'OK' button to close the 'Insert Hyperlink' window and add the hyperlink.
6. Click the next set of data to hyperlink with the same address. Repeat the 'Insert Hyperlink' process, but instead of typing into the 'Address' box, right-click the box and click 'Paste' to paste in the address. Click 'OK.'

How to Use Microsoft Office Home Student for Small Business

1. Use Word 2010 to process most of the documents that your small business requires you to handle. Microsoft Word is the standard application for writing and storing digital documents. The application allows you to read, write, edit and create files with the DOC and DOCX extensions. Word 2010 allows you to create easy-to-read tables and paste images into the text of the document. The 2010 version features an improved Navigation Panel, providing a visual outline of the documents so you can browse and sort what you need quickly.
2. Use Excel 2010 to analyze, organize, share and manage the data of your small business. You can use the many mathematical, financial, statistical and other formulas to collect and process such data as sales statistics and information about your clients and suppliers. You also can calculate the profit margin of your business and use other ratios, including the return on your investment and what proportion of your cash flow goes to repaying bank loans.
3. Use PowerPoint 2010 to create professional-looking presentations. PowerPoint can put your arguments in a concise and easy-to-understand visual image for your perspective customers.
4. Organize your information in OneNote 2010. Create a virtual notebook that can save text, images, video and audio. You can use OneNote to schedule important meetings and write down any thoughts or ideas without worrying that they will be lost if you write them down on a piece of paper.

Thursday, September 27, 2012

How to Create a Histogram in PowerPoint

1. Arrange your data into columns on paper to organize yourself before creating a histogram in PowerPoint. For example, list input data in one column and the range in the second column. Identify the frequency of the data occurring within each of the ranges of the second column.
2. Open PowerPoint to create a new presentation so you can build your chart based on the information you've organized on paper. Right-click on the slide. Choose the 'Layout' option. Click the 'Title and Content' layout. Click the 'Click to add title' text box, and type 'Histogram.'
3. Click the 'Insert Chart' button. Choose the first 'Clustered Column' option. Click 'OK.' Because you will show only two columns of information, drag the right corner of the range so it ends at Column B. Press the 'Ctrl' and 'A' keys and then press the 'Delete' key to delete the default data and add your own. For example, in column A, enter the following data:Range60802040
4. In column B, enter the following data:Frequency3321
5. Click the 'Microsoft Office' button in the Excel window, and click the 'Close' option to return to the PowerPoint window.
6. Add a text box to list the percentage legend to the right of your column chart. From the 'Insert' menu, click the 'Text Box' information and enter this text:100%80%60%40%20%0%
7. Add a cumulative percentage point line. For example, under the 'Insert' menu, choose the 'Shapes' option and select a line. Draw a line from each column to the next column to show the increasing cumulative percentage for the bin range. Insert a text box to label the percentage complete. For example, on the 60 value column, 30% of the data is represented. On the 80 value column, 60% of the data is represented. On the 20 value column, 80% of the data is represented. On the 40 value column, 100% of the data is represented.

How to Extend Margins in Excel

1. Open the Microsoft Excel 2010 spreadsheet that you want to alter.
2. Click the 'Page Layout' tab at the top of the screen. Locate the 'Page Setup' area on the ribbon and click the 'Margins' button found there. Click 'Custom Margins' from the menu that appears.
3. Click the up and down arrows next to each of the four major margins to adjust the margin for that side of the paper. You can also adjust the margins for the header and footer, making them larger or making them so small that they disappear.
4. Click the 'Print Preview' button once you have extended your margins, to ensure that the final product has the look that you want. If the margins are not correct, click the 'Page Layout' tab again and choose 'Custom Margins' again to go back to the margins screen.

How to Convert Excel Field to Boolean Type

1. Click 'File.'
2. Click 'Options.' This opens the 'Excel Options' window.
3. Click 'Customize ribbon.'
4. Check the box next to 'Developer' in the pane on the right. Click 'OK.'
5. Click 'Developer' from the menu bar.
6. Click 'Insert' from the 'Controls' tab.
7. Click the icon for a check box from the 'form options' section.
8. Click on the cell to which you want the add the boolean value.

How to Set Up a Default Email Program With MS Excel

Internet Explorer
1. Open Internet Explorer.
2. Click 'Tools' then 'Internet Options.'
3. Click the tab labeled 'Programs.'
4. Select your e-mail program under 'E-mail.'
5. Click 'OK.'
Control Pannel
6. Click the 'Start' menu.
7. Select 'Control Panel.'
8. Select 'Network and Internet.'
9. Click 'Internet Options.' Click the tab labeled 'Programs.'
10. Select your e-mail program under 'E-mail.' Click 'OK.'

Wednesday, September 26, 2012

How to Create a Summary Workbook Excel 2003

1. Type a description of the information that you want to display as a summary on Sheet 1. If you have an income statement on Sheet 2, for example, you can type 'Net Income' in cell A1.
2. Go to the cell you want to show on your summary page. In the above example, you would go to the cell that has net income on Sheet 2.
3. Click 'Copy.'
4. Go back to the summary sheet and click where you want to display the link. In the example, go to cell A2.
5. Click 'Paste' then 'Paste Special.' Choose 'Paste Link.'
6. Repeat these steps for all of the information that you want to include on your summary page.

How to Subtract Dates in Microsoft Excel 2003

1. Start Microsoft Excel 2003 and open the workbook containing the dates you would like to subtract.
2. Click an empty cell where you would like the results to appear.
3. Identify the cells containing the start date (for example, A2) and end date (for example, B2). Type without quotes: '=B2-A2' and substitute your actual cell references for the examples used here. Press Enter.

How to Calculate Weeks Days Between Two Given Dates in Excel 2003

1. Left-click on the cell you want the result to formulate within.
2. Type in the formula in the cell you selected in step 1 to calculate the amount of days. For example, if the two dates are A2 ('6/9/2004') and A3 ('8/21/2004'), you would subtract A3 from A2 to find out the days and weeks in between. The formula would look like this: ' =(A3 - A2) '
3. Press the 'Enter' key on your keyboard to formulate the result. For example, if you enter the formula using the data in step 2, you would end up with 72 days between the two dates.
4. Select another cell where you wish to input your formula to convert the number of days to weeks.
5. Enter the formula into the newly selected cell beginning with the ' = ' sign and clicking on the cell you selected in step 1. Add a '/' sign followed by the number '7.' This will take the result you found in step 1 and divide it by 7 to find the amount of weeks between the two dates.
6. Press the 'Enter' key on your keyboard to find the number of weeks between the two dates.

How to Change From Sum to Count in a Pivot Table

1. Open the PivotTable report in your Microsoft Excel application. Click any cell in the data area so that it’s highlighted.
2. Click the “Field Settings” option on the PivotTable toolbar. The “Sum” option will be selected by default in the PivotTable Field dialog box.
3. Click the “Count” option in the “Summarize by” box. The data will now be summarized by 'count' instead of 'sum.'
4. Click the “Options” button to change any other field settings, such as adding a custom calculation.
5. Click the “OK” button in the PivotTable Field dialog box to save all of your changes.

How to Use Error Bars in Excel

Excel 2007
1. Click the chart you want you want to add an error bar to.
2. Click the 'Chart Tools > Layout' tab.
3. Click 'Error Bars' in the Analysis section of the Layout tab.
4. Click the type of error bars you want to add. You may choose error bars with Standard Error, Percentages, or Standard Deviation.
5. Select the display type for the error bars in the Format Error Bars dialog.
6. Set the error amount in the appropriate text box.
7. Click 'OK' to exit the Format Error Bars dialog.
Excel 2003
8. Double click the chart series you want to add an error bar to.
9. Choose the 'Y Error Bars' or 'X Error Bars' rab in the Format Data Series dialog, depending on which axis you want to add the bars to.
10. Choose the 'Display' type you want by clicking the appropriate icon, and set the Error Amount values in the appropriate text box.
11. Click 'OK' to the close the Format Data Series dialog.

Tuesday, September 25, 2012

How to Add a Section Line to the Menu for VBA With Excel

1. Start Excel 2003.
2. Open the Visual Basic Editor. Select 'Tools,' then 'Macro,' then 'Visual Basic Editor' from the menu.
3. Insert a blank module for your code. Select 'Insert,' then 'Module' from the menu.
4. Start a new subprocedure. Type the following:Sub CustomMenu()Excel will automatically add an 'End Sub' statement.
5. Define variables. Type the following between the 'Sub' and 'End Sub' statements:Dim MenuObject As CommandBarPopupDim MenuItem As Object
6. Delete any existing instances of your custom menu. This prevents duplicates if you run the code more than once. Type the following:On Error Resume NextApplication.CommandBars(1).Controls('My Macro').DeleteOn Error GoTo 0
7. Create a new menu option called 'My Macro.' Use a parameter value of 10 in the 'Set' statement to place it before 'Help,' the 10th item on the Excel menu. Type the following:Set MenuObject = Application.CommandBars(1).Controls.Add(Type:=msoControlPopup, Before:=10, temporary:=True)MenuObject.Caption = 'My Macro'
8. Add two items to the menu, with a section line between them. Create the section line by setting the 'BeginGroup' property in the second item to 'True.'Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)MenuItem.OnAction = 'MacroName'MenuItem.Caption = 'Run'Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)MenuItem.OnAction = 'About'MenuItem.Caption = 'About Macro'MenuItem.BeginGroup = True
9. Run the code. Select 'Run,' then 'Run Sub/UserForm' from the menu. When you return to the spreadsheet, you will see 'My Macro' on the menu, prior to 'Help.' Click to view the two menu options, separated by a section line.

How to Insert Multiple Rows in Excel 2007

1. Open the appropriate Microsoft Excel 2007 worksheet on your computer.
2. Use your mouse to highlight the number of rows below the area where you want to insert new rows. For example, highlight five rows if you want to insert five more rows above that group.
3. Click on the 'Home' tab and then click on the arrow next to the 'Insert' field in the 'Cells' group.
4. Click on the 'Insert Sheet Rows' option and then multiple rows will be inserted above your highlighted cells.
5. Click on the 'Microsoft Office' button from the top toolbar ribbon menu and then click on the 'Save' option so that your new changes are saved.

Monday, September 24, 2012

How to Adjust Baseline Shift in MS Word

1. Launch Microsoft Word 2007. Click on the 'Home' tab.
2. Click the 'Show Font Dialog' box located in the 'Font' section of the 'Home' tab.
3. Click the 'Character Spacing' tab.
4. Select the type of baseline shift you would like to use from the 'Position' drop-down box. 'Normal' keeps all text aligned and straight. 'Raised' is ideal for creating superscripts, and 'lowered' is ideal for creating subscripts.
5. Click 'OK' to save your changes and exit the window.

How to Display Currency in Words for Microsoft Excel

1. Launch Microsoft Excel and open a spreadsheet.
2. Create a new module by pressing Alt F11 and selecting “Module” under the “Insert” menu. A new window will appear.
3. Open a web browser and go to ozgrid.com/VBA/CurrencyToWords.htm.
4. Highlight and copy, by pressing Ctrl C, the blue text that begins with “Function ConvertCurrency...” and ends with “End Function” near the very bottom of the web page.
5. Paste the copied text into the module window by pressing Ctrl V. This will create a new function that you can now use in Excel.
6. Go to the cell that you wish to display currency in words.
7. Type in “=ConvertCurrencyToEnglish()” with the numerical value of the currency between the brackets. The currency will be converted to words. For instance, “=ConvertCurrencyToEnglish(12.30)” will display as “Twelve Dollars And Thirty Cents.”

How to Remove Blank Rows in Excel

Deleting A Small Number of Rows
1. Save your spreadsheet.
2. Hold the 'Control' key.
3. Left-click the number of each blank row you want to delete to highlight each one. Make sure you continue holding 'Control' down as you select the rows.
4. Right-click on one of the highlighted rows and select 'Delete.'
Deleting Many Blank Rows
5. Save your spreadsheet.
6. Press the F5 key, then click 'Special.'
7. Select 'Blanks' and press 'OK.'
8. Click 'Home,' then 'Delete' under the cell options. (Or click 'Edit' and 'Delete' for older versions of Excel) Note that using this method will remove all blank cells and move the data up, so if there are different blank fields in some rows it may cause data to become disorganized. Always save your spreadsheet before performing operations so you can revert back to old versions if necessary.
Removing Blanks by Sorting
9. Save your spreadsheet.
10. Highlight all the cells that you want to sort.
11. Click 'Data,' then 'Sort.'
12. Under 'Sort By,' select 'Column A,' then click 'OK.' This will sort the data by the values in column A, leaving any rows without data values for column A at the bottom of the sorted list, essentially deleting them.

Sunday, September 23, 2012

How to Compare Files in Microsoft Excel

Compare by Size
1. Save the file in comma separated values format. Use similar file names, such as file1.csv and file2.csv. This method only works for worksheets containing data, not formulas.
2. Go to the start menu and select 'Accessories,' select 'Command Prompt.'
3. Change the directory of the command prompt to the directory where the files are stored. For example, it will start with 'C:\Users\Joe.' Type 'cd Documents' to work within the Documents folder; the directory prompt will then read 'C:\Users\Joe\Documents.'
4. Type 'comp file1.csv file2.csv' using your filenames from the folder where the files are stored. If they are the same size, command prompt will assume they are the same and tell you that 'Files compare OK.' If they are not, command prompt will advise 'Files are different sizes.'
Compare Lists With a Macro
5. Open the Visual Basic Editor and select the primary file from the left menu as VBAProject[file1.xlsm].' Select 'Insert' and 'Module' from the menu at the top.
6. Create a macro in visual basic that will run through an if/then loop comparing the text of each cell in the primary file with a secondary file. Give it a logical name, such as 'CompareFiles().' The code must output a list of data that is different in a new sheet in the primary file. Close the Visual Basic Editor and save the file.
7. Click on the 'View' tab at the top of the main navigation; Select 'Macros' and then 'View Macros.' Select the new macro from the list and click 'Run.'

How to Delete Excel Rows Based on Date

1. Open up your workbook in Excel. If you are using Excel 2007, you need to display the 'Developer' tab if it isn't already showing. To do this, click on the 'Office' button and then 'Excel Options.' Click on 'Show Developer Tab' and click 'OK.'
2. Create a new macro. In Excel 2007, click on the Developer tab and then 'Macro.' In earlier versions, click on 'Tools' in the toolbar and then 'Macro.' Name the macro, 'DeleteRowbyDate' and then click 'Create.' The Visual Basic editor appears.
3. Copy and paste the following code into the Visual Basic editor between the lines, Sub DeleteRowbyDate() and End Sub:Dim x As Long
For x = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
Debug.Print Cells(x, 'B').Value
If CDate(Cells(x, 'B'))
Cells(i, 'B').EntireRow.delete
End If
Next iOn the line where it says, ' If CDate(Cells(x, 'B'))
4. Close the Visual Basic editor. It automatically saves to the macro. When you want to run the macro and delete the rows, go back to 'Macros,', select the 'DeleteRowsbyDate' macro and click 'Run.'

Saturday, September 22, 2012

How to Edit VBA in Excel 2007

1. Start Microsoft Excel 2007, select the 'Developer' tab and click 'Record Macro.' Leave everything as default and click 'OK.' Select 'A1' and type 'I'm going to edit VBA in Excel 2007' and press 'Enter.'
2. Click 'Stop Recording' in the 'Code' pane. Click 'Macros,' select 'Macro1' and click 'Step Into.' The Microsoft Visual Basic window will open and you will see the macro code that you recorded.
3. Click the 'Run' menu and select 'Reset' to stop the macro execution. Under 'Range ('A2').Select' type the following to add another row of text to 'A3':Range('A3').SelectActiveCell.FormulaR1C1 = 'I edited VBA by adding another line of text'Range('A4').Select
4. Run 'Macro1' by pressing 'F5.'

How to Upgrade for Excel 2003

Upgrade to Office 2007
1. Insert your Microsoft Office 2007 installation disk, or double-click the 'Setup.exe' file in the installation pack you downloaded from an online retailer.
2. Enter your 25-digit Product Key when prompted and click 'Continue' when a green check mark appears.
3. Check the box to accept the license agreement and click 'Continue.'
4. Select 'Upgrade' from the installation options.
5. Wait as the program installs. It may take 15 minutes or more to finish installing.
6. Restart the computer when prompted.
Upgrade to Office 2010
7. Insert your Office 2010 installation disk and click 'Customize.'
8. Select 'Remove all previous versions' to remove Office 2003 and click 'Upgrade.' This will uninstall Office 2003, but it will not not delete any of your Office documents.
9. Click 'Upgrade' again to begin installation, which may take 15 minutes or more to finish.
10. Click 'Close' to exit the installer.

How to Make a Double Bar Graph

1. Open an Excel spreadsheet.
2. Enter the data that needs to be charted, making sure to save frequently to insure no work is lost.
3. Click 'Insert' on the tool bar.
4. Go down to 'Chart' and right click the mouse. This opens the Excel Chart Wizard. This allows you to customize the type of chart you want to use to best show the data.
5. Choose 'Column' and select the double bar graph pictured among the choices.
6. Click 'Next' at the bottom of the list of options located at the bottom of the chart wizard.
7. Select the cells that hold the information you are charting. Place your mouse at the top of the first cell. Right click your mouse and do not let go. Drag to the bottom corner of the last cell of data and then let go of the mouse. This will create a box around the data you need to chart.
8. Keep the Excel Chart Wizard open and you will notice that the cell formula is auto entered for you.
9. Select 'Next' from the bottom of the Excel Chart Wizard for the next two options. This tells the chart wizard what to do with the specified data.
10. Choose 'Titles' from the top of the Excel Chart Wizard to enter a title for your graph, likewise choose 'Data Labels' and 'Data Table' to place text in the graph for easier understanding.
11. Select 'Next' to determine if you want the graph to show in the Excel Spreadsheet or if you want to show it as a separate page.
12. Click on 'Finish' to close the Excel Chart Wizard. Your graph will appear as you have selected.
13. Right click on 'Legend' on the graph itself to make changes. Choose 'Format Legend' from the choices and you are then able to change colors, add text, change fonts or add borders.

Friday, September 21, 2012

How to Create a Checklist in Excel

1. Open the Excel software. Locate the A-1 cell at the top left of the spreadsheet table.
2. Double-click the A-1 cell. This will enable you to enter text. You can enter a header for your list, like 'Things To Do.'
3. Enter each task or duty into the subsequent cells in the A column. Double-click each cell to enter text.
4. Enter a header in the B-1 column such as 'Complete/Incomplete' or 'Status.' As you finish each task, you can make a note of it in the corresponding B-column cell.

How to Convert HTML Documents to Excel Spreadsheet Files

The Open Command
1. Open your Web browser, then navigate to a Web page you'd like to convert to an Excel spreadsheet.
2. Click your browser's 'File' menu and select 'Save as' or 'Save Page as.' Type a name for the file, then click 'Save.' Jot down the folder into which you saved the file. This file is the stored Web page you'll convert to an Excel spreadsheet.
3. Open Excel, then click the Office button.
4. Select the 'Open' command, then navigate to the Web page you saved to your hard drive in step 2.
5. Double click the file to open it. Excel will import both text and graphics into the current spreadsheet.
6. Click the Office button, then click the 'Save as' command.
7. Type a file name for the workbook and click any item in the 'Type' drop-down list that says 'Excel.'
8. Click 'Save' to save the converted HTML document as an Excel file type.
Use the Data Tab
9. Open Excel, then click the 'Data' tab.
10. Click the 'From Web' button. Excel displays a small browser window. You'll notice small black arrows with yellow backgrounds displayed here and there on the page. These indicate portions of the Web page you can convert to Excel.
11. Click the top most arrow, then click the 'Import' button. Excel will display a dialog box asking you to choose the location for the imported data. Click 'OK' to accept the default option, which is the current spreadsheet. Excel will load the text of the Web page portion you selected.
12. Click the disk icon in the Quick Access toolbar at the top left of the application window.
13. Type a file name for the converted Web page, then click one of the 'Excel' file types in the 'Type' drop-down box.

How Do I Freeze Frames in Excel 2007?

1. Place your cursor in the first cell from the top and left that you don’t want frozen.
2. Click the “View” tab.
3. Click the “Freeze Panes” drop-down.
4. Click “Freeze Panes.'

How to Make an S

1. Enter your data in the Excel spreadsheet. For example, you may want to track the growth of sales by month. So you would label the month in the first column and then enter the sales numbers in the second column.
2. Click and drag on the spreadsheet to select the data you want included in your chart. Also include any relevant labels, such as the months and the column headings.
3. Click the 'Insert' tab and then click the arrow under the 'Scatter' button in the 'Charts' section. Select either 'Scatter with Smooth Lines' or 'Scatter with Smooth Lines and Marks.' The chart will be displayed on your spreadsheet.

Thursday, September 20, 2012

How to Create Target Lines in Excel Charts

1. Locate the chart data on your Excel worksheet. If you aren't sure which data was used for the chart, click once on the chart: the chart data will be bordered by a blue line on the worksheet.
2. Click on the first blank cell to the right of your chart data. If you don't have a blank column directly to the right of your chart data, create one: right click, then click 'Insert' and then click 'Entire column.'
3. Type your target. For example, if your graph consists of sales figures and your target sales goal is 20 units, type '20.'
4. Click the fill handle for the cell. The cell looks like a little black square at the bottom right of the cell. Drag it down the column to the last row in your chart data. This action copies the input for the cell (in this example, 20) to all cells in the column. This is the dummy series for your chart.
5. Highlight the entire chart data area, including the dummy series. To highlight the data, left click at the top left of the cells and then drag the cursor to the bottom right.
6. Click the 'Insert' tab. Select the chart type you want displayed. For example, click 'Column' and then click '2D.' Excel will insert the chart into the spreadsheet.
7. Click the dummy series on the chart, then click 'Change Chart Type.' Change the chart type to 'Line.'

Wednesday, September 19, 2012

How to Change the Footer in Excel 2007

1. Open the Excel document containing the footer that you would like to change. On the tab bar at the top of the page, locate and click the 'Insert' tab.
2. Click on the 'Header and Footer' button located in the text group on the right-hand side. Clicking this button displays the design tab. The header and footer tools located within this tab will allow you to create a new header or footer as well as edit the current header or footer.
3. Click the 'Go to Footer' button within the navigation group. Insert text by simply typing the text you want to appear as your footer within the footer text box. If you are changing an existing footer, delete the text or graphic that is already there, and replace it with whatever text or graphic you want to appear.
4. Select the desired cell where you want the footer to appear or where the current footer exists. Click and type text or choose pre-formatted data.
5. Click on the appropriate button within the header and footer elements group, such as page number, current date or file name, to use any of the pre-formatted data.
6. Navigate back to the body of your document and continue by clicking the 'Home' tab.

How to Autosave Microsoft Office Documents Every 60 Seconds

1. Open your Microsoft (Word, Excel, PPoint, Access Etc.) document.
2.
In Office 2007, e.g. Word, Click on the Office Button in the top left hand corner of your document.
3. Click on the Word Options button at the bottom of the Drop-Down Menu.
4.
In the left pane, Click on the Save option and under the 'Save Documents' heading ensure that the 'Save AutoRecovery Information every' Checkbox has a tick in it.
5.
In the dropdown list that is directly to the right, type 1 or use the arrow keys to decrease the number from 10 to 1. Click Ok to close the dialog box and you are done.
6. In Office XP-2003, (Word, Excel, PPoint, Access Etc.), Go to 'Tools' choose 'Options' from the menu and in the dialog box, Click on the 'Save' tab and you will see the same feature similar to Office 2007, make your changes and click ok to accept the changes and you are done.
Next time office crashes in the middle of your work AutoRecovery would have saved the last 60 seconds of your edited document(s) for you.

How to Make a Sorted List on Excel

1. Start Excel 2010 using the shortcut in the Microsoft Office folder, and open the spreadsheet containing data that you want to sort.
2. Click the button between the 'A' and '1' in the upper-left corner of the window to highlight all of the data in the spreadsheet. Alternatively, click and drag the mouse pointer across the letters at the top of the window to highlight only certain columns. If you do this, the data in the columns not highlighted remains in its current location after sorting.
3. Click the 'Home' tab at the top of the window.
4. Click the 'Sort Filter' button in the 'Editing' section of the top toolbar, and then click an option such as 'Sort A to Z,' 'Sort Smallest to Largest' or 'Sort Oldest to Newest' to sort the data automatically. The options available are context-sensitive and vary depending on the leftmost column selected. To sort by multiple columns, select 'Custom Sort' and continue.
5. Click the drop-down menu next to 'Sort by,' and select the first column that you want to use for sorting.
6. Click the drop-down menu under 'Order,' and select the sort order for the selected column such as 'Oldest to Newest.'
7. Click the 'Add Level' button to add an additional column to the sorting criteria, and then repeat steps 5 and 6. If multiple rows have matching information in the first selected column, Excel sorts them according to the second column selected. You can add as many columns to the sorting criteria as you like.
8. Click 'OK' to sort the data according to the criteria you selected.

How to Remove Old Items in a PivotTable Drop

1. Right-click on any cell in the PivotTable.
2. Click 'PivotTable Options' from the drop-down menu. A small window will appear.
3. Select the 'Data' tab at the top of the new window.
4. Click on the drop-down arrow under the 'Retain items deleted from the data source' areas. Choose 'None' from the list and click 'OK' at the bottom of the window.
5. Select the 'Options' tab at the top of the screen, directly underneath 'PivotTable Tools.'
6. Click 'Refresh' in the 'Data' area of the toolbar. Your PivotTable should briefly flash and all deleted entries will now be removed from drop-down boxes in the PivotTables.

Tuesday, September 18, 2012

How to Calculate Softball Standings With Excel

1. Launch Microsoft Excel 2010 to open a new spreadsheet. Click on cell 'A1' and type 'Team Name' into the cell. Click on cell 'A2' and enter the name of the first team. Continue to enter names down the first column until you have entered the name of every team.
2. Select cell 'B1' and type 'Wins' into this cell. Select cell 'B2' and enter the number of wins for the team listed in cell 'A2.' Repeat this process for every team.
3. Click on the cell at the top of the next empty column and type 'Winning Percentage' into this cell. Click on the next cell in this column and enter the following formula:=(B2 (D2*0.5))/SUM(B2:D2)If ties are worth a third of a win in your league, enter '0.3333' instead of '0.5.' If your league does not track ties, use the following formula instead:=B2/SUM(B2:B3)Press 'Enter' to complete your formula.
4. Move your mouse over the small fill-handle in the lower right corner of the cell that contains your formula. Click and drag the mouse down to the last row that contains team information and release the mouse button. Excel will automatically copy the formula down the entire column.
5. Click on cell 'A1.' Click the 'Insert' tab at the top of the screen, then click the 'Table' button in the Tables area of the ribbon. Excel will automatically select every cell where you entered data, so just click 'OK' in the Create Table window to continue.
6. Click the drop-down arrow next to the 'Winning Percentage' cell and choose 'Sort Largest to Smallest' from the menu. Your softball standings will now be sorted by winning percentage, placing the best teams at the top of the standings.

How to Disable One Macro in Excel 2003 Using the VBA

1. Open the Microsoft Excel 2003 software and access the worksheet containing the macro you need to disable. Navigate to the 'Tools' option at the top of the screen and click the 'Macro' option.
2. Select the 'Macros' entry and click 'This Workbook' to see a list of macros associated with the spreadsheet. Scroll through the list and click the name of the macro you need to disable.
3. Click 'Delete' to remove the macro from the worksheet. Close the 'Macros' window and return to the 'Tools' menu if you want to disable a macro by manually deleting the code.
4. Choose the 'Macro' option and select the 'Visual Basic Editor' entry. Click the name of the worksheet at the left end of the window that contains the macro.
5. Scroll down to the start of the macro, which begins with 'Sub MacroName().' Highlight the opening segment of the macro and scroll down to the ending 'End Sub' command. Press the 'Backspace' key to delete the macro.
6. Click 'File' and choose 'Save' to save the changes to the VBA code.

How to Use Excel to Solve a Polynomial

1. Input the coefficients into separate cells in row 4 in Excel, starting at cell 'B4.' for example, if you have a cubic equation x^3 - 2x^2 - 3x, then your coefficients are 1, -2, 6 and -3. Click on cell 'B4' and type '1', then click on cell 'C4' and type '-2,' then click on cell 'D4' and type '6,' then click on cell 'E4' and type '-3.'
2. Click on cell 'B6' and type a guess for the value of X. If you have no idea of what the value might be, just type '0.'
3. Click on cell 'G4' and then type the full equation into the cell. In this example, the equation would be 'x^3 - 2x^2 - 3x.'
4. Click on the 'Data' tab, then click on 'What-If Analysis,' in the Data Tools group and then click 'Goal Seek.'
5. Click on the 'Set cell' text box and type 'G4.'
6. Click on the 'To value' text box and then type '0.'
7. Click on the 'By changing cell' text box and then type '$B$6.'
8. Click on 'OK.' Excel will solve the polynomial and return the result in cell B6 (the initial guess box).

How to Consolidate Data in Microsoft Excel 2003

Consolidate Data by Position
1. Open the Excel worksheets that you want to consolidate. Check to make sure that there are no blank spaces within each range.
2. Open a new worksheet. Copy and paste the range from each worksheet that you wish to consolidate that you wish to consolidate into the new worksheet.
3. Select each range one at a time. Click “Named Cells” located on the toolbar. Click “Formulas.” Click “Name a Range.” A pop-up box will open. Type the range name into the “Name” field. Click “Save.” Create a name for each range.
4. Open the master worksheet where you plan to consolidate the data.
5. Click onto the cell in the upper left hand corner of the area where you want to place the consolidated data in the master worksheet, making sure to choose an area with enough room on all sides of the worksheet for all of the data you will be consolidating or you will lose data in the consolidation process.
6. Click “Data Tools” on the master worksheet. Click “Data.” Click “Consolidate.” Click on the function you wish to use in the “Function” box which opens.
7. Click “Browse” if the worksheets you want to locate are in a different workbook. Choose the location from the drop-down menu and click “Okay.”
8. Type into the range dialog box the name that you gave the range of data that you wish to consolidate. Click “Add.” Do this step for all of the ranges that you wish to consolidate.
9. Click to check the “Create links to source data” box if you want the master worksheet to be updated automatically when you add data to the worksheets in a different workbook. This function only works if you are consolidating data from different workbooks. To update the data manually, uncheck this box. Do not fill out the boxes in the “Use Labels” categories.
10. Click “Save.”
Consolidate Data by Category
11. Open the Excel worksheets that you want to consolidate. Check to make sure that there are no blank spaces within each range and that each range is formatted with label titles located in the top row, left column, or both.
12. Open a new worksheet. Copy and paste the range from each worksheet that you wish to consolidate that you wish to consolidate into the new worksheet.
13. Select each range one at a time. Click “Named Cells” located on the toolbar. Click “Formulas.” Click “Name a Range.” A pop-up box will open. Type the range name into the “Name” field. Click “Save.” Create a name for each range.
14. Open the master worksheet where you plan to consolidate the data.
15. Click onto the cell in the upper left hand corner of the area where you want to place the consolidated data in the master worksheet, making sure to choose an area with enough room on all sides of the worksheet for all of the data you will be consolidating or you will lose data in the consolidation process.
16. Click “Data Tools” on the master worksheet. Click “Data.” Click “Consolidate.” Click on the function you wish to use in the “Function” box which opens.
17. Click “Browse” if the worksheets you want to locate are in a different workbook. Choose the location from the drop-down menu and click “Okay.”
18. Type into the range dialog box the name that you gave the range of data that you wish to consolidate. Click “Add.” Do this step for all of the ranges that you wish to consolidate.
19. Click to check the “Create links to source data” box if you want the master worksheet to be updated automatically when you add data to the worksheets in a different workbook. This function only works if you are consolidating data from different workbooks. To update the data manually, uncheck this box.
20. Click the boxes in the “Use labels in” section. Click “Top row” if the category titles are located in the row of your data ranges, click “Left column “if the category titles are located in the left column, or choose both boxes if titles are located in both areas.
21. Click “Save.”
Consolidate Data by Formula
22. Open the Excel worksheets that you want to consolidate. Locate the reference codes of the cells you will be consolidating on the master worksheet.
23. Open the master worksheet where you plan to consolidate the data.
24. Type in the titles of the column and/or rows onto the master worksheet. You can also cut and paste the titles from the original worksheets.
25. Click onto the cell in the upper left hand corner of the area where you want to place the consolidated data in the master worksheet, making sure to choose an area with enough room on all sides of the worksheet for all of the data you will be consolidating or you will lose data in the consolidation process.
26. Type in the formula, which contains the reference codes of the cells you are consolidating into the cell you chose. To consolidate formulas located in different areas on separate worksheets, type an equal sign followed by the word SUM. Next, within parentheses, type the name of the worksheet followed by an exclamation point. Next, type the location of the formula. Type all of the formulas you want to consolidate separating each formula with a comma. For example, = SUM (Inventory!J8, Stock!A3, Tax!B7). If the formulas are located in the same areas of different worksheets, create the using the worksheet names and the area. For example, SUM (Inventory:Stock:Tax!B7)
27. Click “Save.”

How to Create a Quiz in Excel

1. Open a blank spreadsheet in Excel. Use column A for the question, column B for the answer and column C to grade the answers.
2. Type your first question into cell A1. For the purposes of this demonstration the question is, 'If you mix red and blue together, what is the resulting color?'
3. Scroll or arrow over to cell C1 and type the following function: =IF(B1='', '', IF(B1='answer', 'Right', 'Wrong')). Replace 'answer' with the correct answer. Using the example, 'If you mix red and blue together, what is the resulting color?' you would replace 'answer' with 'purple' and as long as the person taking the quiz typed 'purple' in the corresponding B1 cell, the answer would show 'Right'. If someone gives a wrong answer, 'Wrong' shows up and if they put no answer, cell C1 remains blank.
4. Think of questions and answers to fill the quiz. Remember column A is for inputting questions, column B should be left blank with enough space to type answers and column C is where you input the formula.
5. Continue inputting questions into column A by typing your questions into subsequent cells.
6. Skip over to the C column and begin typing in the formula into the cells in the C column. Remember to change 'B1' from the original formula to the B cell number that corresponds to the question. For a question in cell A2 you will want the formula to read =IF(B2='', '', IF(B2='answer', 'Right', 'Wrong')), again replacing 'answer' with the correct answer.
7. Decide if you want to quiz to score the participant's answers. If so, simply input the following into the corresponding D cells: =IF(C1='Right', 1, 0). Remember to update the cell numbers to correspond with each question and answer.
8. Type the following formula at the bottom of the quiz if you have decided to have Excel calculate the score: =sum(D1:D10). This is the formula for a 10 question quiz--adjust accordingly. You can either write in the cell numbers, or after you have typed the opening parenthesis you can use your mouse pointer to click and drag to select the range of cells you want added together. If you chose to select the cells this way they should be highlighted with a blue box.
9. Hide the answers before you send the quiz out. Select column C and choose 'Format> Cells>Protection' and check the 'Hidden' selection box. Select 'OK' to close the window and apply the setting. Now you can send the quiz out knowing the answers are hidden.

Monday, September 17, 2012

How to Link an Excel Spreadsheet to a Visio Diagram

1. Open the Microsoft Excel spreadsheet that contains the data you would like to link to the Visio diagram.
2. Go to the 'Insert' tab and click on the 'Object' button.
3. Go to the 'Create from File' tab. Click on the 'Browse' button and navigate to the Visio file that you would like to link. Check off the 'Link to file' box. Click 'OK.'

How to Copy and Paste in Excel

1. Open an existing Excel file to edit or create a new one. When creating a new file, select 'New' from the 'File' menu. To edit an existing file, select 'Open' from the 'File' menu.
2. Enter data that you want to copy into a cell or identify a cell that you wish to copy that already has data in it. Select the text by highlighting the exact text you want to copy within the textbox at the top of the screen or by clicking the individual cell the text is in.
3. Copy the selected text or cell by selecting 'Copy' from the 'Edit' menu at the top of the screen. You can also use a shortcut key by holding the 'Ctrl' and 'C' keys on your keyboard to copy the data. A third option would be to right-click your mouse and select 'Copy' from the short-cut menu that pops up.
4. Select the cell you wish to paste the copied text into by clicking on it. Paste the data into the selected cell by choosing 'Paste' from the 'Edit' menu. To use a shortcut key you can hold the 'Ctrl' and 'V' keys down to paste the data more quickly or right-click the mouse again and select 'Paste' from the short-cut menu that pops up.
5. Continue editing, copying and pasting data until all the data is entered in your spreadsheet. Save your work by selecting 'Save' or 'Save As' from the 'File' menu.

Sunday, September 16, 2012

How to Set a Fixed Height Width in Excel

1. Open the Microsoft Excel 2010 file with which you want to work.
2. Click and hold the mouse button on the row number or column letter that you want to adjust to a fixed height and width. Then drag the mouse to select the adjacent rows or columns. You can then press 'Ctrl' and click to select nonadjacent columns or rows. If you want to change the height and width of the cells for the entire spreadsheet, just click the box to the left of 'A' and just above '1.'
3. Click the 'Home' tab at the top of the screen. Locate the Cells area of the Ribbon and click on the 'Format' button found there.
4. Choose 'Row Height' from the menu, and a small window appears. Type the value for your desired row height and click 'OK.'
5. Click the 'Format' button again and choose 'Column Width' from the menu. Enter your desired column width into the small window that appears and click 'OK.' Your column width and row height are now set.

How to Hide Indefinite Errors in Excel

1. Open the Excel 2010 worksheet where you want to hide the error values. Click the top-left cell in the range of cells you want to work with, then hold 'Shift' and select the bottom-right cell. If you want to work with the entire worksheet, you can just click the box above the '1' on the end of the top row.
2. Select the 'Home' tab and click the 'Conditional Formatting' button at the top of the screen. Select 'New Rule' from the list that appears.
3. Click 'Format only cells the contain' from the list at the top of the New Formatting Rule window. Click on the drop-down box under 'Format only cells with' and choose 'Errors' from the list of options.
4. Click the 'Format' button and then select the 'Font' tab in the new window that appears. Select the drop-down box under the 'Color' heading and click the white box located at the top-left of the drop-down menu. Click 'OK' twice and the errors will disappear.
5. Click the 'File' tab at the top of the screen. Then click 'Options' on the left side of the screen.
6. Click 'Formulas' on the left side of the Options window. Locate the 'Error Checking' heading and click the box next to 'Enable background error checking' to remove the check mark. Click 'OK' and the small arrows will disappear from the cells with errors.

How to Search a Cell Range for a Matching Value in Excel VBA

1. Open the worksheet in which you want to find a match. Press the 'Alt' and 'F11' keys together. This opens the Visual Basic Editor (VBE).
2. Click 'Insert' and then click 'Module.' This inserts a blank module window.
3. Copy and paste the following code into the blank window:Sub Find_First()Dim FindString As StringDim Rng As RangeFindString = InputBox('Enter a Search value')If Trim(FindString)
'' ThenWith Sheets('Sheet1').Range('A1:Z256')Set Rng = .Find(What:=FindString, _After:=.Cells(.Cells.Count), _LookIn:=xlValues, _LookAt:=xlWhole, _SearchOrder:=xlByRows, _SearchDirection:=xlNext, _MatchCase:=False)If Not Rng Is Nothing ThenApplication.Goto Rng, TrueElseMsgBox 'Nothing found'End IfEnd WithEnd IfEnd Sub
4. Change the 'Range' in the code to the cell range in which you want to look for a match. For example, if you want to look in cells C1:C1000, type 'C1:C1000' in place of 'A1:Z256.'
5. Press 'F5,' then type the string of text or data you want to find into the text box. Excel will find the match and highlight the cell.

Saturday, September 15, 2012

How to Use a Calendar in MS Excel 2010 Cells

1. Open Microsoft Excel 2010. A new spreadsheet will open. Click on the Office Button, followed by Open. Select your file by double-clicking on it. It will open up on your screen.
2. Click anywhere on the spreadsheet. Click 'Insert' on the toolbar, followed by 'Object.' Click 'Create from File', followed by 'Browse.' Select your calendar image file. Click 'Insert', followed by the 'OK' button. Your calendar image will show up on your spreadsheet.
3. Refer to your calendar as you enter your equations into your spreadsheet cells or simply input data, such as email addresses or names. Once you have finished referring to the calendar, simply delete it by clicking on it and pressing the 'Delete' button.

How to Use an Excel Equation

Basic Equation Entry
1. Launch Microsoft Excel 2010.
2. Activate the software over the Internet if prompted.
3. Click in any blank cell and type the number '15' into the cell and press 'Enter' to save the entry and move to another cell. Alternately press the 'Tab' or any arrow key or click the mouse in a new cell to save the entry and move to a new cell.
4. Type the number '5' into another blank cell and press 'Enter.'
5. Type the 'Equal' sign ('=') into a blank cell and click once on the cell with the number '15' in it. Click the 'Plus' sign (' ') and click once on the cell with the '5' in it. Press 'Enter' to save the formula and notice that the answer to '15 5' is displayed in the cell that contains the equation.
Using Built-In Functions
6. Launch Microsoft Excel 2010 and enter some sample data into a few of the cells in a blank workbook.
7. Click on the cell that will contain the answer to the desired equation.
8. Click the 'Formulas' tab on the toolbar.
9. Click 'Insert Function' from the 'Function Library' on the 'Formulas' tab.
10. Click the 'Select a Category' pull down menu to choose the general category of equation required.
11. Scroll down if necessary to find the desired function in the 'Select a Function' list. Click once on the function name and click 'OK' to bring up the formula dialog box.
12. Click once in the cell that contains the information required for the first argument listed in the 'Function Arguments' dialog box. Click the next box in the 'Function Arguments' dialog box to save the first argument and change the focus to the second argument in the equation.
13. Click once in the cell that contains the information required for the second argument listed in the 'Function Arguments' dialog box.
14. Click 'OK' if all of the necessary arguments have been entered into the 'Function Arguments' dialog box. Notice that the cell containing the formula will now display the results of the equation.

How to Customizing the Excel Status Bar

1. Right click anywhere on the Excel status bar to bring up the 'Customize Status Bar' window.
2. Click on the left side of the Customize window next to the items that you want to add or remove from the status bar. Any field that has a check mark next to it is active and will show up on the Excel Status bar. Remember that many of the items on the list, like caps lock or permissions, will only show up on the status bar if they are turned on, so placing a check next to them might not show any immediate results.
3. Click anywhere outside of the Customize window to finish customizing the status bar and return to your work. The status bar is not bound to the worksheet, so the changes you made to the status bar will remain even in a new project.

How to Shrink Columns in Microsoft Excel 2003

1. Select the column you want to shrink. Left-click on the column letter to activate and select that column.
2. Shrink the column using the column width menu. Right-click on the activated column and select “Column Width.” Enter the desired column width in pixels and then click 'OK' to change.
3. Select the column you wish to shrink manually. Left-click on the column letter that you wish to shrink manually to activate that column.
4. Shrink the column manually. Hang the cursor over the edge of the column borders at the top of the column until an arrow appears. Then left-click and hold as you drag the column to shrink it.

How to Divide an Excel Spreadsheet

Freeze Panes
1. Double-click on the Excel file that you want to work with in order to open it up in the Microsoft Excel program.
2. Click on the cell that is below and to the right of the point where you want to freeze the panes. If you only want to freeze the top row or leftmost column, you don't need to select any cell.
3. Select the 'View' tab on the toolbar at the top of the Excel window. Find the 'Window' group within that toolbar. If your Excel window is very small, you may have to click on the 'Window' button to view all the buttons in the group.
4. Click on 'Freeze Panes' to open up a drop-down menu.
5. Select 'Freeze Panes' from the drop-down menu to cause everything positioned up and to the right of your selected cell to freeze in place. You can also choose 'Freeze Top Row' or 'Freeze First Column.'
Split Panes
6. Open the Excel file that you want to work on.
7. Select a cell anywhere in the middle of your worksheet. The panes will be created above and to the left of this cell, but the panes are easy to adjust so it doesn't have to be perfect.
8. Click on the 'View' tab and find the 'Window' group in the toolbar. Click the 'Split' button to divide the worksheet into four panes.
9. Double-click on either the horizontal or the vertical separator to remove it, if you only wanted to divide your worksheet into two panes instead of four.

How to Write a VBA Script

1. Open the Microsoft Office application that you want to write a script for. For example, open Microsoft Excel, Word or PowerPoint.
2. Click on the 'Developer' tab and then click on 'Visual Basic.' The Visual Basic Editor (VBE) screen will open. Alternatively, you can press the 'Alt' and 'F11' keys to open the VBE.
3. Click on the 'Insert' tab and then click on 'Module' to open a blank window. This is where you will type your script.
4. Type your script into the open window. You should start by typing 'Function [function name]()' if your script is a function or 'Sub [subroutine]()' if your script is a subroutine. Type the body of your script, then end with either 'End Function' or 'End Sub.' Use a function if you want to return something (like a value) or a subroutine if your script performs a task without returning anything (like formatting a document). Microsoft's website offers an extensive help section for writing VBA code.
5. Press 'F5' to run the script.

How to Use Excel Countif

1. Select the desired cell on the spreadsheet to display the calculated formula.
2. Type '=countif(' and select the range of cells to be evaluated by clicking the first cell, holding down the mouse button and dragging the cursor to the last cell before releasing the mouse button.
3. Type ',' and then identify the criteria for counting each cell's content as 'TRUE' for one point, making sure to enclose characters between quotation marks. For example, type 'purple' within quotation marks to count those cells containing the word; type '>9' within quotation marks to count the number of cells containing a number above nine; type A2 to count those cells containing information matching the contents in cell A2.
4. Type ')' and then press the 'Enter' key to complete the formula.

How to Create an Option Group in MS Excel

1. Display the Developer tab if it is not already visible. To turn this tab on, click the Microsoft Office Button (a round button with the mutli-colored Office logo) and choose 'Excel Options'. In the section called 'Top Options for Working with Excel', find the 'Popular' category. Place a check next to 'Show Developer Tab' in the Ribbon checkbox and click 'OK'.
2. Access the Developer tab and enter the 'Controls' section, then click the 'Insert' menu.
3. Select the 'Form Controls' option inside the 'Insert' menu, then click on the 'Option' button.
4. Click the cell on the spreadsheet that you want to contain the option button.
5. Launch the 'Properties' setting from the 'Controls' group in the 'Developer' tab.
6. Configure the settings for the new option button using the fields in the 'Properties' area. The 'Value' option allows a default state for the button that remains in effect until the user changes it. An option button is either on or off, so the 'Value' field accepts these two states as 'Checked' or 'Unchecked'.Each option button in a group carries a numerical value, with the first button equaling the value 1. The 'Cell Link' field copies the value of the option button as numerical data stored in any cell. Identify the cell using standard Excel cell referencing. For example, A1 is the upper left cell in a worksheet.
7. Add a checkbox to the option group in the same manner as the option button, if desired. In the 'Form Controls' option inside the 'Developer' tab's 'Insert' menu, click on the 'Checkbox' button.
8. Add more option buttons or checkboxes until the option group is complete.

How to Insert a Zip File Into Excel

1. Open the Excel file that you want to insert a Zip file into.
2. Select the worksheet you want to add the Zip file to and click the 'Insert' tab located in the Excel ribbon.
3. Click 'Object' from the 'Text' section of the ribbon. The 'Object' window will open.
4. Select 'Create from File' and then click 'Browse' so you can search for the Zip file you want to insert.
5. Click the Zip file you want to insert in your Excel workbook and then click 'Open.'
6. Click 'OK' to insert the Zip file you selected. The Zip file will show up as a folder icon in your Excel spreadsheet.

Friday, September 14, 2012

How to Create a Form in Excel 2003

1. Open Excel 2003 from your 'Start' menu.
2. Click the 'New' button that looks like a blank sheet of paper. Delete 'Sheet 2' and 'Sheet 3' by right clicking on the Tab, and then select 'Delete.'
3. Enter any questions and instructions you want in the corresponding cells.
4. Input any calculations you want for the form.
5. Change the font, color, highlighting and formatting to fit your preferences.
6. Save the form as a template. Print the form if you desire by clicking the 'Print' button.

How to Open Excel 2007 Files As Separate Windows

1. Open Microsoft Excel.
2. Click the 'Office' icon located in the upper-left corner of Microsoft Excel.
3. Click 'Excel Options.'
4. Select 'Advanced.'
5. Scroll down to 'Display.'
6. Check the box next to 'Show all windows in taskbar.'

How Do I Vertically Center in Excel?

Instructions
1. Type some text into a cell. Keep the cursor on this cell.
2. Select 'Format' from the top menu bar. The top menu bar includes all the commands.
3. Select 'Cells' from the drop-down menu.
4. Select the 'Alignment' tab from the 'Format Cells' menu that pops up. The alignment tab sits between the 'Number' and 'Font options
5. Select 'Vertical' from the 'Text Alignment' menu. Look for the 'vertical' option right below the 'horizontal' option.
6. Select the vertical alignment position you want for your text. For instance, when you click the 'vertical' option, a drop-down list will show four options: Top, Center, Bottom and Justify. If you want your text to rest along the top of the cell, click 'top. If you want the text to align in the very center of the cell and have a slight margin around all the edges, select 'center.' If you want the text to vertically along the bottom of the cell, select 'bottom.' If you want to distribute the text evenly throughout the cell, select 'justify.
7. Click the 'OK' button and save your preferences.

How to Alphabetize an Excel Spread Sheet

1. Highlight the data you want alphabetized (including the headers). If the employee payroll spreadsheet has columns for 'First Name,' 'Last Name,' 'Hours Worked' and 'Pay Drawn,' and you want to sort data in an alphabetic order by 'Last Name,' then you will have to highlight data under 'Last Name' as well as 'First Name,' 'Hours Worked' and 'Pay Drawn.' If you don't highlight data in other columns, i.e. 'First Name,' 'Hours Worked' and 'Pay Drawn,' then the information in the 'Last Name' would be alphabetized, while information in other columns would not be sorted, and you'd end up jumbling all the data.
2. Select 'Data' from main menu bar and click 'Sort.'
3. Select 'Header Row' in the small new window, if your top row is a header row and you do not want it alphabetized with the other rows. Select 'No Header Row' if otherwise.
4. Select the column header you want to sort data by in the 'Sort by' list of the same window. Here, we want to sort all information by 'Last Name.'
5. Select 'Ascending' if you want to sort this information in an ascending order.
6. Click 'OK' at the bottom of the window. The spreadsheet will be sorted alphabetically.

How Can I Sort Dates on an Excel Spreadsheet by Months Instead of Years?

1. Insert a blank column to the left of the column that has your date range. Go to the top menu and select 'Insert' and choose 'Columns.'
2. Click the first blank cell that is next to your date cell and type =TEXT( cell number,'MMDD'). For example, if your first date range is B4 and your date range is 03/25/1970, your formula would be =TEXT(B4, '0325').
3. Copy the cell with the formula and highlight the remaining blank cell and click 'Paste.' You will see just the months of your date range for each cell.
4. Sort your data by the new-month column. Go to the top menu; click 'Data' and then 'Sort.' The sort dialog box will pop up and continue to select the 'Expand the Selection'option to sort a series of columns or rows. Click 'OK' in the sort dialog box. Your data range is now sorted by month.

Thursday, September 13, 2012

How to Use Inches in Excel

1. Select the cells that you would like to format as inches. To do this, click and drag to highlight the cells. You can also click the letters at the top to highlight entire columns or the numbers on the side to highlight entire rows.
2. Right-click the selected cells to open the context-sensitive menu. If the cells contain numbers or are empty, one of the options will be 'Format.' Select 'Format.'
3. Select the 'Number' tab in the Format Cells screen to show settings for number cells. In most cases, this will be the default tab when the Format Cells screen opens.
4. Scroll down, and choose 'Custom' from the category list on the 'Number' tab. This will show options for setting up a custom format for numbers that is not provided by any other options.
5. Type '#0 [$ inches]' to format the selected cells as inches. If you would rather the cells show the abbreviation of inches, use '#0 [$ in].'

Wednesday, September 12, 2012

How to Calculate Macros in Excel

1. Click the 'File' menu's 'Options' button, then click the 'Formulas' link. Click the 'Manual' option, then 'OK' to close the 'Options' dialog box. This step turns off automatic calculation, which your macro will restore.
2. Type the following into cells A1 through C1 of the current worksheet: 5, 10, '=a1*a2.' Notice that the formula in C1 is incorrect, due to your selection of the manual option in step one. Your macro will restore automatic calculation.
3. Click the 'Developer' tab's 'Visual Basic' button to enter the Visual Basic programming environment. Paste the following program into the window that appears. This program uses the 'Range' object to enter values in two different worksheet cells, and a formula into another cell. The program has two statements that force the current worksheet to update its calculation. The first is 'Application.Calculation = xlCalculationAutomatic.' This statement sets the 'Calculation' property of the Excel application itself. The second is the one calling the 'Calculate' function of the 'ActiveSheet' object. This statement tells only the active sheet to calculate, not the entire workbook.Public Sub recalc()'Enter numbers in a cell'Enter a calulation that depends on those numbers'Force recalculationApplication.Calculation = xlCalculationAutomaticRange('a1') = 5Range('a2') = 10Range('a3').Formula = '=a1*a2'ActiveSheet.CalculateEnd Sub
4. Press 'F5' to run the program.
5. Click the 'Excel' icon on the Windows taskbar to return to Excel. Notice that the cell A3 has a function that requires updated calculation, and that the result of the calculation, the product of the worksheet's top two cells, is accurate.

Tuesday, September 11, 2012

How to Add Two Email Hyperlinks to One Cell in Excel

Adding Multiple Email Hyperlinks in Excel 2003
1. Launch the Microsoft Excel 2003 application.
2. Create a new spreadsheet or open an existing sheet in which you wish to add the email hyperlinks.
3. Configure the desired cell to a size that can easily accommodate two lines of text. This can be accomplished by clicking on the side of a row or column heading that you wish to expand and dragging it in the direction of expansion. For example, if you want to adjust cell B2, you would click on the right border of the column B heading and drag it to the right. The actual width will depend on the length of the email address. You would then click on the bottom border of the row 2 heading and drag it down. A height of 50 should easily accommodate two rows of text.
4. Click the 'View' option at the top of the screen.
5. Choose the 'Toolbars' option, then select the 'Drawing' option.
6. Select the text box from the bottom toolbar.
7. Click on your cell and configure the shape.
8. Type the email address into the text box.
9. Repeat steps 7 and 8 for the second text box, making sure the boxes are not overlapping. Your email hyperlinks will appear automatically.
Adding Multiple Email Hyperlinks in Excel 2007
10. Launch the Microsoft Excel 2007 application.
11. Create a new spreadsheet or open an existing sheet in which to add the email hyperlinks.
12. Configure the desired cell to a size that can easily accommodate two lines of text. This can be accomplished by clicking on the side of a row or column heading that you wish to expand and dragging it in the direction of expansion. For example, if you want to adjust cell B2, you would click on the right border of the column B heading and drag it to the right. The actual width will depend on the length of the email address. You would then click on the bottom border of the row 2 heading and drag it down. A height of 50 should easily accommodate two rows of text.
13. Select the 'Insert' tab, then click the 'text box' option. Configure the shapes of the boxes within your cell so that they are not overlapping.
14. Highlight each email address, then right-click it and choose 'Hyperlink.' Click the 'email address' option on the bottom-left and type in the email address.

How to Anchor Columns in a Spreadsheet

1. Open the Excel program and open the desired spreadsheet or enter data to create a new spreadsheet.
2. Select the column or columns that are to remain visible when scrolling. To select a column, click on the letter at the top of the column. The column will then be highlighted showing it has been selected.
3. After selecting a column when using Excel 2007, click on the view tab on the toolbar. Once the view tab is open, in the Windows group, click on the arrow that is located below the freeze panes. To anchor only one column, click on Freeze First Column. To anchor more than one row, click on Freeze Panes. To anchor columns in other versions of Excel, such as 2003, select the column and click on the Windows tab on the toolbar and click on Freeze Panes.
4. When the column or columns need to be unanchored, click Unfreeze Panes in the Window menu.

How to Make Text Appear as Typed in Excel

1. Open the Excel 2010 worksheet in which you want to enter your information.
2. Click on the top-left cell in the area where you want to alter the cell's format. Hold shift and then click the bottom-right cell in the area. If desired, you can also click the letters above the columns or the numbers to the left of the rows to select an entire column or row, respectively. Finally, to alter the format for the entire worksheet, click the button that sits to the left of the 'A' and above the '1' in the upper left corner of the sheet.
3. Click the 'Home' tab at the top of the screen. Locate the 'Number' area of the ribbon and click on the drop-down box found there.
4. Choose 'Text,' which will be at the bottom of the list that appears. Once you click on this option, your selected cells will display exactly what was typed into them.

Subscribe to: Posts (Atom)