Aug 9, 2018 - Learn how to sort in Excel -- by column, row, number value. For this first set of instructions, we'll be using Microsoft Excel 2017 for Mac. To alphabetize in Excel, highlight a cell in the column you want to sort. With previous versions of Excel or if you choose the wrong options in your configuration, it is easy to sort a single column but break the correlation between the data in that column and the rest of the entries in each row.

Quickbooks 2016 for mac tutorials. I constantly have problems working with dates in Excel, I must be doing something wrong but I don't understand what. I have a spreadsheet, exported from our exchange server, that contains a column with dates on. They have come out in US format even though I'm in the UK. The column in question looks like this In Excel, I have highlighted the column and selected Format Cells. In this dialog box, I have selected the Date, selected English (United States) as the locale and chosen the matching date format from the list.

I hit OK and try to sort the data by this column. In the sort dialogue I choose this column, select sort on Values but the order only gives me options for A to Z, not oldest to newest as I would expect.

This in turn sorts the date data by the first two digits. I am aware I could re-format this data to ISO and then the A to Z sort would work but I shouldn't have too, I'm obviously missing something. EDIT: I messed up the bounty but this should have gone to @r0berts answer, his first suggestion of Text to Columns with no delimiter and choosing 'MDY' as the type of data works.

Additionally, if you have a time (i.e. 18:34:22), you need to first get rid of the time data. However after that the method suggested by @r0berts works fine. The problem: Excel does not want to recognize dates as dates, even though through 'Format cells - Number - Custom' you are explicitly trying to tell it these are dates by ' mm/dd/yyyy'. As you know; when excel has recognized something as a date, it further stores this as a number - such as ' 41004' but displays as date according to format you specify. To add to confusion excel may convert only part of your dates such as, but leave other e.g. Solution: steps 1 and then 2 1) Select the date column.

Under Data choose button Text to Columns. On first screen leave radio button on ' delimited' and click Next. Unclick any of the delimiter boxes (any boxes blank; no checkmarks) and click Next. Under column data format choose Date and select MDY in the adjacent combo box and click Finish.

Now you got date values (i.e. Excel has recognised your values as Date data type), but the formatting is likely still the locale date, not the mm/dd/yyyy you want. 2) To get the desired US date format displayed properly you first need to select the column (if unselected) then under Cell Format - Number choose Date AND select Locale: English (US). This will give you format like ' m/d/yy'. Then you can select Custom and there you can either type ' mm/dd/yyyy' or choose this from the list of custom strings.

Alternative: use LibreOffice Calc. Upon pasting data from Patrick's post choose Paste Special ( Ctrl+Shift+V) and choose Unformatted Text. This will open 'Import Text' dialog box. Character set remains Unicode but for language choose English(USA); you should also check the box 'Detect special numbers'.

Your dates immediately appear in the default US format and are date-sortable. If you wish the special US format MM/DD/YYYY you need to specify this once through 'format Cells' - either before or after pasting. One might say - Excel should have recognised dates as soon as I told it via 'Cell Format' and I couldn't agree more. Unfortunately it is only through step 1 from above that I have been able to make Excel recognize these text strings as dates. Obviously if you do this a lot it is pain in the neck and you might put together a visual basic routine that would do this for you at a push of a button. Dear Patrick, please read carefully.

Or you can use Libre Office Calc - with your data this works straight away - you simply pre-format column for Date - English (USA) and upon pasting do 'Paste Special' 'Unformatted text' - just tell the dialog box that language is English(USA) and don't forget to check the box to recognize special numbers. In my answer I tried to hint as hard as I could that Excel is inconsistent with this (buggy) and you have to resort to these steps 1 and then 2 to achieve the result you want. – Sep 26 '14 at 16:28 •. This may not be relevant to the original questioner, but it may help someone else who is unable to sort a column of dates. I found that Excel would not recognise a column of dates which were all before 1900, insisting that they were a column of text (since 1/1/1900 has numeric equivalent 1, and negative numbers are apparently not allowed). So I did a general replace of all my dates (which were in the 1800s) to put them into the 1900s, e.g.