How to Create a Calculated Column in Microsoft Lists in Teams or Sharepoint - Microsoft List Tips

In this step-by-step tutorial we will learn how to create a calculated column in Microsoft Lists or Sharepoint Lists. Follow along as we demonstrate a simple calculation and also showcase how to use an IF statement in your calculated column. Additionally, we'll share some essential tips and limitations to help you get started. By the end of this video, you'll have a thorough understanding of how to create a calculated column in Lists, making it easy to organize and analyze your data. If you are a student, teacher, project manager, business owner or super nerd like me, then we have got you covered!
RECOMMENDED VIDEOS
Microsoft Lists:
Concatenate Columns & Dates - • How to Concatenate Col...
Calculated Column Custom Date Format - • [2024] - How to Apply ...
Record Form Response - • How to Record Form Res...
Record Form Response Attachment - • [2024] - How to Record...
OFFICE SKILLS WITH AMY
Creating easy-to-follow & fun, how-to-technology videos so that you can be efficient and save time in the workplace.
DISCOVER YOUR INNER NERD
In Wikipedia's definition of Nerd, the word has been "redefined by some as a term of pride.'
I have been advertising myself as an Excel Nerd in interviews since 2009. My goal is to inspire You to Discover Your Inner Nerd.
WHAT WILL YOU LEARN
- Be efficient
- Automate Tasks
- Save time
NEW CONTENT WEEKLY
TECHNOLOGY VIDEOS TO COME
Microsoft 365 apps including Microsoft Office, Microsoft Loop, Microsoft Teams, Microsoft Excel, Microsoft Word, Microsoft PowerPoint, Microsoft Outlook, Microsoft OneNote and more!
LIKE, COMMENT & SUBSCRIBE
If you like my videos then please give them a thumbs up and hit that subscribe button :) If you have ideas for future videos that please feel free to pop them in the comments. Let's Nerd out!
REFERENCES
Audio: Sunset Strip by Audionautix is licensed under a Creative Commons Attribution 4.0 license
creativecommons.org/licenses/...
Common Formulas from Microsoft
support.microsoft.com/en-us/o...

[@DominicDSouza]

Thank you so much for this tutorial. I had been racking my brains to figure out what I was doing wrong! in the Microsoft forum, there is only reference to the formula but nobody says you need to go through the steps that you show here. Much appreciated!

[@officeskillswithamy]

You are welcome 🙃 Thank you for sharing!

[@rebeccameaney9292]

Great video! Thx so much ❤️

[@officeskillswithamy]

You are welcome, glad that you liked it!

[@heribertosanchez5464]

Nice & Simple thanks Amy for Sharing, It really helps a lot ¡

[@officeskillswithamy]

You are welcome, thank you for the comment ☺️

[@wanderson-bastos]

Muito bom! Great!

[@officeskillswithamy]

Thank you! Happy to hear that you liked it!

[@user-rr3ev5dm1z]

Thank you very much

[@officeskillswithamy]

You are welcome, glad that you enjoyed it!

[@MichaelxD1609]

Hello Amy, Thanks for sharing

[@officeskillswithamy]

Thanks for watching!

[@selectland1]

Great video

[@officeskillswithamy]

Thank you, I'm glad that it helped!

[@antonyj154]

Thank you very much for the information. Is there a way to add the "creation date" column? Without it being editable... 🙏

[@officeskillswithamy]

Thank you for the comment, there is a default column called Created that shows the date an item was created. This is often hidden when a list is created, but if you select it from 'show / hide columns' then you can easily add the Created Date to a list. This column type cannot be edited. Hope that this helps!

[@antonyj154]

@@officeskillswithamy Oh Wow! I have it. Thanks thanks thanks!

[@NabilDerouiche]

Thank you for this excellent video. Is it possible to calculate a difference between an existing date and today's date?

[@officeskillswithamy]

Hi there, thank you for your comments :) Yes you can! You can simply do a subtraction or addition formula. For today you can use the variable today(). For example, =today()-[Date Column] You will set the calculated formula type as Number.

[@phloem4]

thanks. How to you get a sum to show at the bottom of a calculated column? This not natively available for calculated columns.

[@officeskillswithamy]

Hello, thank you for the comment. To create a 'totals row' you would have to use a Numbered Column as this is the only column type that has this features, unfortunately, the calculated column does not have this option. To gather this info, you would need to analyze the data outside of Lists such as exporting to Excel or running a Power BI Report.

[@bizcal]

Nice Video, but I am looking for a formula to add risk score column which will be dependent on two other columns. For ex "impact" rating is "major" and "likelihood" rating is "possible" then it should give a risk score of "8"

[@officeskillswithamy]

Thank you for the question! It sounds like this would be a nested IF AND statement. I would suggest something along the lines of this: IF(AND([Impact]="Major",[Likelihood]="Possible"),8,if(.......) I'll do a video in the future on more advanced IF statements in the calculated column of Lists. In the meantime, I would suggest watching this video here - kzbin.info/www/bejne/aZmWXoKNhcqcsNk. Although it isn't specifically what you are after, it has lots of information on if statements in calculated columns and other tips that could be helpful to you. Good luck!

[@MrWanted000]

can we display the sum the SUM of a calculated column? for ex: the cost column in the video

[@officeskillswithamy]

Hi there, I am not sure if I understand this correctly, but you could use a simple addition formula if that is what you are referring to. =[column 1]+[column 2] I hope that this helps!

[@MrWanted000]

@@officeskillswithamy yeah but how to make a total sum of that calculated column? After intensive researching I guess that is not possible

[@officeskillswithamy]

Hello! Calculated columns calculate content in that specific row. So unfortunately we cannot create a sum of the calculated columns as that would be vertical. You'll need to export the list to Excel to gather this info.

[@Antoniopagliuca]

the calculate colum still exist? i have free version and it is no longer available, do you know if the reason is the free?

[@officeskillswithamy]

The calculated column does exist but unfortunately it is not available in the free version of MS 365.

[@brandonrodrigues8407]

Is there a way to concatenate 2 columns into calculated field and check for uniqueness and non blank ?

[@officeskillswithamy]

Hello, thank you for the question :) Concatenate, YES. You can use the =CONCATENATE() formula, I am also going to be releasing a more detailed video on this in the coming weeks, stay tuned! Unfortunately, there is no way to check for uniqueness as calculated columns only work on the row, it cannot reference other rows. I would suggest having a unique column type in your list where you specify that it is 'unique values'. And have that pull to your formula so that you would know each row was unique. For non-blanks, you could use the count nonblank formula, =COUNTA([Column1], [Column2]). Hope that this helps!

[@libafriedlander2428]

Can a formula be added to have the date column change colors based on current date plus one week?

[@officeskillswithamy]

Hello! Great question, I have not played around with this personally but I will look into, stay tuned for a future video. I did find this article that may be helpful to you in the meantime, 'Formatting an item when a date column is before or after today's date (advanced)' - learn.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting#formatting-an-item-when-a-date-column-is-before-or-after-todays-date-advanced

[@officeskillswithamy]

This video that I just released may be what you are looking for 😊 kzbin.info/www/bejne/aZmWXoKNhcqcsNk

[@user-vl5rf3dq2c]

Is there a formula for taking out weekends and holidays?

[@officeskillswithamy]

Thank you for the comment. Unfortunately, there is no Workday formula like there is in Excel. I have seen people do nested if statements with the Weekday function, but then you would also need to add in holidays which could get very long. I know that it is not ideal but it may be easiest to export your List to Excel to run the formula there.

[@AbdulJabbar-gd2xe]

Which office platform is this. Is this excel or PowerPoint

[@officeskillswithamy]

It is within Microsoft Lists, the logo on the thumbnail :)

[@JuanSalvoElEternauta]

Hello Amy! Thanks for sharing these tips! BTW, I have a list with two Choice columns I'd like to link (if possible). I want the 2nd column to show a set of values for selection when you choose a value in the first one. Can this be done? Thanks again! You got yourself a new subscriber :)

[@officeskillswithamy]

Thank you 😊. My first thought was to create a Form and add branching, this would give you the options that you are talking about. Then you could create a Flow to Record a Form Response in MS Lists. I created a video and linked it below. Depending on your use, this may not be the best option but it would work. This can also be done in Power Apps, which I'll be doing a video on in the coming weeks! Thank you for the video idea 💡 kzbin.info/www/bejne/oZjNiHhjiNB8Y8k

[@MichaelxD1609]

Hello Amy, Thanks for sharing

[@officeskillswithamy]

You are so welcome!