How to Share Excel Macros

1. Write the macro to be shared in an empty spreadsheet. Click the 'View' tab on the ribbon menu. If you are going to record the macro, click the small arrow at the bottom of the 'Macros' button and then select 'Record Macro...' from the drop-down menu. If you elect to write the macro using Visual Basic for Applications, select 'View Macros...' from the drop-down menu. Provide a macro name and click 'Create' on the 'Macro' dialog.
2. Save the spreadsheet as an 'Excel Macro-Enabled Workbook' file. If the macros are saved in other types of Excel files, they may not be visible due to internal security settings.
3. Distribute the Excel workbook master file to those co-workers with whom you want to share the macro. When a user bases a workbook on this master file, the macro remain embedded.

Read more ►

How to Link a File to MS Excel

1. Open the saved Excel file.
2. Click on the cell or object to insert the file link.
3. Click on the 'Insert' tab on the command Ribbon.
4. Click on the 'Hyperlink' button on the 'Links' group. A window will show 'Insert Hyperlink.'
5. Click on the file name from the list of files in the box.
6. Click 'OK.' The colored hyperlink will show on the Excel worksheet.
7. Save this Excel file.

Read more ►

How to Summarize Data in Excel

1. Use the Subtotals method. Choose one customer name by selecting a cell in column A. Click the 'AZ' sort button--you can find this on the standard toolbar. Next, select 'Data' and then 'Subtotals.' The Subtotals dialog box should appear. Make sure you've checked off 'OldSales,' 'NewSales' and 'Total.' Click 'OK' to see the summary data in bold on your spreadsheet. Press the '2' in the top left corner of your spreadsheet to see only the summary data.
2. Try the Consolidate method. Select a blank cell to the right of your data. Click 'Data' and 'Consolidate.' In the 'Reference' section in the 'Consolidate' dialog box, select your entire table and click 'OK'. This produces a new row with summarized totals for each unique customer in Column A. Delete the 'Date' field in the summary results.
3. Create a Pivot Table. Select a blank cell to the right of your data. Click 'Data' and 'Pivot Table and PivotChart Report.' Select your entire data table and click 'Finish.' Choose to add 'Customer' and 'Total' to the new pivot table. Excel sorts the summarized data by unique customer.

Read more ►

How to Make a Pie Chart Using Microsoft Office 2003

1. Click 'Start,' 'All Programs' and then click 'Microsoft Office Word 2003.'
2. Click 'File' from the top menu and then click 'Open.' Click the file to which you want to add a pie chart and click 'Open.'
3. Click 'Insert' from the top menu and click 'Object' from the drop-down menu.
4. Click 'Create New.'
5. Click 'Microsoft Graph Chart' from the 'Object Type' box and click 'OK.'
6. Click inside a cell on the datasheet window and enter the text or numbers that you want to display in the pie chart. The default chart type is a bar graph.
7. Double-click the chart. A box appears around the chart.
8. Right-click inside a blank area of the chart and select 'Chart Type.'
9. Click 'Pie' from the 'Chart Type' and click 'OK.'

Read more ►

How to Display a Count of Cells on the Bottom of the Window in Excel

1. Open Microsoft Excel and load the spreadsheet you want to edit. This can be an existing file, or you can just use the default new page to test the functions.
2. Enter data in the first column. Use the following data to populate the 'A' column on the spreadsheet:Price
2.00
3.00
4.00
3. Use the Count or CountA function in the bottom field. The following code is an example of how to use the Count function:=count(a1:a4)To use the CountA function, enter the following code instead:=countA(a1:a4)The equal sign is used to denote a formula. This is prefixed in all cells that contain formulas. The two alphanumeric numbers in the parenthesis are the range of fields. The 'a' represents the column and the number represents the row.
4. Use the CountBlank function to count the number of blank cells. This tells you how many cells contain no data in your list of fields. To use the CountBlank function in conjunction with the test data, type the formula below into a cell at the bottom of the spreadsheet:=countblank(a1:a5)
5. Use the CountIF function to display a count only if a particular cell contains the specified data. For instance, suppose you want to count how many cells contain '2.' Use the CountIF function to determine the output. The code is below:=countif(a1:a4, 2.00)The first parameters are the range of cells to check. The second parameter (2.00) is the value to count.
6. View the results. The Count function returns the value of '3.' This is because only three out of the four cells populated have numerical values. The CountA function returns '4.' CountA displays a count of all cells regardless of the data type. For the CountBlank function, the range has only one blank cell, so the return value is '1.' Finally, for the CountIF function, there is one cell with the matching value, so the return value is '1.'

Read more ►

How to Turn Off Filter Arrows in Microsoft Excel 2007

1. Open the Excel sheet that has the filtering option turned on.
2. Click the 'Data' tab on the Microsoft Ribbon.
3. Click the highlighted 'Filter' button in the 'Sort Filter' section to turn off all filter arrows in the spreadsheet.

Read more ►

How to Sum the Span of Cells of Microsoft Excel 2003

1. Create the numbers in the cells that you wish to sum the span of. To sum the span of cells, you will first need to create a series of numbers that you wish to be summed. You can create lists vertically or horizontally depending on your preference.
2. Select the cells that you wish to sum by highlighting them. To highlight the cells that you wish to sum the span of, simply left-click on the first cell and hold the mouse button in as you drag the cursor over the other cells that you wish to sum. Release the mouse button once you have highlighted the desired cells, and they will stay highlighted.
3. Use the 'sum' icon on the command bar to easily implement the formula. The sum icon is located on the top of the command bar in the middle and looks like an 'M' lying on its side. A drop-down menu will open when you click on the sum icon. Select “sum” to create the formula.
4. Save changes to your spreadsheet. You will want to make sure that you save the changes you just made by clicking on the “file” tab and selecting “save.”

Read more ►

How to Enter a Comment in Excel 2007

1. Open an Excel 2007 spreadsheet.
2. Use one of the following methods to bring up a comment box: Right-click the cell and select 'Insert Comment' from the list; right-click the cell and press 'Shift' 'M'; use the keyboard shortcut 'Shift' 'F2'; or select the cell, click the 'Review' tab on the ribbon and select 'New Comment' from the 'Comments' group.
3. Enter your text into the comment box. Highlight the text and add any formatting options including bold, italics, color, font type and size. Click outside the 'Comments' box to close it.

Read more ►

How to Check for Duplicate Records in Excel

1. Click the 'Home' tab.
2. Click 'Conditional Formatting' in the 'Styles' box in the Office ribbon.
3. Select 'Highlight Cell Rules' and then 'Duplicate Values.' The 'Duplicate Values' box will open.
4. Select 'Duplicate' from the drop-down box and choose the color for the highlighted duplicates. Click 'OK.' Excel will show all duplicate values in the color that was chosen.

Read more ►

How to Change the Vertical Axis Numbers on an Excel Bar Chart

Using Chart Tools
1. Click on the vertical axis numbers on the chart area. This action opens a selection box.
2. Click on the 'Layout' tab under 'Chart Tools.'
3. Choose 'Axes->Axes->Primary Vertical Axis->More Primary Vertical Axis Options.' Choose the factor you would like to change. For example, if you want to change the interval between tick marks to 100, write '100' in the top box.
4. Press 'Close.' Excel will automatically update the graph with the new numbers.
Manual Change
5. Click on the number you want to change in the Excel worksheet (not on the graph). For example, if you want to change a vertical axis number from 2,000 to 2,001 on the chart, click on '2,000' cell on the worksheet.
6. Enter the number into the cell.
7. Press 'Enter.' Excel automatically updates the chart for you.

Read more ►

How to Align Double

1. Create a new Excel 2007 spreadsheet from the circular 'Office' tab of the Ribbon. Select 'New' and then 'Blank Workbook' to create a new, blank spreadsheet. Save your document as an Excel 2007 file format from the 'Save As' button from the 'Office' tab.
2. Switch to 'Page Layout' view from the 'View' tab of the Ribbon. Page Layout view allows you to see the parameters of your print document including margins, column width and row height. It also allows you to view the alignment of your cells to prepare them as tags.
3. Right-click the column header and select 'Column Width' to input your tag width. Type the width of your tag in inches, centimeters or millimeters by entering the appropriate units after the value. For example, to make a tag that is two inches wide, input '2in' without the quotation marks.
4. Right-click the row header to the left of the screen and select 'Row Height' to input the height of your tag. Type the height of your tag followed by the appropriate units. Create duplicate cell parameters in the adjacent cells so that when you print, you can fold your tag in half or cut and reverse it to create a double-sided tag.
5. Highlight your cells and click the 'Home' tab of the Ribbon. Click the 'Borders' button from the Font area and select 'All Borders.' Borders will show you the alignment of each tag after you print your document.

Read more ►

How to Edit a Picture in Excel

1. Right-click on the 'Start' button in the bottom-left corner of the Windows desktop.
2. Select 'All Programs' and then select 'Microsoft Office.'
3. Scroll down to the bottom of the Microsoft Office programs choices and select 'Microsoft Office Tools.'
4. Scroll down in the tool selection category and select 'Microsoft Office Picture Manager.'
5. Click 'Pictures' in the pane on the left side of Microsoft Office Picture Manager.
6. Select the picture that is in the Excel spreadsheet.
7. Click 'Edit Pictures' in the tool bar of the picture manager and an 'Edit Pictures' pane will open.
8. Edit pictures by selecting 'Color,' 'Crop,' 'Resize,' 'Compress' or 'Auto Correct.'

Read more ►

How to Merge Two Excel Workbooks Into One

1. Save your original file as a shared file. With the workbook open, Under 'Tools' select 'Share Workbook.' Check the 'Allow Changes' box, then click the 'Advanced' option. Input the number of days you'd like to allow track changes for. It's a good idea to set this for several days after you plan to merge the two workbooks or meet with the individual(s) in charge of the other workbooks, in case you need to make additional changes at that time. Click 'OK.' Save when prompted.
2. Create a copy of the original workbook. Under 'File' select 'Save As' and give the new workbook a different title. For example, you might append the file name using the employee in charge of the new workbook.
3. When you are ready to merge the two workbooks, open the original workbook (saved to your desktop or some other location). Under 'Tools' select 'Merge Workbooks.' In the pop up window, select the workbook to be merged. At this point, the new workbook should be accessible (either uploaded to your desktop from an email or located in a shared drive folder). Save when prompted.
4. Repeat Step 3 for each additional file you wish to merge, saving when prompted.

Read more ►

How to Update Links in Excel

How to Update Links in Excel
1. Open Excel workbook in which you want to update links.
2. Select the 'Edit' menu from the menu list.
3. Select 'Links' from the drop-down list.
4. In the 'Edit Links' pop-up window, select 'Update Values'.

Read more ►

How to Compare Merge Workbooks That Are Greyed Out

1. Share the workbook that needs to be compared and merged. This can be done by clicking 'Tools' then 'Share Workbook.' This is an initial step that needs to be completed before sending the workbook out to be reviewed.
2. Save all workbooks that have changes or additions with unique file names that differ from the original workbook's file name. Also, ensure they are all saved in the same folder as the original workbook before attempting to use the Compare and Merge feature.
3. Click the 'Compare and Merge Workbooks' buttons located on the toolbar. When prompted, select the workbooks that you wish to merge into the original, and click 'OK.' You can merge multiple copies of the workbook by pressing 'Shift' while you are selecting the various workbooks.

Read more ►