Changing a column type

Column2When you configure a list or library, it is important to select the best column type for your purpose.
Each column has their own characteristics and if you make the wrong decision, you may run into issues later. You can not change every column type freely into another column type.

I sometimes find out I’d better use another column type while testing. That is fairly easy to adjust.
It has also happened that I had to make changes after the system had gone live and data had been added to the system. That is a bit trickier.
In a few cases I have made the mistake to delete that incorrect column immediately, only to find out that I had to re-add all data and reinvent all my views.
So, as a note to myself, here’s the proper way:

1. Can your column be changed into another one?
Go to List/Library settings > Column. Your options are shown underneath the title. For instance, a Single Line of Text can be changed into a number of other types:

Single-Line-of-Text can be changed into other column types
Single-Line-of-Text can be changed into other column types

However, you can not always go back to the original type! For instance, if you change this column to Multiple Lines of Text, the change is final. If you change it to Date and Time, you can only go back to 4 options. Also interesting: if you turn the Single-Line-of-Text into Currency or Number, you can then change it into a Yes/No column. But the Date and Time option is gone. How weird is that!

Changing SLOT into Currency and then Yes/No.
Changing SLOT into Currency and then Yes/No.

2. What about the data?
If you can change the column into another type, think about data loss (e.g. when turning a Single-Line-of-Text into a Choice field).
It may be wise to make an export of the data before you change the column type to know your original values.
You will get a warning before you finalize the change:

Warning message before you make changes
Warning message before you make changes

3. Create a new column if necessary.
If your desired option is not available, you will have to create a new column. These are the steps:

  1. Create the new column
  2. Create a Datasheet view where the old and new columns are side-by-side
  3. Copy data from old to new column. Please note this means you change all items in the system, so:
    a. Modified and Modified By will change
    b. sort orders or groupings may change
    c. views may be empty
    d. Alerts may be triggered
  4. Position the new column correctly in every view
  5. Adjust filters in views that are using the values from the old column
  6. Only then remove the old column.

Now let’s hope I remember to follow my own advice next time I have to use another column. 🙂

Image by imelenchon on


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s