Friday, February 24, 2012

How to Hide Unhide Cells in Excel


Hide Cells
1. Select the cells you want to hide.
2. Click the 'Format' tab and then click 'Format Cells.'
3. Click the 'Number' tab.
4. Click 'Custom' from the Category List.
5. Type three semicolons ';;;' into the 'Type' text box and then click 'OK.' The cell data will be hidden.
Unhide Cells
6. Reopen the Format Cells dialog box.
7. Delete the semicolons in the Type box.
8. Click 'OK.'
Read more ►

How do I Convert Exel 2007 to 2003?


1. Open Excel 2007 and open an existing Excel 2007 spreadsheet. Select the 'Office' button then 'Open.' Browse your computer for files saved in the .xlsx format. Select the file and click 'Open.' The file with the new Excel 2007 naming format will open.
2. Select the 'Office' button then select 'Save As.' Select the format of 'Excel 97-2003.' If any conflicts appear, the Compatibility Checker will display any losses of fidelity and how many times this has occurred. Click 'Find' to see where these instances occur or 'Help' to see how to work around these issues.
3. Click 'Continue' once you are satisfied with the adjustment made from the Compatibility Checker. Excel 2007 will save this workbook in the older .xls format. Upon successfully saving this workbook, you have converted it to the 2003 format.
Read more ►

How to Remove Lines Around Each Cell in Excel 2003


1. Open Microsoft Excel 2003.
2. Click 'Tools' in the top menu bar and select 'Options.'
3. Click the 'View' tab in the Options window.
4. Uncheck 'Gridlines' in the Windows Options section.
5. Click 'OK' to apply your changes and remove the gridlines.
Read more ►

How to Print Multiple Excel Spreadsheets on One Page


Changing the Printer Settings
1. Select 'Print' from your Microsoft Excel's File menu to display the Print dialog box.
2. Choose the option for 'Entire Workbook' in the dialog box's 'Print What' area.
3. Click the 'Properties' button for Excel to display its 'Properties' dialog box for your printer, along with the selected 'Layout' tab.
4. Set the 'Pages Per Sheet' control depending on how many spreadsheets you want printed on the page. Click 'OK' to close the 'Properties' dialog box.
Changing the Page Setup
5. Select the 'Page Layout' tab in the File menu and locate the 'Page Setup' group.
6. Click on the small square with an arrow pointing out of the square at the bottom right-hand corner.
7. Look inside the 'Scaling' section in your 'Page Setup' box and choose 'Fit to:' By default, you should see the file set to one page wide and by one page tall.
8. Select how many pages tall or wide you want for your spreadsheet when printed.
9. Click on 'Print Preview' to check the up or scaled version of the spreadsheet when printed. Click on 'Print' if you are satisfied with the preview. Select 'OK' to close your Page Setup box.
Read more ►

How to Convert a Julian Date in Excel


1. Click on the cell were you would like your output data to start. For example, if you have a list of Julian dates in column A, you might want your regular dates to start on column B, so click on cell B1.
2. Enter the following formula into the cell. =('1/1/'(IF(LEFT(A2,2)*1
3. Copy the formula into as many cells as you want dates outputted. For example, if you have Julian dates in cells A1 through A50, copy the formula 50 times from cells B1 to B50.
4. Highlight the cells you created in Step 3.
5. Right-click on the highlighted cells and choose 'Format Cells.'
6. Click on 'Date' from the 'Number' tab. This tells Excel to convert the formula's output into regular calendar dates. Choose the date type (for example, 14 December, 2009 or 14 Dec) and click 'OK.'
Read more ►

Thursday, February 23, 2012

How to Code a VBA Excel 2007 Macro Consolidation


1. Go to the 'View' tab in the Microsoft Excel toolbar. Click on the 'Macros' button and select 'View Macros' Enter a new macro name for the master macro such as 'All' and click on the 'Create' button to launch the Visual Basic editor.
2. Go to the second line in between the macro name and end of the macro. In our example, it would appear as 'Sub All()' and 'End Sub'. Type 'Call' followed by each of the individual macro names you would like to run in preferential order. For example, we could have a macro named 'Links' that goes through a range of links and copies and pastes data from linked excel spreadsheets into a master sheet. Another macro called 'Format' sorts the data and adjusts the font type and size.Sub All()Call LinksCall FormatEnd Sub
3. Save the code and go back to the Excel template. Click on the 'View' tab and go to the 'Macros' button. Select the name of the master macro and click on 'Run.' This will run the individual macros that were created in order. In our example, it will run 'Links' first. Once it's completed, it will automatically run 'Format' next.
Read more ►

How to Create a Summary Chart in Excel


1. Open Microsoft Excel and create a new workbook by pressing 'CNTL-N.' Press 'CNTL-S' to save the workbook, and name it 'SummaryChartOfBookSalesByPublisher.xls' when prompted.
2. Create several rows of sample data representing the book sales of different publishers, across different types of books. Type or paste the following data into any worksheet in the workbook. Wherever you see a comma in this data, press the 'Tab' key, which will place each item in its own cell.Publisher,Genre,Distributor,Sales
Dolphin Pub. pub,romance,Amazon,$456
Dolphin Pub. pub,romance,Dynamic Dist. dist,$65
Dolphin Pub. pub,how-to,Ma and Mo dist.,$87
Dolphin Pub. pub,how-to,Keemer and Son dist.,$654
Sanford Pub. pub,romance,Ma and Mo dist.,$123
Sanford Pub. pub,romance,Keemer and Son dist.,$789
Sanford Pub. pub,how-to,Ma and Mo dist.,$432
Sanford Pub. pub,how-to,Aunt May dist.,$767
3. Select the data table, then click the 'Data' tab on Excel's main toolbar. Click the 'Sort' icon to display the dialog box with sorting options.
4. Choose the following parameters for the 'Sort' dialog box, to sort the publishing data by publisher names.Select 'Publisher' in the 'Sort by' drop-down list.
Select 'Values' in the 'Sort on' list.
Select 'A to Z' in the 'Order' list.Press 'OK' to exit the dialog box and sort the data by publisher.
5. Press the 'Data' tab on the Excel toolbar again, then click the 'Subtotal' icon to display the dialog box for configuring summary totals for the publisher data. Choose the following options in the 'Subtotal' dialog box:Select 'Publisher' for the drop-down list next to the text 'At each change in:'
Select 'Sum' for the 'Use function' drop-down list.
Check the 'Sales' checkbox for the 'Add subtotal to' list.
6. Check the following checkboxes at the bottom of the 'Subtotal' dialog box:'Replace current subtotals'
'Summary below data'Press 'OK' to create the summary rows for the publisher data. Notice that Excel has inserted boldface rows into the data, to add up the separate sales figures for each publisher.
7. Notice the small numbered buttons ('1,' '2,' '3') in the left pane of the main Excel window. Press the '2' button to hide the detail sales rows of the publisher data, and display only the summary sale data for the two publishers.
8. Select the publisher data, then press 'Insert' on Excel's toolbar to display an array of icons for graphical objects that can be inserted in a worksheet.
9. Click the 'Pie' icon in the 'Charts' group of icons, then select the first 3D-chart icon under the '3D Pie' heading. Drag the resulting blank chart carefully away from the publisher data, then drag the new blue frame so it surrounds just the two sales data rows for 'Dolphin' and 'Sanford' publishing. Don't include the rows with the 'Grand total' or the 'Sales' column header.
10. Notice the slices of the finished pie chart, which now display the summary sales data for both publishers.
Read more ►

How to Import Export Excel Text Into Access


Import in Microsoft Access
1. Open the Microsoft Access application on your computer. Click on the 'File' option for Access 2003 or the 'Microsoft Office' button for Access 2007.
2. Click on the 'Open' option and locate the Access database that you want to import and export data into. Select the database and then click on the 'Open' button.
3. Click on the 'File' or 'Microsoft Office' button again. Move your mouse over the 'Get External Data' option and then click on the 'Import' option. The Import dialog box will then appear on the screen.
4. Select the 'Microsoft Excel' option from the 'Files of type' box. Click on the arrow next to the 'Look in' field and locate the Excel file that contains the text you want to import.
5. Double click the Excel file. Click on the 'Import Data into a New Table' option and then allow time for the importing process to complete. The Excel text will then be imported into your Access database.
Export in Microsoft Access
6. Open the Microsoft Access program on your computer. Click on the 'File' option for Access 2003 or the 'Microsoft Office' button for Access 2007.
7. Click on the 'Open' option and locate the Access database that you want use for import or exporting Excel text. Click on the database file and then click on the 'Open' button.
8. Click on the 'File' or 'Microsoft Office' button again. Click on the 'Export' option and then the Export dialog box will then appear on the screen.
9. Click on the 'Microsoft Excel 97-2003' option from the 'Save as type' box. Click on the arrow next to the 'Save in' box and select the folder you want to save your exported data to.
10. Enter a name for your file into the 'File name' box and the click on the box next to the 'Save formatted' field so that it's selected. Click on the 'Export' button and the data will be exported.
Read more ►

How to Make a Timeline on Microsoft PowerPoint


1. Click on the 'Insert' tab, then click on the 'SmartArt' button in the 'Illustrations' area. Click on 'Process' and select one of the 'Timeline' layouts available. Add text to your timeline by clicking any place that says '[Text]' and typing or pasting in the text you want.
2. Add items to your timeline. Click on the timeline to select it, click on the 'Design' tab, click on 'SmartArt Tools' and click on 'Add Shape.' Delete an unwanted entry by clicking on it and pressing the 'Delete' key on your keyboard. Move an entry by clicking and dragging it to the location you desire.
3. Change the layout of your timeline by right-clicking on the timeline and selecting 'Change Layout.' Any of the SmartArt layouts are available. Your text will remain the same from layout to layout so you can adjust the design without changing the content.
4. Animate the presentation of your timeline to highlight individual events. Click on the 'Animations' tab, then click on 'Animate' and select 'One by One' so that each item on your timeline will be revealed separately during the slideshow. You will need to click your mouse for each event listed on your timeline if you choose this animation style.
Read more ►

How do I Add Information on Excel Worksheets to a Summary Sheet?


Excel 2003
1. Open Microsoft Excel and open the sheet you wish to summarize. Click on the 'Tools' menu and choose 'Scenario.'
2. Click on the 'Summary' option and choose 'Scenario Summary.' Go to the 'Result cells' tab.
3. Enter the cells you wish to summarize. You can simply place your cursor on the first cell and drag it to the last cell to create your range.
Excel 2007
4. Open Microsoft Excel and find the spreadsheet you want to summarize. Go to the 'Data' tab.
5. Go to the 'Data Tools' group and then the 'What-If Analysis-Scenario Manager.' Click the 'Summary' button.
6. Choose the 'Scenario Summary' option and enter the range of cells you wish to summarize. You can select the cells by highlighting the first one and dragging your cursor to the last one.
Read more ►

How to Protect Sales in Excel 2007


Protect an Entire Worksheet
1. Click on the bottom tab to select the worksheet that you wish to protect.
2. Click on the 'Review' tab on the menu ribbon.
3. Click 'Protect Sheet' in the 'Changes' panel of the 'Review' tab.
4. Enter a password if you want the worksheet to be password-protected. If not, leave the 'Password to unprotect sheet' box blank.
5. Click the 'OK' button and the entire worksheet will be protected from future changes.
Protect Cells Within a Worksheet
6. Click on the bottom tab of the worksheet you want to protect.
7. Click-and-drag the cursor to select the cells containing the data you wish leave unprotected. You can also hold the 'Ctrl' key down and click on multiple individual cells. Entire rows or columns can also be selected.
8. Click the 'Home' tab and then click 'Format Cells.' You can also right-click on a selected cell and select 'Format Cells' from the contextual menu.
9. Click 'Protection' and then uncheck the 'Locked' option.
10. Click 'OK' to apply the changes and close the dialog window.
11. Click on the 'Review' tab and then click on 'Protect Sheet.'
12. Enter a password for the worksheet if desired. Leave the input box blank if you don't want to password-protect the worksheet cells.
13. Click the 'OK' button. All cells except the ones selected in Step One will be protected from future changes.
Read more ►

Wednesday, February 22, 2012

How to Use Freeze Panes in Excel


Freeze the Top Row or Left Column
1. Open the Excel spreadsheet.
2. Navigate to the 'View' tab on the top menu.
3. Click on 'View,' then click on 'Freeze Panes.' A drop-down menu opens.
4. Select the 'Freeze Top Row' option to freeze the top row.
5. Select the 'Freeze Left Column' or 'Freeze First Column' option to freeze the left column.
6. Freeze the top row by using the keyboard and sequentially pressing the keys 'ALT, W, F, R.' Ignore Steps 3 through 7 if using this choice.
7. Freeze the left column using the keyboard by sequentially pressing the keys 'ALT, W, F, C.' Ignore Steps 3 through 7 if using this choice.
8. Unfreeze panes by repeating Steps 3 through 5 and selecting 'Unfreeze Panes' or sequentially press the keys 'ALT, W, F, F.'
Freeze Rows and Columns, Multiple Rows, Multiple Columns, or Multiple Rows and Columns
9. Open the Excel spreadsheet.
10. Freeze column(s) and row(s) at the same time by selecting the cell to the right of and below the location you want to freeze.
11. Freeze multiple rows only by selecting the cell in the left (first) column below the rows you want to freeze.
12. Freeze multiple columns only by selecting the cell in the top row to the right of the columns you want to freeze.
13. Navigate to the 'View' tab on the top menu.
14. Click on 'View,' then click on 'Freeze Panes.' A drop-down menu opens.
15. Select the 'Freeze Panes' option. You have now frozen the columns or rows, or columns and rows you designated.
16. Freeze panes using the keyboard by sequentially pressing the keys, 'ALT, W, F, F.' Ignore Steps 5 through 8 if using this choice.
17. Unfreeze panes by repeating Steps 5 through 7 and selecting 'Unfreeze Panes' or sequentially press the keys, 'ALT, W, F, F.'
Read more ►

How to Do a Paired Two Sample T


1. Enter your first set of data in column A. Enter your second set of data in column B. Make sure you only put one data result in each cell.
2. In cell C1 type '=TTEST(.' Do not hit 'Enter.'
3. Highlight your first data set and type ',.'
4. Highlight your second data set and type ',.'
5. Type '2,1).' Press 'Enter.' You formula in the cell should look like '=TTEST(A1:A5,B1:B5,2,1).' The cell returns the t Test result.
Read more ►

How to Create a Command Button in Microsoft Excel


1. Open the Excel worksheet in which you will be installing the command button.
2. Click the Microsoft Office logo located in the top left-hand corner of the screen and choose 'Excel Options.' Check 'Show Developer Tab' under the Top Options tab, and click 'OK.'
3. Click the 'Developer' tab on the main menu ribbon. Open the 'Insert' drop-down menu, and click 'Command Button.' Select the area of the worksheet where you want to insert the command button, and click on it. The command button will appear.
4. Click the 'Developer' tab on the main menu ribbon and select 'Design Mode.'
5. Right-click on the command button that you just created and select 'Properties.' Select 'Text' to add a caption to the button. Select 'Size' to edit the size and position of the command button. Select 'General' to edit the function of the button. Choose 'AutoLoad' if you want the button activated every time the workbook is opened. Choose 'Placement' to edit the movement of the button on the workbook page. Click 'OK' to save the the command button properties.
6. Right-click on the command button and choose 'Assign Macro.' Select the action that you want the button to perform from the macro list. Click 'OK' to save the command button.
Read more ►

How to Put Error Bars on Points in Excel


Office 2003
1. Right-click the data series (the points on the graph) and click 'Format Data Series.'
2. Click the tabs at the top of the window that read 'X Error Bars' or 'Y Error Bars' as necessary.
3. Select the error bar style under 'Display' and set the error amount as a fixed value, a percentage or a standard deviation.
4. Click 'OK' to apply the error bars.
Office 2007
5. Click on the graph and the 'Chart Tools' toolbar will appear at the top of the screen.
6. Select the 'Layout' tab and click the 'Error Bars' button found on the right side.
7. Click 'More Error Bars Options' and choose the style of error bar, and enter the error amount as a fixed value, percentage or standard deviation.
8. Click 'OK' to apply the error bars.
Read more ►

Blogger news