Smartsheet Tutorial: How to Create Automations, Build Workflows, & Set Up Conditional Formatting
Summary
TLDRIn 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
π 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.
β¨ 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.
βοΈ 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.
π 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.
π 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.
Mindmap
Keywords
π‘Conditional Formatting
π‘Automations
π‘Project Plan
π‘Formatting Bar
π‘Triggers
π‘Actions
π‘Workflow
π‘Stakeholders
π‘Manual Data Entry
π‘Streamline Processes
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
Browse More Related Video
Google Sheets - Conditional Formatting
Incident management with ServiceDesk Plus Cloud - Part I
Interactive Excel Dashboard Tutorial in 3 Steps (+ FREE Template)
Automate Content Creation and Scheduling with this Workflow (Make.com and Airtable Tutorial)
Outlook Tips & Tricks to Take Control of your Inbox
Resource Allocation and planning using Excel and Pivot Tables With Demo | Planning Excel Template
5.0 / 5 (0 votes)