Smartsheet Tutorial: How to Create Automations, Build Workflows, & Set Up Conditional Formatting

Summary

In this webinar, Charlie Roy, an implementation consultant from Echo Consulting, provides an in-depth tutorial on utilizing conditional formatting and automations within Smartsheet to enhance project management. The session covers setting up conditional formatting rules to improve sheet organization and readability, alongside constructing simple automations to streamline processes, reduce manual data entry, and ensure timely updates. The webinar is interactive, allowing participants to ask questions, with a recording made available post-event. Additionally, Echo Consulting's upcoming webinars on risk management and leading impactful meetings are introduced.

Takeaways

• 📌 Conditional formatting in Smartsheet helps in better organization and easier viewing by changing formats based on specified conditions.
• 🛠 Automations in Smartsheet can reduce manual data entry, keep stakeholders updated, and streamline processes for increased efficiency.
• 📝 Recording of the webinar will be available post-session, making it easy for attendees to revisit the content.
• 📰 Conditional formatting rules can be created to highlight specific rows or cells, such as phases or overdue tasks, improving readability.
• 🚨 Automations can be set up to send notifications, like update requests, to stakeholders when certain conditions are met, ensuring timely communication.
• 💡 The session provides practical examples of creating conditional formatting rules and automations, offering hands-on learning for attendees.
• 💁‍💼 Echo Consulting is highlighted as a women-owned project management solutions company, showcasing its expertise in the webinar.
• 💬 Audience engagement is encouraged through the webinar, allowing for real-time questions and interaction.
• 📢 Upcoming webinars and additional resources from Echo Consulting are mentioned, providing further learning opportunities.
• 🔧 Tips and tricks for effective use of conditional formatting and automations in Smartsheet are shared, enhancing the utility of the tool.

Q & A

Who conducted the webinar on conditional formatting and automations in Smartsheet?

Charlie Roy, an implementation consultant for Echo Consulting.

What is Echo Consulting?

Echo Consulting is a women-owned project management solutions company offering a variety of services to a wide range of organizations and organization sizes.

What were the main topics covered in the webinar?

The webinar covered the purpose and use cases for conditional formatting and automations in Smartsheet, including how to create conditional formatting rules and simple automations.

What is the purpose of conditional formatting in Smartsheet?

Conditional formatting aims to provide better organization and easier viewing of content within sheets and reports by changing the format of specific rows, columns, or cells based on specified conditions.

How can automations in Smartsheet enhance efficiency?

Automations are used to reduce manual data entry, keep stakeholders up to date on relevant tasks, and streamline processes for increased efficiency.

What are some examples of conditional formatting rules demonstrated in the webinar?

Examples include highlighting phase rows for better visibility and highlighting tasks that are past due and incomplete in red.

What types of automations were demonstrated in the webinar?

The webinar demonstrated how to create an update request automation for assigned tasks and how to automatically populate cell values in new rows.

How can you customize an automation in Smartsheet?

You can customize automations by setting triggers, actions, and additional conditions. You can also customize the message that goes out with an alert.

What are the benefits of recording dates using automations in Smartsheet?

Recording dates automatically when a task's status changes to complete helps in tracking progress and maintaining accurate records over time.

What upcoming webinars were mentioned by Echo Consulting?

Echo Consulting mentioned upcoming webinars on risk management techniques utilizing a crate log and best practices for leading impactful meetings.

Outlines

00:00

📄 Introduction to Conditional Formatting and Automations

The speaker introduces himself and the purpose of the webinar, which is to explore conditional formatting and automations in Smartsheet. He explains the benefits of conditional formatting for better organization and easier viewing of content, and the advantages of automations for reducing manual data entry, keeping stakeholders informed, and streamlining processes. He then begins demonstrating how to create conditional formatting rules in Smartsheet.

05:00

✨ Creating Conditional Formatting Rules

The speaker walks through creating a conditional formatting rule to highlight phase rows in a project plan sheet. He then demonstrates highlighting rows where the task is past due and incomplete using additional conditions. Next, he applies a rule to gray out completed task rows. By the end of this section, the project plan is visually enhanced to easily identify critical information.

10:04

⚙️ Setting up Automations

The speaker introduces automations and their purpose of streamlining processes. He begins by creating an automation to send an email notification when a task is assigned to someone. The steps involve setting a trigger (when the 'Assigned To' field changes), selecting an action (alerting someone), and customizing the message details. This automation helps keep stakeholders informed about their assigned tasks.

15:06

🔄 Creating Default Values with Automations

The speaker demonstrates how to use automations to reduce manual data entry by pre-populating certain fields when a new row is added. He creates an automation that sets the 'Percent Complete' to 0, 'Status' to 'Not Started', and 'Task Type' to 'Task' whenever a new row is added. This automation streamlines the process of creating new tasks in the project plan.

20:10

📅 Recording Dates with Automations

The speaker showcases an automation to record the date when a task is marked as complete. The trigger is set to fire when the 'Status' column changes to 'Complete', and the action is to record the current date in the 'Complete Date Record' column. This automation helps track task completion dates without manual entry, enhancing project reporting and analysis.

Keywords

💡Conditional Formatting

Conditional formatting refers to the ability to apply specific formatting (such as colors, fonts, or highlights) to cells, rows, or columns in a spreadsheet based on predefined conditions or rules. In the context of this video, conditional formatting is used in Smartsheet to better organize and visualize important information within project plans or sheets. For example, highlighting rows where tasks are past due, bolding phase headings, or graying out completed tasks. By applying conditional formatting, users can quickly identify and focus on the relevant or critical information within their sheets.

💡Automations

Automations are pre-defined actions or workflows that are triggered by specific events or conditions within a Smartsheet sheet. They are designed to streamline processes, reduce manual data entry, and increase efficiency. In the video, examples of automations include sending notifications when a task is assigned to a user, populating default values (such as setting the percent complete to 0% for new tasks), and updating the status of a task based on the percent complete value. Automations help automate repetitive tasks and keep stakeholders informed about relevant updates.

💡Project Plan

A project plan is a comprehensive document or sheet that outlines the tasks, schedules, resources, and other details required for completing a project. In the video, the presenter demonstrates conditional formatting and automations using a basic project plan sheet in Smartsheet. A project plan serves as a central reference point for tracking progress, managing tasks, and ensuring that the project stays on track.

💡Formatting Bar

The formatting bar is a user interface element within Smartsheet that provides access to various formatting options, including conditional formatting. In the video, the presenter navigates to the formatting bar and selects the conditional formatting button to create and apply formatting rules based on specific conditions. The formatting bar is a key tool for customizing the appearance and organization of information within a Smartsheet sheet.

💡Triggers

In the context of automations, triggers are the specific events or conditions that initiate or 'trigger' an automated action or workflow. In the video, examples of triggers include when rows are added or changed, when a date is reached, and when a specific column value changes (e.g., when the status changes to 'complete'). Triggers are the starting point for defining automated workflows, allowing users to specify the conditions under which certain actions should be performed.

💡Actions

Actions are the specific tasks or operations that are performed as part of an automation workflow. In the video, examples of actions include alerting someone (sending a notification), changing cell values (such as setting the percent complete to 0% for new tasks), and recording dates (e.g., updating the complete date record when a task is marked as complete). Actions define the behavior or functionality that should occur when the specified trigger conditions are met.

💡Workflow

A workflow is a series of connected steps or actions that are performed in a specific order to achieve a desired outcome. In the context of Smartsheet automations, a workflow defines the trigger conditions, actions to be taken, and any additional conditions or logic that should be applied. In the video, the presenter creates and configures various workflows to automate tasks such as sending notifications, populating default values, and updating task statuses based on specific conditions.

💡Stakeholders

Stakeholders are individuals or groups who have a vested interest in the successful completion of a project or task. In the context of the video, the presenter mentions using automations to keep stakeholders up-to-date on relevant tasks. By automating notifications or alerts, stakeholders can be informed about important updates, assigned tasks, or changes in project status without the need for manual communication. Keeping stakeholders informed is crucial for effective project management and collaboration.

💡Manual Data Entry

Manual data entry refers to the process of manually inputting or updating data into a system or document. In the video, the presenter highlights the use of automations to reduce manual data entry, such as populating default values for new tasks or updating task statuses based on certain conditions. Automating these tasks not only saves time and reduces the potential for human error but also increases efficiency by eliminating repetitive and tedious data entry tasks.

💡Streamline Processes

To streamline processes means to optimize and simplify workflows or procedures by eliminating unnecessary steps, reducing redundancies, and automating repetitive tasks. In the video, the presenter emphasizes the use of automations in Smartsheet to streamline processes for increased efficiency. By automating tasks like sending notifications, updating statuses, and recording dates, users can focus on more critical tasks while ensuring that important information is captured and shared seamlessly.

Highlights

One of the purposes of conditional formatting is just better organization, and easier viewing of content within your sheets and reports.

You can change the format on specific rows and columns that meet specified conditions and you can highlight certain cells that meet specified conditions.

We like to use automations to reduce manual data entry, keep stakeholders up to date on relevant tasks and streamline processes for increased efficiency.

Creating a conditional formatting rule to highlight phase rows in a project plan to make it easier to digest.

Highlighting rows where the finish date is in the past and the task status is 'not started' or 'in progress' to easily identify delayed tasks.

Graying out completed task rows to focus on upcoming or incomplete tasks.

Using an 'update request' automation to send a notification when a task is assigned to someone.

Creating an automation to pre-populate default values like percent complete, status, and task type when a new row is added to reduce manual data entry.

Recording a date in a separate column when a task's status is marked as 'complete' using an automation.

Creating an automation to update the status to 'complete' when the percent complete reaches 100% to maintain consistency.

Noting that automations only run after they are created, so existing discrepancies may need to be manually updated.

Mentioning that automations can be scheduled to run hourly, daily, or weekly to receive updates in batches instead of individually.

Explaining the importance of setting up columns as the correct data type (e.g., date columns) before creating automations that rely on those column types.

Highlighting the ability to use templates or create automations from scratch in Smartsheet.

Mentioning the availability of a recording and slide deck from the webinar for future reference.

Transcripts

conditional formatting and automations

in smartsheet

my name is charlie roy i'm a

implementation consultant for echo

consulting

echo consulting is a women-owned project

management solutions company

we offer a wide variety of services to a

wide range of organizations and

organization sizes

just to go through the rules of

engagement for this webinar please feel

free to unmute yourself at any time to

engage and ask questions throughout the

webinar

there should also be some time at the

end

to follow up with questions if you have

them

please feel free to take notes as we go

throughout but a recording of this

webinar will be made available to you

once it's finished

so what to expect today

we're going to be going through uh kind

of the purpose and some use cases for

conditional formatting and automations

in your smartsheet sheets

we'll go through how to use conditional

formatting and actually

create a few and we'll also go through

how to build

some simple automations in your

smartsheet sheets

as we go through we'll be giving a few

tips and tricks for conditional

formatting and automations

so one of the purposes of conditional

formatting is just better organization

and easier viewing of content within

your sheets and reports

you can change the format on specific

rows and columns that meet specified

conditions and you can highlight certain

cells that meet specified conditions

for automations

we like to use them to reduce manual

data entry

keep stakeholders up to date on relevant

tasks and streamline processes for

increased efficiency

so now we're going to head into

smartsheet

and as you can see here i just have a

basic project plan for you

we're going to get started as you can

see here it's just all white it's not

the easiest to read and we're going to

make a few conditional formatting rules

just to make this a little bit easier to

digest

so one of the in order to um

use conditional formatting you're going

to go up to your formatting bar in your

smartsheet sheet and you're going to

click on this button here for

conditional formatting

and to add a new rule you just click add

new rule and then you're going to get

prompted to set a condition

and then

under this format that's where you can

set the type of formatting whether it be

bolding

your

background fill of the cell or the color

of your text

and then you're also going to select

where that format

is highlighted whether it's just a cell

or an entire row

so we're going to start by i'm going to

back out here

this card

and so

i want to highlight all of these phase

rows just so that

it's a little bit easier to digest

within my sheet

so i'm going to go here and create a new

rule

and my condition is going to be that

this task type column over here

is a phase

now i'm just going to

change the format to highlight it

there we go

and i might bold the letters as well

okay

so now all of my phase headings

are going to show up in this light blue

and all of the text within those

headings is going to be bolded

so some other conditional formatting

that we like to do is based on

conditions within the cells

so for instance if

a task is past due and the task is

incomplete it'd be nice to highlight

that row

so we're going to do that so we can see

here that we have this received lumber

delivery in our project plan it has a

finish date that is in the past

and it is still in progress so we're

going to highlight that row

we're going to add a new rule

so we're going to set the condition

that the finish

is in the past so you have a whole bunch

of options to select from here

i'm going to select in the past

okay

and format it we're just going to

highlight that whole row

in a red color

now

we're going to come back in and edit

that

condition because as we can see here

we have ordering the lumber as a task

but that's complete so we don't

necessarily need to highlight that as

red

going back into the conditional

formatting

we're going to take this finishes in the

past

option here and we are going to

add

a condition

so it's if the finish is in the past and

if the status

is not started or still in progress

so now we can see that it's only

highlighting that one row

any questions so far is everybody

following along

all right i'm going to take the silence

as everybody's got it

so now we're going to go in as well

because when you're looking at your

project plans it's helpful to look at

only the things that are upcoming or the

things that you still need to get done

so i'm going to add a formatting here

to

gray out any of the rows that have been

completed

once again i'll go back up to add

conditional formatting i'm going to

add a new rule

and i'm going to say

if the status

is complete

we're going to apply this format

to the entire row

okay

excellent

cool

so as you can see by adding some

conditional formatting it becomes a lot

easier for you to

look at

um

any tasks that are upcoming or the real

content of the sheet that matters to you

so now we're going to go into how to add

some automations here to kind of

streamline your

processes and one of

my favorite automations is using an

update request

so for instance let's say that

we need to schedule a crane in this

example and i come over here and i

assign the task

to myself

now what's helpful is

in smartsheet by using an automation

when i get assigned this task i can have

smartsheet send me a notification that

i've been assigned so we're going to go

through how to build that up

this

in order to create new automations in a

sheet you come up to this automation tab

and in our case we're going to create a

workflow from scratch

however

as you can see

there's all kinds of templates that

smartsheet has available to you

we're going to go to create from scratch

so here is our workflow we're going to

title this

sign to

alert

okay

so in an automation

there's always going to be a trigger

and an action and you're also going to

be able to add additional conditions as

well

so our trigger we have a few options

here

when rows are added or changed

uh when a date is reached

just when rows are changed or just when

rows are added

in our case we're going to come down

here to when rows are added or changed

and we're going

to go when the assigned to changes to

any value you could also set this

condition specifically for yourself or

another person on your team

now we're going to select our action and

there's a large variety of actions that

you can choose from

in this case we're going to choose to

alert someone so this is going to come

in the form of an email from smartsheet

you could also send an alert to a

microsoft teams channel

you can alert a slack channel

you can generate a document in pdf form

request an update

request an approval

or a variety of other a variety of other

things

we're going to select alert someone

and now with this alert you can choose

to send it to specific people whether

that be a project manager or someone

else

or

you can send it to

contacts in a cell

everyone shared to the to the sheet

we're going to select contacts in the

cell so this alert is going to go to the

person

that was assigned in that column

so we're going to select the field that

is the assigned to

okay

excellent

you can also come down here and

customize your message with a title

you can

write a message

to go out with the email and you can

also select

which field so which columns you want to

show up in said email

we're going to skip that part

i'll save this

so now that workflow is up and running i

can go back to my sheet by hitting this

back button up here

now let's say down here that

i assign charlie roy there we go

so you want to hit save

and also refresh the sheet

and smartsheet will run your automation

now the person in this assign to column

will get an email that email is going to

look something like this

here we go

it says you have been assigned to a task

mark layout on top plate charlie roy and

as you can see in this option here i had

selected some of the fields that were

going to show up

okay

so you can also use automations to

reduce some of the manual data entry

that you need to do

so let's say that there is an additional

task before scheduling a crane that we

need to go through

i go up here and let's say i add a row

and this is my

new task

now i can use automations to

pre-populate some of these fields so

that i don't need to go through and

check off all of them i'm going to go

through how to do that for you

so

we'll go back up to our automations tab

and we're going to once again create a

workflow from scratch

i will note that i personally just have

a preference of creating the workflow

from scratch

versus using the templates

okay

so we're gonna create some default

values

when rows

are

added okay

so i'm going to leave the trigger just

as is i'm not going to get specific here

so it's just when rows are added or

changed actually i'm going to make this

just when rows are added

and i'm going to

just leave it as when any field changes

this workflow is going to run

when that row is added so that's the

trigger you can also

set these workflows to run say on an

hourly basis or a daily basis or a

weekly basis so you can set your set

your schedule that way if you don't want

to

get emails all the time you can get them

all in one lump say at the end of the

day

so i'm going to come down here

and

rather than say alerting someone like

last time we're going to come down

and we're going to make a change in our

sheet and we're actually going to change

a cell value

so when i add a new

[Music]

row to the sheet

i'm going to want the percent complete

just to populate to zero

because it hasn't been started yet i

just added it so something to note when

you are

changing a cell value

is

that as you can see here it says enter

the text you would like the sales value

to change to when this automation runs

so what you enter here is text

so when i enter the number zero

it's actually reading it as

text versus a value

all right and this comes in

um in play importantly if you have a

column that is uh for instance a percent

complete column so if i put in say

100 here

it would show up as

100 percent

and if i put in one it would show up as

one percent however

if we go back to our sheet

i'm gonna

save this here

okay

back

and i come over to the percent complete

column

and let's say i put in 100

sorry

basically if you enter a value in these

percent complete columns

uh versus having the

um

like the actual number that you want to

appear sometimes when you enter say like

100 that would show up as

10 000

if you understand what i mean so

versus putting like 0.3 or 0.4 those

would then transfer to 30 or 40

so let's go back to our automation

go back to that one that we had just

created

where rows are added

we're going to come down to

change a cell value

so now when rows are added we're going

to put percent complete to zero

there we go

and we're going to change we can select

other columns that we want to change as

well

so we're going to put the

status

as not started

there we go

and we'll

also

put the task type

we'll default that to

task

of course this can be changed at a later

date

so when rows are added and any field

changes

we're going to change the cell value in

the percent complete column to zero

we're going to change the value in the

status column to not start it and we're

going to change the value in the task

type column to task

hit save here

there we go

always good practice to name your

workflow as well

so

there we go next one

i'll go back to my sheet

and now we're going to try adding a new

row again

i'll insert above

i'm going to put

you

task one hit enter

now

the automation

does not run

until you save and update the sheet

and sometimes even then we'll see here

it might take just a couple of minutes

for it to come along but as you can see

here

now these cell values have updated

without me having to go in and manually

do it

as you can see these ones are still

blank that is because this task was

added prior to

the automation being in place

i'm just going to get that out of there

excellent

all right so another thing that we

really like to do at echo is we like to

record dates this is great for tracking

down the road

this is another um

automation option that you have

so we're going to go in and create a new

automation and what it's going to do is

once this

um status changes to complete

we're going to record a date over here

in the complete date record column

back over

once again we can head up to our

automations

we're going to create a workflow from

scratch

now we're going to change our trigger

because our trigger is going to be a lot

more specific with this one

so

our trigger is going to be when

the status column

changes to

complete

we can come down here for our actions

and one of the options is to record a

date

now it's important that you do have your

columns

set up as date columns

ahead of trying to build your automation

because

you have to select from a list of

date columns

so we're going to put it in the complete

date record

there we go

i'm going to put

status

complete

record

save

excellent so once again we can go back

to our sheet here

now

i come over and i change

my status to complete

you can see our conditional formatting

running

i'll refresh it

and i should have a date recorded as you

can see 8 24 22 that's today

in the complete record

complete date record

excellent

right

so you can also create automations as

well so like as you can see here

um whoever is in here may have updated

some of these tasks to 100

but you can see the discrepancy where

the status still says that it is in

progress

so you can create automations as well

just so that you don't run into these

inconsistencies in your project plans

and create another workflow

that

when percent complete

equals

100 percent

we can change status

to complete

once again

we can do this when

rows are changed

and when the

percent complete changes to

100 percent

change your cell value

in the

status column

do complete okay

there we go

back

okay

so something to know with automations

is that they will run after you have

created them

so as you can see we still have this

discrepancy here

that's because our trigger

is when this is changed to one hundred

percent

so this was already at one hundred

percent so the trigger didn't run and

these are not going to update to

complete

however if i come down here

and i mark this as

100

save and update it

you'll see that the status will then

change to complete

all right

next one so any questions while i still

have smartsheet open here or everybody

follow along okay

all righty

great well thanks everybody for uh

joining in on this webinar today i hope

that you got something out of it like i

said we will be making a recording of

this webinar available to all of you

please feel free to reach out if you

have any questions

regarding conditional formatting or

automations and how you can use that

within your smartsheet

if you enjoyed this webinar please check

out some of echo consulting's upcoming

webinars we have one on risk management

techniques utilizing a crate log that's

coming up on september 7th

and

we have another one coming up on

september 21st that is best practices

for leading impactful meetings

we will uh also make a copy of this

slide deck available to you all at the

end so you have this information here

thanks for joining