WEBVTT 00:00.500 --> 00:00.800 So in 00:00.800 --> 00:01.500 this video we're going 00:01.500 --> 00:02.166 to take a look 00:02.166 --> 00:04.600 at the Excel template functionality 00:04.600 --> 00:05.133 to address 00:05.133 --> 00:05.766 some custom 00:05.766 --> 00:08.766 formatting needs that we have. 00:09.300 --> 00:10.566 First, just to talk a little bit 00:10.566 --> 00:11.166 about what 00:11.166 --> 00:13.766 the Excel template functionality is. 00:13.766 --> 00:14.766 What this really does 00:14.766 --> 00:15.366 is allow you 00:15.366 --> 00:16.933 to combine the power of vault 00:16.933 --> 00:18.700 and all the data that's being tracked. 00:18.700 --> 00:19.400 There. 00:19.400 --> 00:22.400 With the flexibility of Excel. 00:22.466 --> 00:22.700 You know, 00:22.700 --> 00:24.200 we often see that customers 00:24.200 --> 00:25.300 have requirements 00:25.300 --> 00:28.300 to generate reports in a specific format. 00:28.333 --> 00:29.700 There could be a lot of different reasons 00:29.700 --> 00:30.066 for that. 00:30.066 --> 00:32.400 Maybe a health authority needs, 00:32.400 --> 00:35.033 a report formatted in a certain way on 00:35.033 --> 00:36.566 a monthly basis, 00:36.566 --> 00:38.333 maybe on a quarterly basis. 00:38.333 --> 00:39.600 You're providing reports 00:39.600 --> 00:41.666 to, management 00:41.666 --> 00:43.000 and need that to be formatted 00:43.000 --> 00:44.433 in certain ways. 00:44.433 --> 00:46.200 So there's lots of different scenarios 00:46.200 --> 00:47.700 that can come up. 00:47.700 --> 00:49.000 And maybe in those cases, 00:49.000 --> 00:50.633 you need to do things like add 00:50.633 --> 00:52.333 your company logo to the report, 00:52.333 --> 00:54.000 or rearrange columns 00:54.000 --> 00:55.666 or change the colors 00:55.666 --> 00:57.100 and other formatting. 00:57.100 --> 00:58.600 Or maybe you just need 00:58.600 --> 00:59.733 to do further 00:59.733 --> 01:00.600 analysis 01:00.600 --> 01:01.466 and Excel 01:01.466 --> 01:04.033 has a lot of functionality for that. 01:04.033 --> 01:04.766 This is something you need 01:04.766 --> 01:06.900 to do on a regular basis. 01:06.900 --> 01:08.933 It can be quite a tedious process 01:08.933 --> 01:12.166 to export the data from vault every time, 01:12.166 --> 01:13.233 and then do all of this 01:13.233 --> 01:15.500 manipulation in Excel. 01:15.500 --> 01:18.266 So to solve this problem, Excel templates 01:18.266 --> 01:21.466 basically allows you to set a template 01:21.466 --> 01:22.533 one time, 01:22.533 --> 01:24.600 upload, upload it to vault, 01:24.600 --> 01:26.500 and then download that template 01:26.500 --> 01:28.600 in the format that you've defined 01:28.600 --> 01:31.066 with any subsequent export that you do. 01:31.066 --> 01:32.133 And it'll always, 01:32.133 --> 01:33.000 you know, basically 01:33.000 --> 01:33.666 pull whatever 01:33.666 --> 01:36.133 the current data from vault is. 01:36.133 --> 01:37.500 And this allows you to do things 01:37.500 --> 01:38.166 like custom 01:38.166 --> 01:39.166 formatting, 01:39.166 --> 01:40.633 manipulating the data, 01:40.633 --> 01:42.400 creating custom charts. 01:42.400 --> 01:44.100 And you can even use macros 01:44.100 --> 01:47.100 in VBA in Excel as well. 01:47.100 --> 01:48.000 So here I'm 01:48.000 --> 01:50.166 in a clinical operations vault, 01:50.166 --> 01:51.100 and I'm looking 01:51.100 --> 01:52.500 at a report here 01:52.500 --> 01:55.133 of our monitoring events. 01:55.133 --> 01:57.500 Now let's say in this scenario 01:57.500 --> 01:59.533 I have a report that I need to be able 01:59.533 --> 02:00.300 to provide 02:00.300 --> 02:02.100 on monitoring event metrics 02:02.100 --> 02:04.400 to my management. 02:04.400 --> 02:07.233 Who may not have access to vault. 02:07.233 --> 02:08.966 And so I don't want to just export 02:08.966 --> 02:12.900 sort of this Excel file as is and provide 02:12.900 --> 02:13.500 that to them. 02:13.500 --> 02:14.433 I want to provide them 02:14.433 --> 02:15.500 with some information 02:15.500 --> 02:16.266 that's, 02:16.266 --> 02:19.266 more presentable and easy to digest 02:19.466 --> 02:22.133 and not have to manipulate that in Excel 02:22.133 --> 02:23.100 over and over again 02:23.100 --> 02:24.166 when I need to provide 02:24.166 --> 02:25.733 these updates to them. 02:25.733 --> 02:26.533 So in this case, I'm 02:26.533 --> 02:28.000 going to use Excel templates 02:28.000 --> 02:29.633 to be able to do that. 02:29.633 --> 02:32.233 Now I've already got an Excel template 02:32.233 --> 02:33.633 set up on this report. 02:33.633 --> 02:35.666 So you can see in Advanced options 02:35.666 --> 02:37.900 it says Excel template. 02:37.900 --> 02:40.200 So first we're going to just start 02:40.200 --> 02:41.000 by looking at 02:41.000 --> 02:42.700 what is the output look like 02:42.700 --> 02:44.733 and how I've set this up. 02:44.733 --> 02:46.066 And then we'll talk through 02:46.066 --> 02:47.566 how I actually went about that. 02:48.600 --> 02:50.533 Now to export to an Excel template. 02:50.533 --> 02:52.433 When one is in place 02:52.433 --> 02:54.866 you go to the action menu on the report 02:54.866 --> 02:57.566 you choose Export to Excel. 02:57.566 --> 02:59.800 And if a template is associated to 02:59.800 --> 03:00.500 the report 03:00.500 --> 03:01.800 will automatically see 03:01.800 --> 03:03.766 this option available. 03:03.766 --> 03:05.400 And so I can select that 03:05.400 --> 03:08.000 and then hit export. 03:08.000 --> 03:08.833 All right so here 03:08.833 --> 03:11.166 I've got my report open. 03:11.166 --> 03:13.966 And I'm on the export tab initially here. 03:13.966 --> 03:15.466 And essentially what 03:15.466 --> 03:17.400 this is is all of the data 03:17.400 --> 03:19.200 from the report. And vault. Right. 03:19.200 --> 03:21.200 All of the columns in the report 03:21.200 --> 03:23.133 and vault are here. 03:23.133 --> 03:24.566 Plus any other columns that 03:24.566 --> 03:25.700 maybe I defined 03:25.700 --> 03:28.700 in the Excel template itself. 03:28.866 --> 03:30.233 But what you can see here is 03:30.233 --> 03:32.833 I was able to actually format this 03:32.833 --> 03:33.600 as a table 03:33.600 --> 03:34.400 so that it's got, 03:34.400 --> 03:36.633 sort of a nicer visualization 03:36.633 --> 03:38.266 of the data here. 03:38.266 --> 03:39.200 And I was even able 03:39.200 --> 03:42.033 to add my company's logo to this. 03:42.033 --> 03:42.733 Again, 03:42.733 --> 03:43.500 to kind of make this 03:43.500 --> 03:44.933 a little bit more official, 03:44.933 --> 03:47.166 if I'm sharing this information, 03:47.166 --> 03:49.866 maybe to, some of my management at 03:49.866 --> 03:50.500 my company. 03:51.533 --> 03:52.566 Now, if I come over here 03:52.566 --> 03:54.666 to the pivots tab, 03:54.666 --> 03:55.233 you'll notice 03:55.233 --> 03:56.533 what I was able to do in 03:56.533 --> 03:58.800 my Excel template is define 03:58.800 --> 03:59.666 a bunch of different 03:59.666 --> 04:00.433 pivot tables 04:00.433 --> 04:03.500 to kind of summarize the, data here. 04:03.800 --> 04:05.233 So and I was able 04:05.233 --> 04:07.733 to kind of give each of these a title. 04:07.733 --> 04:09.300 So you know for instance 04:09.300 --> 04:12.000 this first one is basically showing me, 04:12.000 --> 04:14.300 do I have any monitoring events 04:14.300 --> 04:16.433 broken down by study 04:16.433 --> 04:19.233 that required, more than one 04:19.233 --> 04:20.633 approval cycle? 04:20.633 --> 04:21.700 We actually don't 04:21.700 --> 04:24.100 all of those were just one. 04:24.100 --> 04:25.500 But if we look at, 04:25.500 --> 04:26.966 the number of monitoring events 04:26.966 --> 04:28.433 that had more than one review 04:28.433 --> 04:30.000 cycle by study, 04:30.000 --> 04:31.366 we can see, for instance, 04:31.366 --> 04:33.266 that this study had one 04:33.266 --> 04:35.633 that was only one review cycle, 04:35.633 --> 04:36.933 and this one had one 04:36.933 --> 04:39.400 that was more than one review cycle. 04:40.500 --> 04:43.066 And I was able to define pivot tables, 04:43.066 --> 04:45.566 basically grouping and summarizing 04:45.566 --> 04:47.233 all of the different data 04:47.233 --> 04:48.733 that I'm interested in 04:48.733 --> 04:51.733 or that I need to provide internally. 04:52.066 --> 04:52.333 All right. 04:52.333 --> 04:54.600 So then all of these pivot tables 04:54.600 --> 04:55.733 are actually feeding 04:55.733 --> 04:56.633 some charts 04:56.633 --> 04:59.033 that I've set up in the template here 04:59.033 --> 05:00.300 as well. 05:00.300 --> 05:01.033 So again I'm 05:01.033 --> 05:02.200 using kind of my 05:02.200 --> 05:05.000 my company logo on each page. 05:05.000 --> 05:06.966 But basically here, 05:06.966 --> 05:08.500 I've created some different charts 05:08.500 --> 05:10.600 based off of those pivot table groupings. 05:10.600 --> 05:11.700 So I can see things 05:11.700 --> 05:13.800 like the average number of days 05:13.800 --> 05:16.800 to approving the monitoring visit report 05:16.866 --> 05:18.800 broken down by study, 05:18.800 --> 05:20.333 the average number of days 05:20.333 --> 05:22.166 to start approval, 05:22.166 --> 05:24.800 being able to visualize, review 05:24.800 --> 05:25.933 cycles again, 05:25.933 --> 05:27.333 how many monitoring events 05:27.333 --> 05:30.333 had just one review cycle by study, 05:30.333 --> 05:33.166 versus how many, monitoring events 05:33.166 --> 05:36.900 had more than one, on a given study. 05:37.600 --> 05:39.933 So all of this is information 05:39.933 --> 05:40.566 that, you know, 05:40.566 --> 05:43.566 allows me to easily be able to provide 05:43.933 --> 05:45.500 a, formatted 05:45.500 --> 05:46.933 report to, 05:46.933 --> 05:47.966 you know, maybe others 05:47.966 --> 05:49.766 within my organization 05:49.766 --> 05:52.900 who don't have access, to to vault. 05:53.300 --> 05:55.100 And it allows me to be able to do this 05:55.100 --> 05:56.533 in a very repeatable way. 05:56.533 --> 05:56.866 I'm not 05:56.866 --> 05:58.333 having to do all of this 05:58.333 --> 06:00.666 Excel manipulation 06:00.666 --> 06:03.600 every time I need to provide this report. 06:03.600 --> 06:06.000 It's simply pulling the data from vault 06:06.000 --> 06:07.533 and then populating 06:07.533 --> 06:09.066 all of the different formatting 06:09.066 --> 06:10.333 that I'm seeing here. 06:11.800 --> 06:13.500 Okay, so now we're back in vault, 06:13.500 --> 06:15.300 and I want to take, some time here 06:15.300 --> 06:16.400 to talk about 06:16.400 --> 06:19.400 how we set up this Excel template. 06:19.433 --> 06:20.366 So I'm going to go into 06:20.366 --> 06:23.366 edit mode on this. 06:23.400 --> 06:24.200 Now there are a few things 06:24.200 --> 06:25.000 I just want to note. 06:25.000 --> 06:25.933 I'm using, 06:25.933 --> 06:27.600 formula fields 06:27.600 --> 06:29.900 and conditional fields here. 06:29.900 --> 06:32.133 To create some additional data 06:32.133 --> 06:34.100 within this report. 06:34.100 --> 06:35.066 So you'll see 06:35.066 --> 06:36.633 I've got a few formula fields 06:36.633 --> 06:38.100 that are calculating, 06:38.100 --> 06:40.100 essentially a timelines, right. 06:40.100 --> 06:40.833 How many days 06:40.833 --> 06:43.333 did it take to start the approval process 06:43.333 --> 06:46.200 from when the visit actually occurred? 06:46.200 --> 06:49.300 Or how many days did it take to approve? 06:50.400 --> 06:51.866 So we've got some metrics 06:51.866 --> 06:53.200 that we're calculating here 06:53.200 --> 06:55.200 within vault itself. 06:55.200 --> 06:56.400 And then I have a couple 06:56.400 --> 06:58.266 of conditional fields here 06:58.266 --> 07:00.333 that are creating some grouping right. 07:00.333 --> 07:00.733 So for, 07:00.733 --> 07:03.600 approval cycles is greater than one, 07:03.600 --> 07:06.733 we want to label that as more than one. 07:06.733 --> 07:09.900 Otherwise we would label it as just one. 07:10.600 --> 07:12.233 And then of course I have a filter here 07:12.233 --> 07:15.933 that's just on, final monitoring events. 07:16.666 --> 07:18.166 But adding in these formula 07:18.166 --> 07:19.666 fields and conditional fields, 07:19.666 --> 07:21.366 this is basically giving me 07:21.366 --> 07:21.966 the ability 07:21.966 --> 07:24.633 to have additional columns here. 07:24.633 --> 07:25.600 So you can see like 07:25.600 --> 07:27.000 my time to start approval. 07:27.000 --> 07:29.333 That's one of my formula fields. 07:29.333 --> 07:31.166 My more than one approval cycle. 07:31.166 --> 07:33.300 That's one of my conditional fields. 07:33.300 --> 07:35.000 So these are just allowing me 07:35.000 --> 07:36.766 to create groupings 07:36.766 --> 07:37.333 and perform 07:37.333 --> 07:38.700 additional calculations 07:38.700 --> 07:41.700 right within the report in vault itself, 07:41.800 --> 07:44.800 and then add that information as columns. 07:45.633 --> 07:47.566 Now, to actually leverage, 07:47.566 --> 07:48.333 Excel 07:48.333 --> 07:48.866 templates, 07:48.866 --> 07:49.700 we need to go down 07:49.700 --> 07:52.333 to advanced options here. 07:52.333 --> 07:53.000 And you'll see 07:53.000 --> 07:55.566 we have this Excel template section. 07:55.566 --> 07:56.400 Now we can see that 07:56.400 --> 07:57.700 I have a template 07:57.700 --> 08:00.300 you know already associated here. 08:00.300 --> 08:00.766 But first 08:00.766 --> 08:02.133 I just want to take a look 08:02.133 --> 08:04.233 at this sample option. 08:04.233 --> 08:07.200 So once you've built a report in vault, 08:07.200 --> 08:08.400 if you want to be able to turn that 08:08.400 --> 08:10.000 into an Excel template, 08:10.000 --> 08:11.200 your starting point is 08:11.200 --> 08:12.900 to download this sample. 08:14.500 --> 08:16.800 So here we see the sample file. 08:16.800 --> 08:17.933 And there's a couple of tabs 08:17.933 --> 08:19.000 that are going to show up here 08:19.000 --> 08:20.433 by default. 08:20.433 --> 08:22.800 The first is the cover page here. 08:22.800 --> 08:25.000 And this is just a default cover page 08:25.000 --> 08:27.100 that's going to use tokens 08:27.100 --> 08:29.233 to pull in information about the report, 08:29.233 --> 08:32.233 such as the name when it was exported, 08:32.233 --> 08:33.800 when it was run. 08:33.800 --> 08:35.366 All of this is information 08:35.366 --> 08:38.366 that it's pulling in from, vault itself. 08:39.366 --> 08:42.366 And then if we go over to the export tab, 08:42.533 --> 08:44.100 what we're going to see here 08:44.100 --> 08:44.633 is all 08:44.633 --> 08:46.466 the columns in our report 08:46.466 --> 08:48.200 are going to show up here. 08:48.200 --> 08:49.566 But they're going to show up 08:49.566 --> 08:51.533 with tokens in them. Right. 08:51.533 --> 08:53.100 Because we're creating a template here. 08:53.100 --> 08:55.566 There's no real data in it yet, 08:55.566 --> 08:57.166 but these tokens are essentially going 08:57.166 --> 08:58.533 to populate that 08:58.533 --> 08:59.800 real data anytime 08:59.800 --> 09:01.533 somebody runs this report 09:01.533 --> 09:04.366 and exports to the Excel template. 09:04.366 --> 09:05.766 So really what we're seeing here 09:05.766 --> 09:08.266 is all of those columns 09:08.266 --> 09:10.233 just automatically have a token 09:10.233 --> 09:11.433 within them. 09:11.433 --> 09:12.433 And then, you know, 09:12.433 --> 09:15.266 we can essentially take this information 09:15.266 --> 09:16.733 and start formatting 09:16.733 --> 09:18.400 and creating pivot tables 09:18.400 --> 09:19.533 and things like that. 09:21.333 --> 09:22.100 So let's go ahead and 09:22.100 --> 09:23.333 start looking at how 09:23.333 --> 09:24.566 I can apply 09:24.566 --> 09:27.200 some of this custom formatting here. 09:27.200 --> 09:28.800 Now the first thing I'll highlight is 09:28.800 --> 09:32.033 I added my logo for my company 09:32.333 --> 09:33.866 into this file. 09:33.866 --> 09:34.800 And the way that I did 09:34.800 --> 09:36.800 that was really quite simple. 09:36.800 --> 09:39.900 I basically came up here on this tab 09:40.900 --> 09:42.600 and I added in some rows, 09:42.600 --> 09:44.000 some just blank rows. 09:44.000 --> 09:45.300 All I'm trying to do here 09:45.300 --> 09:48.800 is just give some space, for that logo. 09:49.333 --> 09:50.666 And then I dragged that 09:50.666 --> 09:53.666 image file into the Excel file. 09:54.566 --> 09:57.566 So here I'll just drag an image in 09:57.566 --> 09:58.400 and we can see that 09:58.400 --> 10:00.233 I've put a little logo here 10:00.233 --> 10:01.966 that I can resize 10:01.966 --> 10:05.133 and have in my Excel template here. 10:06.933 --> 10:07.333 And again 10:07.333 --> 10:09.700 I basically just use that on every tab 10:09.700 --> 10:11.000 that we ended up creating. 10:12.300 --> 10:14.166 The other thing I can do here, right. 10:14.166 --> 10:17.033 So these columns are really, 10:17.033 --> 10:18.900 you know, fairly simple format, right? 10:18.900 --> 10:20.300 It's just the column headers 10:20.300 --> 10:22.800 and the tokens from vault. 10:22.800 --> 10:24.633 But as we saw in the template 10:24.633 --> 10:25.800 that I had set up 10:25.800 --> 10:27.700 and actually formatted this a bit, 10:27.700 --> 10:29.200 so what I did there to do 10:29.200 --> 10:30.366 that is 10:30.366 --> 10:33.233 I essentially highlighted everything. 10:33.233 --> 10:35.700 And I use the format 10:35.700 --> 10:38.700 as table in Excel 10:38.766 --> 10:41.766 and apply that here. 10:41.866 --> 10:43.966 Now one of the things to note with Excel 10:43.966 --> 10:46.933 templates is in the template itself. 10:46.933 --> 10:49.233 We don't yet know how many rows 10:49.233 --> 10:50.600 are actually going to show up. 10:50.600 --> 10:53.400 And every time we run this report, 10:53.400 --> 10:54.900 we're going to have a different number 10:54.900 --> 10:56.900 of rows potentially. 10:56.900 --> 10:59.400 So when we set the table here, 10:59.400 --> 11:02.400 there is a range to that table. 11:02.800 --> 11:03.700 And in order for it 11:03.700 --> 11:05.266 to maintain this formatting 11:05.266 --> 11:07.000 all the way down, 11:07.000 --> 11:08.300 the easiest way 11:08.300 --> 11:11.533 to ensure that that happens is actually 11:11.533 --> 11:14.033 to go to your table tab here 11:14.033 --> 11:15.966 and just say convert to range. 11:18.066 --> 11:20.366 This will take the filters off. 11:20.366 --> 11:21.100 But now when 11:21.100 --> 11:23.100 this is actually run from vault 11:23.100 --> 11:25.600 and populated with real vault data, 11:25.600 --> 11:27.166 you're going to maintain 11:27.166 --> 11:27.633 sort of this 11:27.633 --> 11:29.500 formatting for every row 11:29.500 --> 11:31.900 that shows up in this report. 11:31.900 --> 11:32.366 And of course, 11:32.366 --> 11:33.766 somebody can apply filters 11:33.766 --> 11:36.133 and things if needed. 11:36.133 --> 11:37.933 So those are some pretty basic things 11:37.933 --> 11:40.400 I can do in terms of the formatting here. 11:40.400 --> 11:43.400 But let's talk about pivot tables next. 11:43.466 --> 11:46.466 So in order to create a pivot table here 11:46.700 --> 11:47.166 I would need 11:47.166 --> 11:50.433 to select my entire set of data. 11:50.933 --> 11:53.566 So I'm just going to scroll over here 11:53.566 --> 11:56.233 and choose the entire table. 11:56.233 --> 11:58.233 And then I'm going to use the Insert 11:58.233 --> 12:00.300 Pivot table option. 12:00.300 --> 12:01.700 So one of the really important things 12:01.700 --> 12:02.400 to be aware of 12:02.400 --> 12:03.366 when you're creating 12:03.366 --> 12:05.933 pivot tables with Excel templates 12:05.933 --> 12:08.666 is that we need to update the range here, 12:08.666 --> 12:10.266 because right now 12:10.266 --> 12:11.700 it's really just selecting 12:11.700 --> 12:14.700 sort of these two rows of information. 12:14.766 --> 12:15.900 But when the report itself 12:15.900 --> 12:17.033 is generated from vault 12:17.033 --> 12:18.833 there could be five rows. 12:18.833 --> 12:20.133 There could be 100 rows. 12:20.133 --> 12:20.466 Right. 12:20.466 --> 12:21.566 It's going to be dynamic 12:21.566 --> 12:24.000 based off of the data that's in vault. 12:24.000 --> 12:25.366 So we need our range here 12:25.366 --> 12:27.466 to be dynamic as well. 12:27.466 --> 12:28.466 And so the way that we do 12:28.466 --> 12:29.800 that is you can clear out 12:29.800 --> 12:31.866 what's there by default. 12:31.866 --> 12:32.666 And then 12:32.666 --> 12:34.400 you can use the option here 12:34.400 --> 12:36.300 to select your range. 12:36.300 --> 12:39.033 So I'm going to select the first cell 12:39.033 --> 12:41.333 in the first column that we want. 12:41.333 --> 12:41.866 And you'll see it 12:41.866 --> 12:45.000 says monitoring event Ktms name label. 12:45.700 --> 12:47.100 And then I'm going to add a colon. 12:48.433 --> 12:49.500 And I'm going to choose 12:49.500 --> 12:53.266 the last cell in the last column right. 12:53.266 --> 12:56.166 The bottom cell and the last column here. 12:56.166 --> 12:57.900 And you'll see that this, 12:57.900 --> 13:00.233 has a label of bottom. 13:00.233 --> 13:01.233 And so what this is going to 13:01.233 --> 13:03.300 do is allow this pivot table 13:03.300 --> 13:06.300 to be dynamic based off of however 13:06.300 --> 13:07.133 many rows 13:07.133 --> 13:10.133 actually show up in this report. 13:10.833 --> 13:13.833 So I'll click enter here. 13:14.666 --> 13:15.800 And then I'll click okay 13:15.800 --> 13:18.333 to create our pivot table. 13:18.333 --> 13:19.333 As we saw 13:19.333 --> 13:21.900 I actually renamed this to pivot. 13:21.900 --> 13:23.333 But you could obviously call it 13:23.333 --> 13:25.933 whatever is needed. 13:25.933 --> 13:26.800 And then we use 13:26.800 --> 13:28.866 sort of the pivot table functionality 13:28.866 --> 13:30.400 within Excel here. 13:30.400 --> 13:32.300 So maybe, 13:32.300 --> 13:34.100 you know, to look at the time 13:34.100 --> 13:37.366 to start approval, for instance, maybe 13:37.366 --> 13:40.700 I want my, rows to be study. 13:40.700 --> 13:42.500 So I want to see sort of the counts 13:42.500 --> 13:45.300 or the, the amounts by study. 13:45.300 --> 13:47.400 And then I want to have time 13:47.400 --> 13:49.000 to start approval. 13:49.000 --> 13:51.900 As my actual values. 13:51.900 --> 13:54.000 And maybe I want to average that. 13:55.400 --> 13:56.100 So I don't want to see the 13:56.100 --> 13:57.400 total across everything. 13:57.400 --> 13:59.000 I want to average it. 13:59.000 --> 14:00.066 Now what you'll notice here 14:00.066 --> 14:02.966 is shows a little bit of an error. 14:02.966 --> 14:04.266 And that's okay. 14:04.266 --> 14:06.200 It's showing that because at this point, 14:06.200 --> 14:07.600 there's not really anything 14:07.600 --> 14:10.133 real populating this. Right? 14:10.133 --> 14:12.600 But when we actually go to run this 14:12.600 --> 14:13.300 out of vault, 14:13.300 --> 14:16.300 this will, be populated accordingly. 14:16.800 --> 14:18.166 Now to ensure that it's 14:18.166 --> 14:19.633 populated accordingly. 14:19.633 --> 14:21.033 So the other thing that we need to do 14:21.033 --> 14:21.600 to make sure 14:21.600 --> 14:23.533 that this pivot table works 14:23.533 --> 14:25.900 when this is exported from vault, 14:25.900 --> 14:28.366 is we need to, change 14:28.366 --> 14:30.066 the settings here. 14:30.066 --> 14:32.866 So we will go to options. 14:32.866 --> 14:34.700 Let me bring this down. 14:34.700 --> 14:36.900 Under data 14:36.900 --> 14:38.466 we need to check this option 14:38.466 --> 14:41.466 for refresh data when opening file. 14:41.466 --> 14:44.800 So this basically, ensures that 14:45.033 --> 14:46.000 when this is actually 14:46.000 --> 14:47.300 generated from vault 14:47.300 --> 14:49.700 and the file is initially opened, 14:49.700 --> 14:51.033 this is going to refresh 14:51.033 --> 14:52.900 based off of the actual data. 14:52.900 --> 14:54.400 That vault has populated 14:54.400 --> 14:56.333 it into the export tab. 14:59.066 --> 15:00.500 So with that 15:00.500 --> 15:02.633 now I've got a pivot table set up 15:02.633 --> 15:03.400 that will run 15:03.400 --> 15:06.400 appropriately when I put this into vault. 15:06.500 --> 15:08.233 Now the other things you can do, right. 15:08.233 --> 15:10.333 You can on your pivot table. 15:10.333 --> 15:10.533 You know, 15:10.533 --> 15:13.100 we saw that I had added some charts. 15:13.100 --> 15:16.100 So I can come in here and set up charts. 15:16.166 --> 15:18.066 When you're doing it based off of pivot 15:18.066 --> 15:19.033 tables, one things 15:19.033 --> 15:20.100 that's nice is, you know, 15:20.100 --> 15:23.100 we've already set the data range on this. 15:23.100 --> 15:25.266 So, you know, this is going to update 15:25.266 --> 15:26.800 dynamically, 15:26.800 --> 15:28.433 based off of that data range. 15:28.433 --> 15:29.600 And of course, you can come in 15:29.600 --> 15:32.066 and make other changes and, 15:32.066 --> 15:35.966 adjustments here, to, to your chart. 15:37.566 --> 15:38.900 So with that, we covered 15:38.900 --> 15:40.066 kind of how I created 15:40.066 --> 15:41.666 some of these different components 15:41.666 --> 15:43.433 and formatting here. 15:43.433 --> 15:44.000 Now let's talk 15:44.000 --> 15:46.233 about how this gets back into vault. 15:46.233 --> 15:48.266 So first of all what I need to do 15:48.266 --> 15:50.033 is save this file. 15:50.033 --> 15:51.133 So I will, 15:51.133 --> 15:52.366 go ahead and 15:52.366 --> 15:55.366 save this, locally to my computer. 15:55.800 --> 15:57.166 And then what we're going to do is bring 15:57.166 --> 15:59.166 this file back into vault. 16:01.433 --> 16:01.833 All right, so 16:01.833 --> 16:02.466 back on my 16:02.466 --> 16:05.466 report here in vault, I'm in edit mode. 16:05.766 --> 16:07.633 And if I scroll down to that advanced 16:07.633 --> 16:09.300 options section 16:09.300 --> 16:09.733 now again, 16:09.733 --> 16:11.600 I actually already had a template 16:11.600 --> 16:12.833 set up here. 16:12.833 --> 16:14.400 But I'm going to go ahead and remove that 16:14.400 --> 16:15.533 just for the purposes 16:15.533 --> 16:16.500 of demonstration 16:16.500 --> 16:19.100 to the first time you go to do this 16:19.100 --> 16:21.400 you're going to see an upload button. 16:21.400 --> 16:23.900 And so you can basically click upload 16:23.900 --> 16:24.866 and then choose 16:24.866 --> 16:25.500 the template 16:25.500 --> 16:28.433 that you've saved to your computer. 16:28.433 --> 16:31.200 Now once you have that associated 16:31.200 --> 16:34.200 all you need to do is click save. 16:34.300 --> 16:36.933 And then you can run the report 16:36.933 --> 16:39.800 and you can perform the Export to Excel 16:39.800 --> 16:40.966 and use the template 16:40.966 --> 16:43.966 to test out and see what the results are. 16:45.500 --> 16:47.366 And so here is the report that 16:47.366 --> 16:48.033 I've exported. 16:48.033 --> 16:50.833 We can see my pivot table populated, 16:50.833 --> 16:52.200 my chart populated. 16:52.200 --> 16:54.300 If I look at the export tab 16:54.300 --> 16:57.333 we can see all of that data populated, 16:57.333 --> 17:00.366 with the formatting that I had set up. 17:01.133 --> 17:02.333 And so what's really nice about 17:02.333 --> 17:04.400 this is I've kind of defined 17:04.400 --> 17:06.066 this format that I want to use 17:06.066 --> 17:07.900 on an ongoing basis, 17:07.900 --> 17:09.233 and I don't really ever need 17:09.233 --> 17:12.233 to manipulate that or address things 17:12.233 --> 17:13.300 in the Excel file 17:13.300 --> 17:15.400 before providing this report, 17:15.400 --> 17:17.633 to the appropriate people internally. 17:17.633 --> 17:21.300 I'm not having to do manual manipulation 17:21.300 --> 17:22.466 every time. 17:22.466 --> 17:23.566 I'm essentially 17:23.566 --> 17:25.233 doing that effort up front 17:25.233 --> 17:27.266 by defining the template. 17:27.266 --> 17:29.700 And then all I need to do is export to it 17:29.700 --> 17:31.033 and essentially populate 17:31.033 --> 17:33.500 the template with actual vault data. 17:35.266 --> 17:38.266 Now going back over to vault here. 17:38.400 --> 17:40.900 If I go back into edit mode, 17:40.900 --> 17:41.333 the other thing 17:41.333 --> 17:42.533 I just want to highlight with 17:42.533 --> 17:44.166 these options is 17:44.166 --> 17:45.833 you might decide over time 17:45.833 --> 17:47.333 that you actually need 17:47.333 --> 17:48.766 to make some updates 17:48.766 --> 17:50.666 to your Excel template. 17:50.666 --> 17:53.633 So there are actions here to be able 17:53.633 --> 17:55.066 to download the template 17:55.066 --> 17:57.266 that you have associated to it. 17:57.266 --> 17:57.666 You could 17:57.666 --> 18:00.966 then make adjustments, to that template 18:01.333 --> 18:03.100 and then use the upload option 18:03.100 --> 18:05.766 to essentially replace the template here 18:05.766 --> 18:07.166 within vault. 18:07.166 --> 18:08.433 And then again, 18:08.433 --> 18:09.233 all you need to do 18:09.233 --> 18:10.333 when you're making a change 18:10.333 --> 18:12.566 like that is click save. 18:12.566 --> 18:15.800 And it's ready to be run and exported 18:15.800 --> 18:18.800 to that Excel template. 18:18.933 --> 18:21.000 Now in this demo, you know, I covered 18:21.000 --> 18:22.933 sort of a few common scenarios 18:22.933 --> 18:25.200 of how folks might leverage Excel 18:25.200 --> 18:27.866 to do some custom formatting here. 18:27.866 --> 18:30.233 There's really a lot 18:30.233 --> 18:31.466 that can happen here, right? 18:31.466 --> 18:32.766 Excel's very powerful 18:32.766 --> 18:34.833 functionality itself. 18:34.833 --> 18:37.500 So being able to, 18:37.500 --> 18:40.866 really use vault data to feed, 18:41.133 --> 18:42.333 the functionality 18:42.333 --> 18:43.266 within Excel, 18:43.266 --> 18:46.200 is, is really, really powerful. 18:46.200 --> 18:48.600 I mean, you can do more advanced things. 18:48.600 --> 18:50.300 And I'm showing here, like being able 18:50.300 --> 18:51.200 to set up 18:51.200 --> 18:53.366 macros in your Excel template, 18:53.366 --> 18:54.666 for instance. 18:54.666 --> 18:56.400 So there's, there's a lot 18:56.400 --> 18:57.266 that can be done 18:57.266 --> 18:58.733 just based off of, 18:58.733 --> 18:59.633 the amount of 18:59.633 --> 19:01.666 functionality within Excel.