How to Create a CALCULATED COLUMN in Microsoft Lists

Подписаться 4,2 тыс.

Просмотров 24 тыс.

50% 1

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
creativecommon...
Common Formulas from Microsoft
support.micros...

Опубликовано:

5 окт 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 52

@DominicDSouza 6 месяцев назад

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 6 месяцев назад

You are welcome 🙃 Thank you for sharing!

@Naveenkumar-pp3iu 2 месяца назад

Thanks Amy, for the video. It was lucid and simple. Greetings from Hyderabad, India 🤗👍

@officeskillswithamy 2 месяца назад

You are welcome, happy that it helped. Thank you for the comments ☺️

@stephd2607 Месяц назад

Thanks- that was exactly what I was looking for!

@officeskillswithamy Месяц назад

That is great to hear!

@MichaelxD1609 Год назад

Hello Amy, Thanks for sharing

@officeskillswithamy Год назад

You are so welcome!

@antonyj154 5 месяцев назад

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

@officeskillswithamy 5 месяцев назад

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 5 месяцев назад

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

@rebeccameaney9292 Год назад

Great video! Thx so much ❤️

@officeskillswithamy Год назад

You are welcome, glad that you liked it!

@bizcal 11 месяцев назад

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 11 месяцев назад

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 - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-3h3-OWPeizw.html. 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!

@NabilDerouiche 10 месяцев назад

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

@officeskillswithamy 8 месяцев назад

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.

@selectland1 Год назад

Great video

@officeskillswithamy Год назад

Thank you, I'm glad that it helped!

@rquader 2 месяца назад

Very helpful. Thank you!

@officeskillswithamy 2 месяца назад

You are welcome!

@heribertosanchez5464 3 месяца назад

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

@officeskillswithamy 3 месяца назад

You are welcome, thank you for the comment ☺️

@TARIKBOUGUERAA Год назад

Thank you very much

@officeskillswithamy Год назад

You are welcome, glad that you enjoyed it!

@phloem4 6 месяцев назад

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

@officeskillswithamy 6 месяцев назад

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.

@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 💡 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-kgjWE-SkI-g.html

@DwightGriffith-c7u 8 месяцев назад

Is there a formula for taking out weekends and holidays?

@officeskillswithamy 8 месяцев назад

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.

@wanderson-bastos Год назад

Muito bom! Great!

@officeskillswithamy Год назад

Thank you! Happy to hear that you liked it!

@MrWanted000 9 месяцев назад

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

@officeskillswithamy 8 месяцев назад

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 8 месяцев назад

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

@officeskillswithamy 7 месяцев назад

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 11 месяцев назад

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 11 месяцев назад

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

@aducaale328 День назад

Thanks

@officeskillswithamy 23 часа назад

You are welcome!

@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 😊 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-3h3-OWPeizw.html

@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!

@AbdulJabbar-gd2xe Год назад

Which office platform is this. Is this excel or PowerPoint

@officeskillswithamy Год назад

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

@samuelshehata1605 2 месяца назад

Hi, I wonder if we can use the Today's function to create a review date conditional formatting on MS Lists Example: Conditional format policies based on review date. For Review Within 6 Months For Review Within 1 Year For Review Within 2 Years For Review Within 3 or more Years. Thank you.

@officeskillswithamy 2 месяца назад

Hey, thank you for the comment! I've done another video on this linked here. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-3h3-OWPeizw.html Good luck!

@MichaelxD1609 Год назад

Hello Amy, Thanks for sharing

@officeskillswithamy Год назад

Thanks for watching!