Microsoft Knowledge Base Wiki

1. Start Microsoft Excel, and create a new, blank workbook.
2. Click the cell that you want to format with Date formatting.
3. On the Format menu, click Cells.
4. On the Number tab, click Date in the Category list.
5. In the Type list, click the date format that you want, and then click OK.
6. Type the number that you want in the cell that you formatted with the Date format, and then press ENTER.
7. Click the cell from step 6.
8. On the Edit menu, point to Fill, and then click Series.

   Notice that the Series dialog box appears.

9. Under Series in, click Rows if you want to fill a row, or click Columns if you want to fill a column.
10. Under Type, click Date, and then under Date unit, click the date format that you want.
11. In the Step value box, type the number by which you want to increment the series.
12. In the Stop value box, type the number that you want to use as the end value of the series.
13. Click OK.

Notice that the series is filled with dates, and that the dates are incremented correctly throughout the series.

1. Switch to the worksheet that you want to hide.
2. On the Format menu, point to Sheet, and then click Hide.

After you have rehidden any of the worksheets you want to hide, save the
workbook.

When the Resolve Conflicts dialog box appears, if the worksheet where a conflict occurs is hidden, the worksheet will be unhidden, no matter what button you click (Accept All Mine, Accept All Others, Accept Mine, Accept Other, or Cancel).

including the quotation marks. To do this, follow these steps:

1. Click Cells on the Format menu.
2. Click the Number tab.
3. In the Category list, click Custom. In the Type box, type the following

   h:mm a/p”m”

   and then click OK.

Excel X and later versions

For more information about custom number formats, follow these steps:

1. Click Search Excel Help on the Help menu.
2. Type about basic number format codes, and then click Search.
3. Click a topic to view it.

Excel 2001

For more information about custom number formats, follow these steps:

1. Click Office Assistant.
2. Type custom number formats, and then click Search.
3. Click a topic to view it.

Note If the Office Assistant is hidden, click Office Assistant on the standard toolbar.

Excel 98

For more information about custom number formats, follow these steps:

1. On the Help menu, click Contents and Index.
2. Click the Index tab in MS Excel Help.
3. Type number formats, custom, and then double-click the selected text to view the “Custom number, date, and time format codes” topic.

Built-in sorting

In Microsoft Excel, you usually use the sorting feature to sort a
list. A list in Excel is a series of worksheet rows that contain related data,
such as an invoice database or a set of client names and phone numbers. You can
use a list as a database, in which each row is a record, and the columns are
fields. The first row of the list usually has labels for the columns, for
example:

   A1: Q1   B1: Q2   C1: Q3
   A2: 10   B2: 2    C2: 4
   A3: 14   B3: 18   C3: 9
   A4: 7    B4: 17   C4: 12
   A5: 1    B5: 13   C5: 5
   A6: 11   B6: 8    C6: 16
   A7: 3    B7: 6    C7: 15
				

If you sorted this list in ascending order, by the Q1 field
(column A), the results would be as follows:

   A1: Q1   B1: Q2   C1: Q3
   A2: 1    B2: 13   C2: 5
   A3: 3    B3: 6    C3: 15
   A4: 7    B4: 17   C4: 12
   A5: 10   B5: 2    C5: 4
   A6: 11   B6: 8    C6: 16
   A7: 14   B7: 18   C7: 9
				

Sorting a newspaper-style column

Newspaper columns and snaking columns are different terms for the
same type of data format. In newspaper columns, the data fills one column and
continues at the top of the next column. In this case, your data probably does
not have labels for each column, for example:

   A1: 10   B1: 2    C1: 4
   A2: 14   B2: 18   C2: 9
   A3: 7    B3: 17   C3: 12
   A4: 1    B4: 13   C4: 5
   A5: 11   B5: 8    C5: 16
   A6: 3    B6: 6    C6: 15
				

You can use the sample macro in this article to sort newspaper
columns in ascending order. If you use the sample macro on the above data, the
results are as follows:

   A1: 1    B1: 7    C1: 13
   A2: 2    B2: 8    C2: 14
   A3: 3    B3: 9    C3: 15
   A4: 4    B4: 10   C4: 16
   A5: 5    B5: 11   C5: 17
   A6: 6    B6: 12   C6: 18
				

To see the sorting macro work as described, follow these steps:

1. Open a new workbook.
2. Type the following sample data in the worksheet:

      A1: 10   B1: 2    C1: 4
      A2: 14   B2: 18   C2: 9
      A3: 7    B3: 17   C3: 12
      A4: 1    B4: 13   C4: 5
      A5: 11   B5: 8    C5: 16
      A6: 3    B6: 6    C6: 15
   					

3. On the Tools menu, point to Macro, and then click Visual Basic Editor.
4. On the Insert menu, click Module.
5. Type the following code in the module:

   Note The following example demonstrates how to sort in ascending
   order. You can change this by modifying the value of the “Order1:=” argument to
   xlDescending.

      Sub SortAllRangeData()
      ' Place column header for temporary sort area.
      Range("IV1").Value = "Numbers"
   
      ' Move numbers to temporary sort location.
      For Each cell In Selection
         Range("iv65536").End(xlUp).Offset(1, 0) = cell.Value
      Next cell
   
      ' Sort numbers in ascending order.
      Range("IV1", Range("IV1").End(xlDown)).Sort Key1:=Range("IV2"),  _
      Order1:=xlAscending, Header:=xlGuess, _
           OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
   
      ' Move sorted data back to original sheet location.
   
      Selection(1, 1).Activate ' Make sure the ActiveCell is the
                               ' top left of Selection first.
      CCnt = Selection.Columns.Count
      RCnt = Selection.Rows.Count
      CellCnt = Selection.Cells.Count
      Tcell = 2
      For c = 1 To CCnt
        For r = 1 To RCnt
           Range(ActiveCell.Address).Offset(r - 1, c - 1).Value =  _
           Range("iv" & Tcell).Value
           Tcell = Tcell + 1
        Next r
      Next c
   
      ' Clean up temporary sort location.
      Range("IV1", Range("IV1").End(xlDown)).Clear
   End Sub
   					

6. On the Excel menu in Microsoft Excel X for Mac and in later versions of Excel for Mac, or on the File menu in all other versions of Excel, click Close and Return to Microsoft
   Excel.
7. Select A1:C6.
8. On the Tools menu, point to Macro, and then click Macros. Select the SortAllRangeData macro, and then click Run.

All the data within the selected range is now sorted, and
appears as follows:

   A1: 1    B1: 7    C1: 13
   A2: 2    B2: 8    C2: 14
   A3: 3    B3: 9    C3: 15
   A4: 4    B4: 10   C4: 16
   A5: 5    B5: 11   C5: 17
   A6: 6    B6: 12   C6: 18
				

Excel X for Mac and later versions

For more information about how to sort, click Excel Help on the Help menu, type sorting, click Search, and then click a topic to view it.

For more information about how to use the Sort method from the Visual Basic Editor, click Visual Basic Help on the Help menu, type sort method, click Search, and then click to view sort method.

Excel 2001 for Mac

For more information about how to sort, click the Office Assistant, type sort a list, click Search, and then click a topic to view it.

Note If the Assistant is hidden, click the Office Assistant button on the Standard toolbar.

For more information about how to use the Sort method from the Visual Basic Editor, click the Office Assistant, type sort method, click Search, and then click to view sort method.

To create an example that demonstrates how to use the Text To Columns command:

1. Create a new workbook.
2. In Sheet1, type the following data:

         A1: The,dog,ran
         A2: abc,def
         A3: abcdefg,hijkl
         A4: 2554,4874,41
   					

3. Select the range A1:A4.
4. On the Data menu, click Text to Columns.
5. In Step 1 of the Convert Text To Columns Wizard, make sure that
   the Delimited option is selected, and then click Next.
6. In Step 2 of the Convert Text To Columns Wizard, click to select the Comma check box under Delimiters, and then click Next.

   NOTE: All of the check boxes under Delimiters should be cleared except for the Comma check box.

7. In Step 3 of the Convert Text To Columns Wizard, type B1 in the Destination box, and then click Finish.

Your data is parsed into separate columns, beginning with column B, and
the data in Sheet1 should resemble the following:

   A1: The,dog,ran     B1: The     C1: dog   D1: ran
   A2: abc,def         B2: abc     C2: def
   A3: abcdefg,hijkl   B3: abcdefg C3: hijkll
   A4: 2554,4874,41    B4: 2554    C4: 4874  D5: 41
				

Note The example illustrates data with commas as the delimiter. This is only one of several delimiters that you may see in your data. For example, if you have Names in a column of data, consisting of first and last name in a single cell separated by a Space, the Space character can be used as a delimiter to separate the first and last names into adjacent cells instead of the same cell.

For example, the text file may contain the following text:

   ID, STATUS
   123, open
   456, closed
				

Note This problem does not occur if the first two characters are lowercase (id).

How to Insert an Apostrophe

To add an apostrophe to the beginning of the first line of text in your file, follow these steps:

1. Open the text file in a text editor, such as SimpleText or TextEdit. Click to place the insertion point before the first character in the first line of text. Press the apostrophe key on your keyboard (‘).
2. On the File menu, click Save. Quit the text editor.
3. You can now open the file in Excel.

=1*(.5-.4-.1)

may be evaluated to the quantity (-2.78E-17), or
-0.0000000000000000278 instead of 0.

Method 1

To minimize any effects of floating point arithmetic storage
inaccuracy, use the Round() function to round numbers to the
number of decimal places that is required by your calculation. For example, if
you are working with currency, you would likely round to 2 decimal
places:

=ROUND(1*(0.5-0.4-0.1),2)

Method 2

You can frequently prevent floating point rounding errors from
affecting your work by using the Precision as displayed
option. This option forces the value of each number in the worksheet to be the
precision that is displayed on the worksheet.

Note Use of the Precision as Displayed option can
have cumulative calculation effects that can make your data increasingly
inaccurate over time. Use this option only if you are certain that the
displayed percision will maintain the accuracy of your data.

To use
the Precision as displayed option, follow these steps:

Excel 2000, Excel 2002, and Excel 2003

1. On the Tools menu, click
   Options.
2. On the Calculation tab, under
   Workbook options, click to select the Precision as
   displayed check box.
3. Click OK.

Excel 2007

1. Click the Microsoft Office Button, click Excel
   Options, and then click Advanced category.
2. In the When calculating this workbook
   section, select the workbook that you want, click to select the Set
   precision as displayed check box, and then click OK.

If you use the Precision as
displayed option, you must format your numbers by using a specific
number format.

To format cells to a specific number precision, follow
these steps:

1. Right-click
   the cells that you want to format,
   and then click Format
   Cells.
2. On the Number tab, under
   Category, click Number.
3. In the Decimal places box, select the
   precision (number of decimal places) that you want.
4. Click OK.

Excel 2010

1. Click on the File tab, click Excel
   Options, and then click Advanced category.
2. In the When calculating this workbook
   section, select the workbook that you want, click to select the Set
   precision as displayed check box, and then click OK.

If you use the Precision as
displayed option, you must format your numbers by using a specific
number format.

To format cells to a specific number precision, follow
these steps:

1. Right-click
   the cells that you want to format,
   and then click Format
   Cells.
2. On the Number tab, under
   Category, click Number.
3. In the Decimal places box, select the
   precision (number of decimal places) that you want.
4. Click OK.

IEEE 754 specifies that numbers be stored in binary format to
reduce storage requirements and allow the built-in binary arithmetic
instructions that are available on all microprocessors to process the data in a
relatively rapid fashion. However, some numbers that are simple, nonrepeating
decimal numbers are converted into repeating binary numbers that cannot be
stored with perfect accuracy.

For example, the number 1/10 can be
represented in a decimal number system with a simple decimal:

.1

However, the same number in binary format becomes the repeating
binary decimal:

.0001100011000111000111 (and so on)

This number cannot be represented in a finite amount of space.
Therefore, this number is rounded down by approximately -2.78E-17 when it is
stored.

If several arithmetic operations are performed to obtain a
given result, these rounding errors may be cumulative.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

Excel 2007 and Excel 2010

For more information about how to change the precision of calculation,
click Microsoft Office Excel Help on the toolbar, type
change when and how formulas are calculated in Excel Help, and then click Search to view the
topic.

Excel 2002 and Excel 2003

For more information about changing the precision of calculation, click Microsoft Excel Help on the
Help menu, type change when and how formulas are
calculated in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Excel 2000

For more information about changing the precision of calculation, click Microsoft Excel Help on the
Help menu, type change the way Microsoft Excel
calculates formulas in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Note The trendline formula should only be used when your chart is an XY Scatter chart. This chart plots both the X axis and the Y axis as values. Line, Column, and Bar charts plot only the Y axis as values. The X axis is plotted only as a linear series in these chart types, regardless of what the labels actually are. Therefore, the trendline will be inaccurate if displayed on these types of charts. This behavior is by design.

1. In the chart, select the trendline equation.
2. On the Format menu, click Selected Data Labels.
3. Click the Number tab, and then click Number in the Category list.
4. In the Decimal places box, increase the number of decimal places to 30 so that you can see all the decimal places.
5. Click OK.

• You edit an Excel workbook that is saved on a SharePoint site that is running Excel Services in Microsoft Office SharePoint Server 2010.
• The workbook contains a PivotTable.
• You press ENTER to expand the PivotTable, or you press ENTER to collapse the PivotTable.

In this scenario, the PivotTable does not expand or it does not collapse.

To collapse a PivotTable in Excel Services in SharePoint Server 2010, press ALT+UPARROW.

• You have a server that is running Excel Services in Microsoft Office SharePoint Server 2010.
• On this server, you have a Microsoft Excel workbook that is saved to a SharePoint site.
• You edit the workbook.
• You type a new value into a cell that contains an array formula.

In this scenario, the contents of the cell changes to show to the new value and then the contents of the cell changes back to the array formula. However, you do not receive an error message.

1. Open the Excel workbook in the client version of Microsoft Excel 2010.
2. Make the change to the cell value.
3. Save the Excel workbook to the SharePoint site.