Up next in 10
Excel Automation with Power Automate : Automate Your Business Processes - Ep. 3
0 views
Jun 20, 2025
Unlock the potential of Excel automation with Power Automate! This session guides you through performing various operations in Excel workbooks, making data management faster and smarter. Perfect for boosting productivity and eliminating manual tasks. 📺 CSharp TV - Dev Streaming Destination http://csharp.tv 🌎 C# Corner - Community of Software and Data Developers https://www.c-sharpcorner.com #CSharpTV #csharpcorner #TheCloudShow #CSharp #interview 🎙️ New to streaming or looking to level up? Check out StreamYard and get $10 discount! 😍 https://streamyard.com/pal/d/5348850450038784
View Video Transcript
0:00
heat hey heat hey heat
0:06
[Music]
0:13
[Music]
0:29
[Music]
0:39
mahindra muted greetings everyone welcome again to business automation so
0:44
where we talk about that uh how to automate your business process
0:50
so on every session we take uh you know one topic and we discuss the detail uh
0:56
on that topic uh how we can use uh you know the automation
1:02
to you know act uh do this automation on your day-to-day activity or whatever
1:07
task you are doing on your business so that we talk here on that business
1:13
automation so so today we are going to talk about uh specifically on that excel
1:18
how we can automate our processes if you are using Excel for your day-to-day
1:23
activities okay so here is the agenda for uh today uh so we will start with
1:28
the basic introduction and then we will see what are that excel triggers and you know
1:34
accents available and then we will see what are the most frequently used uh you
1:41
know triggers or actions in the excel that we can utilize to do these
1:46
automations and we will do all the demo so whatever topic we will discuss here so we will be doing demo on
1:59
this okay so before jumping on the main topic let's have a quick introduction
2:05
about ourself so myself Mahindra and I have my friend Sep with me so we both
2:11
are Microsoft MVPs and we have more than 10 years of experience in Microsoft tech
2:16
and we have both our C# corner MVPs as well okay so let's jump into our uh
2:26
first topic introduction so you know Excel you know
2:32
today we are going to talk about that how we can do it automation if you are using Excel okay so there are lots of
2:40
small business owner or medium-siz business owner they are still using Excel for their day-to-day activities
2:47
and keeping their data into Excels okay so whether it's a their sales data
2:52
whether it's their product information whether they are employee information or their order history product history so
2:59
they are using Excel very much to store all these data locally okay so they are
3:05
not using any uh any kind of uh relationship management or any kind of
3:10
uh you know cloud database to store the data so they are utilizing Excel right
3:16
but when we are using Excel and we are storing the data sometime we are same
3:21
data we are storing on the different different places which create inconsistent uh you know inconsistency
3:28
in the data right and that data can be wrong and definitely when we have wrong
3:34
data the output is going to be wrong as well right so let's take an example that we want to do some kind of sales
3:41
forecasting right and we have different uh uh excel seed where we have kept our
3:47
sales data but those data are not matching right when the end of the day
3:52
you want to generate some kind of report from the taxel that the number can be
3:57
wrong there right and it won't give you that actual output or actual performance
4:03
that how your sales is going on and actual what is the actual numbers right so there are chances that the data will
4:10
be wrong and there will be inefficiency in the day-to-day operations as well
4:15
Right so now to streamline this we can utilize power automate to all that
4:22
actions all the data that we are doing we can do that automation using with the
4:28
power automate okay so we can use power automate and we can perform different actions automatically or whatever task
4:35
we are doing whether it's say you know storing the data or fetching data from outside and putting on there or
4:42
generating that report those kind of thing we can easily do using the power automates and that's what we are going
4:48
to talk today so we will show you how we can leverage different kind of actions
4:54
that is part of the power automate to do these automation with the excel right so
5:01
these excel automations depending on the requirement whether it's you know inserting the data or retrieving the
5:07
data updating the data or deleting we can have you know different options for example maybe we can you know schedule
5:14
some job right which will be running on the background or on a scheduled manner there and during that particular point
5:21
of time it will perform some set of action that we will uh configure in the power automate right so those those when
5:29
when we are doing these tasks using this power automation it means that there is no human you know basically we are not
5:37
doing anything or feeding any data manually there everything is happening automatically right so based on the our
5:44
recorded step based on the action that we have used in the power automate okay
5:49
so then the output obviously the output will be much better than the manual effort that we were doing earlier in the
5:56
excel Okay so
6:02
now let's see that what are the triggers and actions that is available on the if
6:09
we want to utilize you know excel connector on the power automate so we
6:14
can see here that we have basically not much triggers specifically from that
6:20
excel site so we have one trigger that is available which is kind of a manual trigger that when you are selecting some
6:26
row so let's take an example you have a workbook and under that workbook you have a seat and if you you know want to
6:32
do some kind of automation like you know if I select some row I want to perform some action okay so that type of trigger
6:39
that is available here so we can select that row and this trigger will be you
6:44
know executing and after that we can utilize these number of the action so these are the it's not full list but
6:52
these are the action that is available as a part of the excel and these excels
6:58
you know have like different type of you know things like for example if you want to you know create a workbook or you
7:05
want to you know create table or if you want to uh write some data onto that excel worksheet or you want to get that
7:12
row so those kind of thing that is available on the actions and as we said
7:18
earlier when we are working on the power automate these action and trigger these are the more core component here so
7:24
whenever you select any connector okay the connector basically which help you to connect these between two
7:31
applications so here we we are talking about two applications one application that is basically Excel and another one
7:37
is the power automate right both are basically part of the our Microsoft uh
7:44
stack only Microsoft cloud only so we are like for example this is a excel online right so both are on the same
7:51
tenant so we are now connecting these two applications using the connector
7:56
that is available on the power automate so when you will select that connector then you will be able to see these two
8:02
options here trigger and the actions okay and using these uh we can perform
8:09
uh as I said that we can do all your day-to-day activities depending on that what is your job role what what you do
8:15
in you know day-to-day activities so you can perform those actions and while
8:20
working on these Excel Excel act as a database right so it means that we are
8:28
considering here that Excel workbook or that Excel seat that basically will have a table so and under that table that
8:36
will be basically a particular boundary that where we can say that okay up to this range to this column range this
8:42
will be considered a one table and under that one table we can have different rows and then on the different rows we
8:49
can perform different action that is part of these power automate connectors
8:55
okay so now let's see what are the most frequently used actions that when we
9:02
work with the automation on what we do that okay so we have here get worksheet
9:09
and get tables so these are commonly used you know action that here we can
9:14
use so these are basically if there is a worksheet that is part of your you know you have some uh location where you have
9:22
stored your file it could be sharepoint it could be you know one drive where you
9:28
have you know this worksheet and you want to fetch that worksheet and you want to fetch the data basically that is
9:34
part of the table it means you want to fetch the table that is part of the worksheet okay so these both actions
9:42
shares same set of parameters here using those parameters we can get these files
9:48
and the tables so the first parameter that is here is the location which basically represent at where you have
9:56
stored your file whether it's a sharepoint one drive so that basically is the location so when you click on
10:03
that location and and we will show you this on the demo when we will do demo
10:09
you will see a drop-down there under that dropdown you will get different options and then when you will select
10:16
that option okay let's take an example that I'm saying uh you know on shareepoint I have stored my file so you
10:22
will select shareepoint and then next is the document library so location is
10:29
basically the place where you have stored that And under that location you may have different kind of directory
10:36
right so depending on your you know job profile or depending on your requirement
10:41
let's say you have different folders one folder you have created for orders one folder you have created for product one
10:48
you know you have created for maybe your uh you know reports or maybe there are
10:53
some other uh marketing folder is there so you could have a different kind of folder structure there so document
11:00
library basically you know target that document uh structure where you have
11:05
stored that file okay so this location and after that you select that particular folder that this is the
11:12
folder where my file is there so you can select that and then you will select
11:18
that file okay so under that folder you may have different different files but
11:23
you want to work with the you know one particular file here under these different taxims so under the file you
11:29
will select okay I want to work with this particular file i want to get the
11:34
worksheet that is part of this particular file and I want to get tables that is part of this particular
11:41
worksheet uh you know that we will mention on this file okay so this is
11:48
what we have here get worksheet and get tables using that we can get our files
11:54
and the table that is part of the that file
11:59
now so as I said that this Excel basically here as you know it's playing
12:06
role of a database right it it act as a database here so in excel we can also
12:12
define tables depending on the row and column so you can let's say for example
12:17
if you are working with excel so if you have some set of column and rows you can select those range and then under that
12:24
properties you can define that okay this will be the name of this particular range so you give it a name that okay
12:32
this this range will be called as this table okay so similarly if we want to
12:38
create the table using the automation using the power automate so we have these set of parameters that we need to
12:45
configure so these are again the common parameter that we have location and document library file is also same here
12:52
that you know on which file we want to create this table then here is the table
12:58
name okay so when we are creating the table you need to define that what name you want to give to your table that will
13:04
be for that particular range and that is the another you know parameter here
13:10
where we define that you know starting from your first column first row up to
13:15
there that okay which range you want to cover into the table and that uh you
13:20
know you want to use to create your table right and then next parameter is the
13:28
column names right so we have these as we are talking about the table table is is basically combination of row and
13:35
columns right So we have horizontal rows and then we have a vertical columns so
13:42
we said that okay up to this column maybe there are for example you know five or 10 columns are there into your
13:48
seed but maybe you just want to utilize three column or particular columns so you can say that these are the columns
13:55
that I want to use for creating my table so you can define name of the columns
14:01
and then you can use uh you know here semicolon to just separate them right for example I want to use name product
14:08
name okay uh product number so these type of uh product column when I will be
14:14
writing I can use semicolon here to segregate these column differently as a
14:20
part of the table okay so that's what we have here create table which we can use
14:26
to create table under the workbook
14:33
now next is mostly used is the get row so when you have Excel file you have you
14:40
know table there under the table you have multiple records there and one record means one row okay and you want
14:47
to fetch that data maybe you want to read that data and you know want to process that data into power automate so
14:55
it could be depending on that maybe you want to get those uh you know row and you want to do some kind of checking
15:00
that okay what data is there as a part of the row so those kind of thing can be
15:06
done using this get row so again we have these uh these are the three four things
15:13
this is the same from the last one location where you have that file in the
15:18
folder structure basically the document library and file is the name of file now
15:24
here because now we are fetching the one record right and that record will be
15:30
part of a particular table so you need to tell that okay what is the table name and from which table you want to get
15:36
that row and when we are fetching that particular row so in the table there can
15:42
be multiple records right so there there could be 10 record 100 record thousand
15:48
records but how you will identify that okay but which particular record you
15:54
want to fetch using these set of columns so you need to define okay what are the
16:00
key column that you want to use for the comparison so that you know power automate will go there into that file
16:06
into that table and it will look for that particular column and then you will
16:11
provide value of that column so this will be basically the ID so just take an
16:17
example that maybe we have a unique identifier there and maybe there is a serial number right and based on the
16:24
serial number I want to fetch that particular row so you can use those serial number as a key column and the
16:30
value of the serial number will be known as the key value here that will then
16:35
power automate will go and it will compare the data and get the data and it will return to you
16:42
right and there is another thing here datetime format so this basically a daytime format is you know used for if
16:49
there is any kind of you know date time format column that you have used into your table and how it will be returned
16:57
so Excel have its own way to storing that date column so there are you know like a serial number or there is a ISO
17:04
format you know how it will return so there you can define that okay when you
17:09
are returning the data in the uh the if there is a date column how it will be
17:14
returned so that for that we use this datetime format okay so that's we have
17:21
here get row now update row so you have that list
17:31
of reports that is part of the table and you want to do some kind of modification
17:36
to those rows maybe you want you have some numbers there you want to do some calculation okay you have some you know
17:44
maybe you want to calculate some discount there or maybe you want to do the what is the you know uh total order
17:51
uh how many products are there depending on the product pricing you want to do that total orders so those kind of thing
17:58
we can do using the update row action and here as well we have this these are
18:04
the common things that we have for example you know location folder structure our file and table and for the
18:12
update as well as there could be multiple records we need to provide some
18:19
kind of key column and key value so that it will be unique and it can go ahead
18:24
and compare that okay these are the data that I can match and when it will find
18:29
that matching row it will take that and it will update the data okay in the pro
18:36
this basically the provide the item property this is that what we want to
18:41
update okay so here we provide these uh type of properties that we want to
18:47
perform and date time format is same uh just like I said that if there is a date then you need to provide what format it
18:54
will be using so that's what we have here update
19:01
row now delete so if you want to delete some data from the Excel file you can
19:09
use this particular accent delete row uh these are again the common things here
19:15
and then we have here the key column and key value it is again because when we
19:20
are going to delete that row it need to identify that okay which is the unique column you know I'm passing the value
19:28
and based on this key value it will go ahead and do the comparison and it will delete only that particular row from
19:35
that table okay because there can be multiple rows here and I don't want to delete everything I just want to delete
19:42
a particular row here so using this action we can delete
19:48
it okay so these these are the common actions that we use while we do
19:55
automation in excels okay now we will showcase this on the demo so I will pass
20:01
mic to my friend uh you know sep and he will showcase all these how we can use these uh you know actions that we you
20:09
know talked about here now and let's see on the demo sep over to you
20:16
yeah thank you very much am I audible yeah I can hear you okay so let me share
20:24
my screen just a
20:32
second so is it visible uh not yet uh yeah it's loading now i
20:39
just want to check that both Yeah both tabs are visible okay so I don't think
20:45
we need to explain power automate because in last two sessions we already explained that how you can use power
20:51
automate UI so now what I'm going to do is I am just going to create a manual
20:57
flow and using that I will add all those actions which already Mahendra explained
21:03
so I'm just going to use this manual trigger flow so the first thing
21:10
is like when you are working with uh uh Excel the first thing you will do you will create a seat so let me find a
21:19
seat and I will go in a flow like I will use uh like the create a seed then I
21:25
will create a table then I will add the data so we will able to understand that
21:30
how you we can use all those actions listed here in Microsoft uh online
21:37
Excel and how we can use in our application as well so the first action
21:44
I'm going to add add that is create a worksheet so worksheet is like whenever
21:50
you are using Excel you can see there are multiple sheets so one seat is one worksheet so we'll create a worksheet to
21:57
create a worksheet the first thing we need to add as a input is the location
22:02
of that particular file and you can see as Mahendra already explained that we have groups here then we have the all
22:09
the Sharepoint sites available so if you are a part of a particular group or you are a part of any Sharepoint site that
22:15
is what you can see here so if your file is in a particular group folder or if
22:21
your file is in a sharepoint folder documents that is what you can access from here in my case I'm going to use
22:28
one drive and then the the folder we need to select here
22:34
document library that is one drive and then we need to select the path of that
22:41
particular file so I have my file here in Excel
22:47
demo and I will select this file and then the name uh whatever name you want
22:54
to give to your worksheet that is what you will give here so I am giving demo seat and then I will save
23:03
it so let me test and see that this is working or not so I will
23:11
taste continue and run so now you can see this
23:17
is running so let me open that seat and show you so you will have better
23:22
understanding that what we are doing so I'm going
23:27
here and this is the seat that is what we are using let me refresh it so you can see
23:35
that so right now I can't save but let me refresh one or two times and see that
23:41
we have that particular seat or
23:51
not so you can see this is here so the next the the this create
23:58
worksheet is working well so now the next step is I'm going to create a table
24:04
in this seat so what I will do is I will add a action to create a table so you
24:12
can see this action create table and already Mahendra explained like the all these things like how you can create
24:18
that table so you can select the location then you can select the the folder and then the file
24:27
we select the same file so I'm using this and then here uh
24:37
in my seat I have two seats and uh in the same way when we are working with
24:43
the Excel seat we add the seat name and then we add the range so that is what we
24:48
need to add here so I will just use this particular seat name the demo
24:54
seat so we'll add the demo seat here and then we'll add the range that is
25:01
starting from D1 and then we'll use the like whatever
25:09
name you want to give let me add so we'll give the table one and then the
25:16
columns so the columns I'm going to use here that
25:22
is the ID We will add the ID ID and then the next column
25:30
is name then use
25:37
age and then gender so these are the columns I'm
25:44
adding here so let me save and run this flow one more time and see that it is
25:49
working or not so I'm just going to run it manually
25:55
run the flow and done so now you can see this is working let's go back to our seat and see that
26:02
so now you can see we have here but uh the error we can see we can't have the
26:07
uh the table header let me refresh it like sometimes it uh it will take a little
26:14
bit time and then you can see so you can see we have the table along with the the header as well
26:21
so the next part is that we are going to add some rows in this particular table
26:27
for that let me delete this so for example when you are working
26:34
with a live data might be you are getting data from API or you are getting
26:39
data from CSV or any third party application in that case um we know that
26:45
we have the compose action in a power automate that is how you can prepare the
26:50
data so I'm going to use the compose action
26:55
here so I will
27:00
use let me delete
27:07
it so operations I will use the pass JSON and
27:14
uh so I will add the sample data here and whenever you are adding a sample
27:20
data at the same time you need to add the schema so you will able to better pass it so I will add the schema here in
27:27
this sample data you can see the the way I created ID name column and gender that
27:32
is what I have here so what I will do is we'll pass this data and insert in
27:38
our created table so let me save it one more time and then we will add a action
27:46
that is add a row so add a row into a
27:53
table and the same thing we need to do we need to select the
27:58
folder then the path so I'm going to select it here and
28:06
the file once you will select the file you can see the table that is what
28:11
we created table one and here you have all those columns which you created in
28:17
that seat and then We need to select the data from here so I will select the ID
28:22
from here then I will select name i will select age
28:28
and gender and let me save it and run
28:35
this so we will test it and run the flow so this is also
28:43
working fine let's go back to our seat and see that we have all those
28:50
records in our seat or not so it's it's still running let's
28:56
wait for And now you can see we have all those uh uh rows
29:02
here so this is also working fine let's go back here and the rest of the actions
29:08
we'll do so let me delete it so we'll go in edit
29:20
mode and we will delete
29:37
this so now we'll add new action here and this is the old like the old UI so
29:45
we'll select the Excel from here and then we will use get row so if you want
29:51
to get a particular row from this seed the same thing we will select the location from here
29:58
so we'll use one drive and then we will use uh the folder the file
30:11
path power automate demo then we'll go to the folder and we select the file
30:17
once you will select the file we will have the table here and the column the key column we need to add we have the id
30:24
and uh based on that like whatever uh one value we need to provide here so
30:31
we'll add this one and then let me save
30:36
it and run it so let's
30:44
test another
30:56
This is running properly and you can see if you go in output and click here you can see
31:02
we got that data that particular record we got the ID we
31:08
provided so get row is also working fine let's go in edit mode and delete this
31:15
action so the next step I'm the next action I'm
31:21
going to add uh that is uh list all row
31:27
present in a table that is very important because whenever you are dealing with Excel sheet most of the
31:34
time we need to get the data and do some operations using that particular data so
31:39
I'm going to use it so list rows present in a table so
31:45
we'll select the location what and then the folder the file
31:56
path the same file we'll select more one more time and the table so this is how
32:03
like the from this particular table all the records you will get after this action and then the next thing I'm going
32:09
to do is that uh either we can show somewhere or so let me post in a
32:16
Microsoft teams so let me select using
32:22
post post a message in teams channel and we'll
32:30
select the channel and then we will select the
32:39
team group and that is demo team and then I will select the channel I have
32:45
there that is general and after that like uh you can
32:51
post like commas separated or
32:57
whatever so ID then so based on your requirement this
33:03
is what I am add adding just for the demo so we can show that we are getting data and pass and posting in Microsoft
33:10
teams let me save
33:15
it and paste
33:25
So run the flow and this is al also working fine so it will take a little bit time
33:32
to post in Microsoft teams let's wait for that and then we
33:39
will see
33:45
here so yeah this is working fine let me go back
33:51
to the Microsoft Teams and see that if we have
34:01
there so you can see like all those uh records you can see that is got posted
34:07
let me check that it is visible or not i think not
34:15
so even though we can click here and we can go to the output and
34:22
see so you can uh see the data we have here and that is what we posted in
34:28
Microsoft teams the next action we can do like uh the update row so let me edit
34:37
this and what we will do is like all the records we are getting using list row
34:42
that is what we will use to update the record so I will add that
34:49
action update row in a same way whenever you are
34:55
adding action the same uh the same parameters you need to give like the the
35:02
path then the folder then the file so let's go go back to our file
35:10
path here and select the file then you will select the
35:16
table and the key is
35:22
ID so key value you can provide uh in our case what I want to do is that I
35:28
just want to update all those records so we can add a condition
35:34
here control and apply to each
35:49
And then I will add this section here
36:18
so we'll go here and we'll try to add the value
36:31
ID demo
36:38
so I'm just updating the demo value gender and then I will save
36:46
it so what I'm going doing is like I'm just taking all those rows here and
36:52
then apply to each and then we are using the ID parameter to update all those
36:59
records let me test and see that it is working or not
37:14
so let's go back here and you can see all those records updated by demo demo demo so this is how like based on like
37:21
whatever data you are getting from some other places and you will take all those rows and then you can update using
37:28
update a row so the next action we can do let me
37:33
edit delete all these so one very important after all
37:40
these things you can see here in a uh Excel online actions that is run a
37:47
script uh whenever you are working with Excel online and if you go here uh you
37:53
can see in automate uh multiple scripts are like multiple files are available
38:01
and you can create a new script as well and whatever scripts you already created that is what you can see here so you can
38:07
see one script file is attached if you want to create a new script you can click on new and you will able to create
38:14
the new script as well so that is what we can see if you go back and you can see we have two scripts so in this
38:21
script let me edit and see so what I'm doing is that I'm just getting that seed
38:26
and then updating this uh from 1 to D7 D9 let me check D9 and then I'm just
38:34
updating all those rows uh background color is green so let me save it so that
38:43
is what you can run so this is very handy like whenever you are working with a excel seat and you want to run some
38:49
kind of script because VBA is not available VBS script is not available
38:54
here so in some cases you can write the JavaScript kind of code and that is what you can run to do some dynamic
39:01
operations in Excel sheet so you can go here if again the same thing we will
39:06
select the file the one drive and then we will select the
39:15
folder the
39:25
path and we'll select that file and then once you will select the
39:31
file all the script attached with that particular file you can see here so you will select and then you will save it so
39:39
this is I'm doing manually but even though you are creating a cloud flow or automated flow or schedule flow uh you
39:46
can uh schedule a script and on a schedule time or whenever you are
39:51
running that flow that script will run on that particular file so let me test
39:58
it and run it and
40:04
done so let's see it is working or
40:09
not so this is working
40:16
and let me refresh so let me first check the script
40:26
all the script go here and run this
40:36
script so this is working fine maybe we need to check or either it will take little bit time and after that update
40:42
because it is working from the file so I'm hoping that it will work from here as well so that is from my side for in
40:50
the same way like all those other actions which is available in Excel
40:55
online you can also run the same you have to select the file then path and
41:01
then the action you want to do so from this
41:07
uh let's hand over the stage to mahenda and if you have any question like
41:12
whoever watching we can you can ask the questions
41:20
Thank you Sundep thank you explaining all these uh still automation actions and that's
41:28
uh what we wanted to cover in today's session and uh as we said in earlier
41:34
session so if you guys have any any uh pro specific problem or if you have any
41:40
specific use case that you want us to cover in our automation so so feel free to comment on uh you know on the C#
41:48
corner community or YouTube or wherever you are watching this uh so and uh
41:54
thanks for joining us and we will be back with another use case where we will talk about other automation that how we
42:01
can you know make your life easy using the digital transformation with the help of power automate and you can you know
42:08
automate your day-to-day activity depending on whatever your job role is so thank you very much for joining us
42:14
today stay tuned for our next episode thank you
42:19
[Music]
53:36
[Music]
#Business Operations