Collaborate on Office 365 Invoice Template for Export with Ease Using airSlate SignNow

Office 365 invoice template for Export

hello hello everybody good evening and welcome to this session about microsoft 365 and tonight mainly we are going to discuss about world and how to create an invoice inside word alright so just confirming we have things working yes okay um all right so quick presentation okay my name is david gerard and i am a microsoft certified trainer certified business intelligence data analytics and passionate about all microsoft technologies and mainly what i would call office i.t technologies so technologies that are used every day by almost everybody in the office word excel outlook powerpoint and any other tools that are present inside the family of microsoft 365 application and usually okay users don't know exactly how to use the different tools that are existing and they don't know how to leverage their capabilities there is a rich collection of tools that are existing and we see here those applications that come with microsoft office 365. the most common ones are well excel which is used for very long time by a lot of people words then we have of course outlook but there are many other applications that people don't especially know so power apps power automate power bi we have project sway white board and you know you might be wondering what are those applications doing exactly what can we do with them how can they help us in our business and how can we improve our productivity and proficiency at the workplace so well that group is especially for that microsoft 365 meetup group is there to help you better understand how to use those different applications so today as i was mentioning we are going to focus on word and actually let's see if we can connect to the meetup group just meetup.com so we have also a group in whatsapp in which we participate and answer questions and mainly about excel but quite often users are asking questions about how to do this how to do that and recently we had a question about invoicing quite often people tend to do their invoices or receipts within excel and i want to show you that it's possible to do it in word and actually it's even much better and word than in excel much more convenient because it contains usually much more text that need to be properly formatted and a word is very good for that that's the main purpose for that application so actually we have three different groups today we we are about microsoft 365 office productivity uganda group in which we discuss the use of the different applications that are part of the family of microsoft 365 so we had someone who asked a question saying i kind of needed tips on how to create an invoice and as i say most people would think oh let's do it in excel so okay we are going to see we can do it in word and i will defend the idea that it's way much better in word and it should stay in work things are good in excel but other things are very good in excel in word and quite often okay you would store your data in excel unless you have the capacity of having a real database a server to store it and and everything okay it can be a little bit costly you need also some expertise of course to create it manage it and update updated regularly so you know after all excel remains very uh powerful simple to use and very uh efficient and you can connect word to excel to obtain data stored in excel inside work so if you wanted to create an invoice based on existing clients and existing transactions well you could do it very well in word connecting it to excel and getting those data with excel so let's go and have a look at a word document so hold on i see okay i look very dark i'm going to put the lights for the camera at least it might help a little bit to see me a bit better all right so let's jump into word and let's have a look a little bit how we can create that invoice and we we are going to take the example of an invoice i created some time ago um in which we have fields that are available form fields or so we have different areas in this document that can be filled by the user some areas are protected and can't be accessed and and modified so these texts the the the company the company name and and address can't be accessed i can click on it it doesn't let me uh select it or even change it it's the same for the logo it's the same for the number of the invoice well apparently i can select that but i cannot move it or i cannot resize it i cannot do anything so the number of the invoice for instance it's not accessible and we can imagine that legally speaking we shouldn't be able to modify the invoice number i think okay the invoice number should be automatic and not modifiable for auditing purpose and traceability the number one the number two should be remaining number one and number two such that okay over time we can always trace back an invoice number and understand the status of an invoice is cancelled that number should remain as so if it is number 102 and this invoice is cancelled well then one of the two numbers shouldn't be reused for something else it should still be existing but identified as cancelled invoice number so that's why this number is not is not accessible and actually generated automatically in that very invoice template some fields are accessible we can distinguish them because they are grayish actually and so in those fields we can clearly select them or at least place our cursor inside and start typing inside the the very first field that exists inside this is the currency so we can choose in which currency we want to invoice our our client so we have usd and new jigs so again this is really targeting the ugandan market or east african general but this one was made for uganda so it has uggx we could place the currency that we want so if i choose uggs um i can start typing the company name company name okay name all right whatever and i validate note that as soon as i validate okay we'll see it again later i will attract your attention that but the the field formats the text automatically as well with a proper case so an upper case character at the start of each word within that field so that's a little automation that we have put in place then okay we have the date which okay is 30 of today is the 30th of october 2020 so here also the format is going to be created automatically so the field is going to format the date automatically once i validate that field with the tab key on my keyboard so i use quite often my my keyboard because i find it very very useful and much more practical than using the mouse and navigating with the mouse so using the tap key on my keyboard i'm going to exit that field and move to the next field available as soon as i move out where we can see that that date is now formatted as a date so the same as in excel when we enter a date we should enter it correctly um not mixing um numbers and letters as i can see sometimes if i go back and i put okay the 30s with with the th and okay the tens and then 20 i'm not sure it's going to work correctly x word might not recognize that date as a proper date because we have introduced some additional characters that are letters which are might create a problem so i validate and when we can see okay it didn't work okay the date is not valid so we really need to enter the date correctly inside and it's the same principle as in excel okay the way you would enter a date in excel is the same you would enter a date correctly inside a word and a date is a very specific type of of data that contains different components or dead parts identified as days components or dead parts identified as days month yeah but from which date we can also extract additional information like the week number like the quarter or eventually the semester and etc we can compute those information and a date can be used also to subtract a date from a date or add a certain number of days on that date let's say okay just in that invoice we have the date in which that invoice has been issued but usually when we invoice an organization we expect to be paid for that invoice and maybe we want to place the information below the invoice that the client need to pay within 30 days after being invoiced and we want to maybe precise that day so we want to be able to add 30 to that very date and that's possible as long as a date is properly formatted as a date and it is recognized by the system as a date so let's correct that it's important to enter uh just the the recorded information and word or excel if you are in excel is going to do the work for us to format it correctly with a different format that we have selected before so we will see that because we are going to construct also something like that so now i i validate i'm going to exit that field using the tab key on my keyboard and move to the next field available which is this one okay so i validate and now word has accepted that date we can clearly see okay the 30-oct 20 and now we are in the next field and well that's a properly formatted date that word recognizes and has formatted for readability purpose so now we connect our product okay nothing very specific in there again i validate with the tap key it's very easy and convenient to move from one field to another field then here i can enter the quantity and not know what's gonna happen in the amount column and the totals at the bottom currently those fields are at zero we have no value but as soon as i'm going to exceed that field and validate it moving to the next field available then word is going to compute those values we are going to have the total here the total of all the different quantities and um well the amount is going to be display when both the quantity and the unit cost are going to be filled as well so if one of those it's at zero then the amount remains at zero okay so that's a an evaluation that have put in place also in that in that in that in voice template so i believe in that in the in that invoice template so i validate look at this here total below boom as soon as i validate this field is computed automatically okay i cannot access it i cannot change it i cannot manually inside this helps avoiding human errors or mistakes okay if i were to compute all the different figures here and then i revised my my invoice and i changed the value because the client decided okay i don't want 20 i want 15 i would need to manually change that figure if i were to manually input everything which increases the the risk of course for uh those uh for that okay and we want to avoid the risk and miscalculation okay so we want to automate as much as possible the different fields all right we have moved 25 000 shillings oh i'm not i have to click this one right okay so 25 000 shillings now again look at what we have here zero zero now i'm going to validate and as soon as i'm going to validate so i will use again the tab key my cursor is going to move to the next field available which is poop the new product here this field so this is going to be computed automatically and the total at the bottom is going to be computed automatically as well i validate and boom we have it the quantity 20 times 25 000 makes 500 000 which is properly computed again we avoid mistakes in calculation and correction so it's there automatic and the bottom as well okay product two we created a pla tabulation to navigate to the next field available quantity let's say 40 i have for 42 000 shillings or it's 420 let's leave it like that we value date this is computed automatically 16 800 million 16 million 100 000. this is computed by the sum of all the things my total quantity has been updated also to show now 60 which is the total of 20 and 40. i add another product okay number three the quantity is going to be 15 the amount is going to be 650 i validate with the top key and up it is computed so we have it okay so now you might be wondering okay all good but how do we create an invoice how can we get it done so that's what we are going to review let's have a look behind all those fields what do we have so we need to remove the the protection that has been applied on that document that's why we cannot access this field okay because it's protected so if we remove the protection then we can access those fields now we can access okay so it's a simple protection to have the field work as well so we can to reveal fields codes as we call them we need to use a keyboard shortcut alt f9 if i remember correctly i'll alt f9 on our keyboard to reveal all the different form fields and formulas functions that we have used within the template to to to to create our document so we have um a form drop down that has been inserted here a form text then we have okay a certain form field which actually picked the information from document property which is okay the short name is top property so this information picks values inside the doc property of the document and it looks for the the property in inem which stands for invoice number of course so let's have a look okay if you want to access the document property would go in the home tab and then you look at option option maybe not option actually i made a mistake we look at info we have the properties here so info properties and we need to look at all the properties or advanced either okay rather it's advanced properties advanced properties and we need to go inside the custom properties of the advanced properties dialog box inside the info of the document and here we have our number in in number and currently the value is one of the two and this is okay this doesn't need to be accessed or managed by the user because this is automatically um incremented and and for that okay it definitely requires uh a macro to update it okay so this would require a macro but today we are not going to talk about vba macro we are going to keep it simple okay um but that's what i wanted to show you that the infnum property invoice number is found here type number with the number one so that's that's how it is we can create different custom properties in our document so form text okay a specific form field here that collect the information from dot property a form text as well so it's the same as this one another one here another one here another one here all those are the same form text from text here we have a formula that goes and picked the actually it calculates the product so which is the multiplication of two values from the left so in word okay we can create function that are going to aggregate data from left we can those are keywords that we can use we can use left to right above below i've never used below so i don't know if it really exists but the most common one okay for us because we read from left to right and we usually compute things mostly on the left side and we show the resolute on the right side it's pretty common so we use the product left well i think it exists okay pro i mean right and and below but i've never used them so anyway product left to multiply the values from the left and even though i have well the left would be all that actually and so all that is what's on the left of this cell but because this is a text it's ignored and uh word calculates just those two values which are numbers so the text is in your okay even though the left is is all that all the three different columns on the left that being text it's it's being ignored okay so and actually okay there is also a formatting uh formula so with an if function if the currency because okay we have here in that field the drop down ugs and ugs and usd so if i were to use usd actually uh it would deplay display the formatting differently so for uggs where we would use the ugg's extension currency and the formatting okay we don't need the decimals and for the usd well we usd is pretty common to have sense so if you were to invoice in usd we would give place for sense so that's why okay we have the dot zero zero uh just in case it is uh usd otherwise it would display as without the decimal and and always with a thousand separator so the the if function works pretty similarly to what we can have in excel okay so if you are familiar already with what you have in excel you should be able to construct if functions uh inside word as well it's pretty much similar and the product function well it's the same as the product function in excel as well okay it's very similar and if we have product you can imagine that you have also the sum function which actually we should have down below here uh for the total sum here it is yeah that that's the sum function okay so here we have the same function with the keyword above so we are summing everything what's above or anything what's above and okay the the entire field is this one that's the entire field okay it starts up there uh to down that's why we had the two bracket i was a bit confused at the beginning because we had the two curly bracket at the end for which we find the the starting brackets here okay so in the first page of it so we have some custom format first which is being applied again if we have the the if we chose within the dropdown usd then okay we will display usd otherwise we would display uggs here okay so if if if the currency where that field is actually called currency so if i right click on it i look at the properties the bookmark name or the name for that field is called the currency so that's why we refer to it that we use the refer syntax so we refer to the currency name or bookmark we evaluate it against the usd and that's what the if does if it is if the currency is equal to usd then we display ust otherwise if it is not usd then we display ugs okay so that's what the if function does does and then we have um the the the sum of all what's above and again a new function to evaluate okay if we should display the decimals or not display the decimal so that's what we we have here and here we have some extra text actually to normally display the numbers into text but i am not sure it has worked and it displayed as as we could have expected so well that that okay that becomes certainly a little bit more complex okay but it's it's basically just some if functions embedded together uh yeah with with okay some evaluation to um to to uh break down the number because excel has as well word has some limitation to display um numbers using the the the special switch text which normally automatically display numbers into text um and and it has some limitations so for big numbers in millions we need to basically use that that syntax and function inside so anyway um we could just copy paste that uh if you know it's it's okay um let's go back to our original con alt f9 so to go back i didn't go back at f9 okay to go back to what what we have so actually okay here normally i should have some some values displayed in in text okay did not display that okay anyway um maybe something needs to be fixed here so now let's let's recreate that in in in our document so okay let's do let's do something okay ctrl c ctrl c to copy everything ctrl n to create a new document ctrl v to copy everything now we are going to remove that and we are going to recreate them from scratch at least we have um we have the place where we can create those fields okay the same structure so we have our invoice so we we want to place some fields inside to allow the user to enter information inside right so here we we have seen before we had some text fields text fields uh a drop down here just in case okay we have an invoice in usd or in dollar for for this one okay just to keep it simple we we might not use the drop down i can show you how it works but we will keep it simple we will be using just the the basic fields there is already quite a lot to do with that so uh text fields uh in a few areas okay that we need to put in place and actually okay the text field is going to be the most important of all the different fields so in in the ribbon so you need to go to the developer tab and and you need to make sure the developer tab is activated in your ribbon if you don't see it you need to right click on your ribbon you choose customize the ribbon click on that you will display the customize the ribbon dialog box or options i think it is just it's taking a bit time to display so it's coming uh here it is okay the word options um so in which you can change all options for your word application so customize the ribbon and we just need to make sure that among the different tabs that are available you have the developer tab which is selected okay uh okay when when you have the developer tab selected okay make sure you have it you click ok and now you will see it inside your ribbon developer will appear there so what we are going to use is within the different controls what we can do is that we can click on design mode it's not obligatory it works well without but okay it's it's it's a good practice certainly to switch to design mode to create your your document or to design your document okay so we have the most modern form fields that are available here but those one actually you can't perform calculation with those fields so they're very good there is a calendar there is a drop down a combo box different things drop down list um check box i mean they're pretty good but you can't use them to perform calculation and exit of that so we need to use legacy forms when you click on that little suite case with uh where that little toolbox we have access to the legacy tools and mainly what we need the legacy forms and the one that we most use is the text form field this one that's mainly pretty much it that we need if we were to use the drop down form field we would choose this one okay we'll see at the end if we still have a bit time but we are going to concentrate on this one and really explore it to to to to its full potential so let's click on that okay i am here in the quantity so i have inserted that field okay you might not see it because it's gray of the gray so it might be a bit confusing but it's there so what what i need to do with that is just copy paste it it's as simple as that we can control c copy and and paste it here and paste it here and paste it here okay in all the different lines that we want to uh give the user the possibility to type something so quantity our user should be able to enter value so we can copy that field everywhere until now pretty simple okay i'm here i don't need this one okay and for the date as well we need a form field uh invoice number well okay we are not going right now to automate that invoice number again we are going to keep it simple um you would need to enter it manually okay that's fine okay there might be other ways also you could generate it and and restrict access to that but let's do step by step make sure that um already this this knowledge is uh well understood okay so we didn't spend much time on on formatting the document those could be some some basic as well this is structure inside the table and i have hidden the the borders of that okay this is some simple text aligned to the left and here we have a picture which is in front of text so it moves on top of the text it's not in line so that's important then we have a little table structure here very very little okay just one one cell [Music] and actually okay maybe we could reveal the the hidden character so that gives an id of actually this one is is a table within a table okay so we have our all our table here with transparent borders okay and this one this small invoice is just a a table within a table okay i did that a long time ago and i think this is the same it's a small table within the cell of of the table here so it's a table within a table okay that's that's how i did that the the day i i did that it's okay so here we have another table structure for which okay all borders have been hidden unless the inner borders to create a certain structure to hold our information okay so now we forgot we need also a field here to calculate okay the the total quantity and the other quantity over there uh we will see okay i'm not i'm not certain okay the form field should be used for that actually for other template i use something else we'll see okay let's let's first do step by step we'll replace it after so here form field so if you want to customize that and change the properties of that form field we need to select that field right click on it and then we can choose properties from the contextual menu that displays otherwise from the developer tab also we could choose properties from the ribbon developer tab within the controls group we have access to the properties that we can click it does the same if i click on that i have access to the text form field option dialog box and if i right click on my field which i usually prefer because i find it faster and more efficient so i click on properties and i have the same okay so here within the text form field options dialog box i can choose the different properties of of that text form so i can choose whether the the the data type inside that field should be regular text number date current date current time and our calculation so actually okay we might need a calculation for that field we might need a date for that field a number for that field and text for all the rest most probably right and of course those fields should be numbers as well okay we cannot accept text in quantity we cannot accept text and you're gonna in unit cost and we need to make sure that if we want to have the the calculation operating correctly we shouldn't have any text coming to disturb us so it should be on the numbers we should we should then have a ugex 100 000 or something like that which is quite often disturbing when there is a mix of data types so in our first field here the the that's the name of the the company we are invoicing so it should be regular text the default text well in that case we don't need any specific default text otherwise yeah it wouldn't make sense much maximum length well okay we don't need to limit the length in particular the text format that's where we can choose if we want the text to be formatted as upper case lowercase first capital or title case so if we have a sentence multiple words first capital will capitalize the first letter of that sentence and if we use title case uh it will it will capitalize each letter of each word what what i had in the previous invoice i hope i'm not mistaken with that and of course uppercase is for all uppercase lowercases for all characters lowercase okay so let's choose a title case and i hope i'm not making any mistake okay and then we have okay macros that can run on entry of that field or on exit of that field well in this document i don't have any macros so they are empty and again we are going to keep this demonstration relatively simple so we are not going to interact with macros but if we had macros that's where we could add interactivity with our fields by you know adding macro on entry of that field or on exit of that field bookmark is the name that we want to give to that field so well in that case because it's the company name that we want to have in that field we are going to name that bookmark okay company name um usually okay like like in excel also when we talked about named ranges those names of bookmark don't accept a spaces in between the name so if i had a space here i'm not sure if it would work i don't think it would work you see the bookmark name is not valid there okay because it has a space inside so eventually we could use okay underscore or hyphen you know this hyphen or no space at all and then okay we distinguish the different words with a capital letter at the beginning of each so let's use an underscore and as as a bookmark name and we can validate that and it works okay so no space let's go back to the properties we have um the possibility also to make it enable or disable so if i wanted to disable that field okay i could just click on this checkbox to disable it but in that case we need it enabled and functionality is going to be useful for quantity and unit because we want to perform calculation on exceed of that field so we want a calculation to be processed on exit and of the quantity and the unit cost so that checkbox will be very important and useful when being endospect all right so i have set up my my field okay so i can click on ok and we are fine with that so number here again we won't make it automatic automatic this number you will need to feed it manually but just know okay there are ways of automating it and restricting access to that and securing it so we are going to restrict the type of information that we want to number and the maximum length okay we don't want especially to restrict it okay and the number format uh well we have different format options that are available we can choose this one yeah those other format number will be very useful for currency if you want to display the decimals and if you want to display the values with a thousand separator those two will be used for currencies for example and we have extra formatting for percentages if you wanted as well so again we have the same type of properties as we have seen before we can quickly move forward to the next we don't especially need a default number we can or maybe just zero if we want and that's what displays inside but it still requires the input from from the user definitely then we have the date they fit so again we said sorry we select that field right click on on that field and we select properties we want to restrict it to a certain date and we want the format of that date being um so we have different options available if there is not the option that i want inside here i can also create it myself so we have well mmm for months and when there are four m's it's for the name of the month in full d for the day and one character represent the day number the day number within the month without the leading zero double d would be with the leading zero then we have y for the year so well if i don't especially see what i want what i want to have the leading zero as well to be displayed such that okay if we are the second of november 2020 we display zero two of nerve twenty okay so well ju just to say we can customize it also i picked uh one existing format and then i i added myself my i i i'll tell the to represent what i want so we can customize it also the way we want all right so we are going to restrict that field to a date we have chosen the type to be a date and the format okay it has disappeared again okay so i'm going to select this one and add a d to the day okay and i validate that okay good so now we have our dates which is here then all those field text field so i can right click on it well and this one i'm i'm not going to change so much because anyway it's regular text do we want to format it specially it's up to developer really we we could want to first capital letter for everything so we can right click on it on each look at the properties confirm okay by default it's always regular text we don't need to change that first capital validate select our next field properties first capital validate we don't need well we could give it a bookmark name as well um but okay we need to give it okay like product line one project line 2.93 and we might not need to refer them in other in other fields so the bookmark name is not obligatory okay i i filled it for this one but it doesn't really matter in that case it's it's not obligatory we can leave it empty and then usually okay you fill that bookmark name if you intend to use the field value inside another field or if you want to reuse it somewhere so you can reference it with its name but in that case if you don't want to reuse the product name in in other location of your document we don't need to precise that so by default it's always regular text what we just need to do is to make sure okay that that text is formatted correctly on exit such that okay it looks a bit better and all uniform and that's all what we need okay so properties i know it's that field we want first capital and the last one is this one right click properties choose the text format as first capital and validate very good so now we are arriving at the quantity quantity we right click choose property and we choose okay number and we choose the format with one zero which will uh force display of one zero at least okay uh bookmark here it got a bookmark name text one which okay we don't especially want yes yes yes maybe no good question because there are different ways of computing here the the value so we could use the the function some left but if you use a form field uh how would it go actually i've never used the form field i usually use other type of fields okay let's have a look we can always come back later um what i'm going to do i'm not going to right click on each of those and try and format them i'm just going to copy this one and paste for those one to replace them okay this will go faster and i double click on it to access it and to confirm that it has been registered as a number type field which is the case so it's very good so i can do the same for this one i double click on it it's regular text ctrl v to paste double click and now it's number okay so it has done the work correctly now if i want this field what i need to do or something important what i need to do double click to access it so that's another way of accessing it right click property or eventually double click on it as well it works okay what we need to do for that field is calculate and exit we want that that field on exceed of that field it triggers calculation in another field and well it will trigger the calculation in that amount field which will be the the product of quantity and you need cost or you need cost time quantity so okay calculate on exit that's very important and and we can repeat what i did before ctrl c because i forgot to do that ctrl v select ctrl v click select ctrl b click select ctrl v okay confirm calculate and exit good you can do the same for this one okay so ctrl v but this one as it is unit cost i want to display the format slightly differently i want to have thousand separators so each thousand separated by a comma so i want to use this format instead okay so ctrl c and ctrl v ctrl v ctrl v ctrl v and ctrl v double click this one i confirm it's a number type format is correct calculator makes it is correct as well no default value that's fine all good very good okay let's find out how we can create that field which suits sum the values above so we would use a different type of field which is a calculation the expression should be and i hope it works in that case okay some we can use the special keyword as i explained before which is above so you don't have that equivalent in excel because you can easily reference cells uh references in in excel in word it works pretty much the same sorry so because we have a table and values organized within cells columns and ranges we could reference them with column references and rows so and then pretty much the same as in excel with a1 a2 a3 and so on and the first column correspond to the column a here we have the column b c d etc but because we don't have those letters and numbers visible inside that table it's a bit difficult we would all the time look at column a then we have okay one this is our title column one row one two three four five so here we have b one one two three four so it would be a bit complicated for that um we can use the keyword above so we are going to sum what's above and the number format should be 0 that's fine we click ok and we hope it works so now we can we can restrict editing of our document and test that it works correctly as expected then once we confirm that this works as expected will continue with the other fields on the right side okay so from the developer tab we are going to restrict editing of that document and we are going to editing restriction allow only this type of editing in the document we click on that and we choose filling in forms filling in forms and what did i limit formatting what what i should be able to click on that why why it's not possible i should be able to edit that eating let's display again wait when it's in review okay okay [Music] what did i do wrong i should be i should be able to uh oh yeah yeah okay no i i'm in design mode yeah if i remove design mode you see now it works okay so if you want design mode make sure okay you you turn off design mode so that you can uh start enforcing protection i had forgotten that so in that case okay just for the demo i don't need especially a password okay just to make sure it works correctly and there is no security issue with that document so just want okay whatever i type here we should have the first letter of each word capitalized okay if i made it correctly so name of company again i validate with the tab key okay to move from one field to the other field and it has worked okay each letter each first character of each word is capitalized as as we wanted okay so that's cool number we can type okay number two the date okay we are going to say we are the 30 10 20 and remember the format that we applied to that date now the date should display as 30 hyphen uh oct hyphen and 20. okay so i validate with the tab key to exit that field and move to the next field and notice that when i say exit you remember that one of the properties of that form field was to apply a certain macro on entry or on exit so currently we are within that field and i'm going to exit that field with the tab key so i'm moving from that field to another video so if we had a macro um activated on that field on exit that that's when i exit that the macro would would run but okay we don't have any macro right so let's let's look at that value inside and how it's going to be formatted and it has worked correctly wonderful so now we have okay uh product a and i use the tab key remember that this one we applied a different formatting which was actually um uh what first capitalized uh first yeah uh what first capitalized first yeah i don't remember what's the term we we would expect okay just the first letter of the sentence to be capitalized so my a shouldn't be capitalized okay so i validate with the tab key and it works as expected wonderful so now i'm going to add the quantity five inverted wonderful so now i'm going to add the quantity five and i would expect that field below to compute that so currently it should display five so i i use the tab key i hit the tab key and wonderful it has computed five great let's move quickly and i i passed those fields i believe those fields are going to work equally as this one i just want to make sure that if i add let's say 15 it computes to 20 and that works perfect right so those fields are working now we are going to do the same for that well this one i also um working correctly okay it formats with the thousand separator that's great 45 and it might format as the thousand separators great those fingers are working very well now i just need to add a function a calculation here to calculate the product of what's on the left for all those fields and here i need to stop the the protection um i'm going to double click on that and i need here a calculation that calculation should be an expression being equal to a product the product product of what's on the left so the function is going to reference to what's on the left and i i could actually use also uh cell references i'm going to show you that it works equally but just okay it's a bit more work we need to identify this is the first column so column a column b column c so b it's one and b2 and this is c2 so i could say b2 and the same naming convention as in excel so b2 colon c2 to say to excel that i want to compute from b2 to c2 okay so i'm going to validate that and now well 5 times 20 000 because i have already some values in the quantity and unit cost uh word returns okay the computation of five and twenty thousand okay which obtain one hundred thousand so we can do like this we can do also um calculation of a product we could do also we don't need to type product okay we could do also so b this is b2 this is b3 okay this is c3 so b2 times c3 we could write it also like this so b2 times c e actually should be c3 okay yeah c3 times b3 c3 okay b3 c3 okay uh the resulting format should be thousands separator like this and that's good and okay and it works equally okay so 15 times 45 000 uh most probably that's correct uh okay so it's uh most probably correct all right so 675 000 all right so it works also but again the the fastest way of doing that is using a product so a calculation equal product left okay so product and whatever it's on the left we are going to calculate the product of whatever is on the left so whatever is on the left of that cell is all those so again it's going to consider also the the particular or the product name the quantity and the unit cost but because this is text and this is valued as nothing it will be ignored by the function and it will return the the the the product of those two okay so i will also for sure confirm that it works correctly as expected so we do that now we have zero okay um let's enforce the protection and again we are going to test that it works correctly so we are okay so we have our product uh so some let's type without any capitalization so all lowercase so some other product and as soon as i validate i should have my s capitalized and not the rest so i validate s capitalize not the rest very good so 15 20 and yet another i validate the tap key i enter 20 and i enter 100 000 so 100 000 will make it a bit easier for us to evaluate that it works correctly uh 100 times then time 20 we should obtain 2 million if my calculation is correct and yes i calculated correctly 2 million and that works correctly pretty fine so here we had product left okay so we calculated the product of whatever is on the left and we can see that even we have some text here it's completely ignored and word calculates only what's represented as numbers or 20 times 100 000. of course you could say it's dangerous because if you have some product numbers um you know you you you you would potentially include some product numbers here but if you had a product number i would highly suggest you keep the field as text anyway okay very important okay all right so now let's make sure that we continue that so the advantage of having product left here is that we can easily very easily copy and paste it so we ctrl c ctrl v ctrl v ctrl v so now it it looks like it's computing what's up there but no okay we are going to select that and to force calculation we use f9 and it's the same function key as in excel in excel if you don't if you want to manually process calculation you would use f9 so in word it's the same as well if you want to manually process some calculation f9 now it has reset to zero because it has forced that field to calculate the product of what's on the left product of what's on the left and as on the left side there is nothing it it returns zero okay that uh that makes sense now we need to make sure that this one calculate the sum of what's above so we use calculation we use the expression uh above so again those keywords like above and left make it very easy to obtain what we want otherwise we would need to create the sum of using cell references again a b c d this is 1 2 so d 2 2 d 3 4 5 6 7 so d 2 to d7 so we would need to write uh d2 colon and d7 right so we would need to type it like this but dangerous also because if you want to insert a row with the function referencing cells it would not take it put to consideration an inserted row it would still calculate d2 to d7 so if you had like eight rows that function wouldn't work so it's very practical with the keyword above because whatever you insert you could insert two rows it will still create the sum of what's above no matter how many rows you have so we validate that and we have it so actually we did not we did not calculate you see that we did not click calculate and exit so we are going to see that it computes anyway correctly but those actually those fields shouldn't be touched by the user so we don't have access to fill in enable the it shouldn't be enabled and that's that's correct that's fine okay the user shouldn't be interacting with that so it's actually great and no not accessible by default okay so let's uh and start enforcing protection and make sure that it all works company so let's reset let's reset our form uh can we reset it ctrl a okay so ctrl a f9 will reset everything to uh zero to nothing okay and false calculation but as we have zero everything is at zero all right so uh we have our invoice template that we have created now once we enforce protection and restrict editing only the great fields that we have in certain inside can be used to fit our form so we have some company names here some company name not that i capitalize the first letter but not the rest but as we enforce that property in our field to capitalize each first letter of each word as soon as validate it format it correctly my invoice number is number five let's say that i i wanted to type okay five in uh it doesn't work it doesn't take text because we have restricted the type of that field to a number so it could it can only access numbers five and not the in or whatever i've put inside there date we should enter our date properly as a date okay um so okay 2020 okay 2020 okay 30 10 20 20 that will work i validate with the top key and we'll format it as we wanted it to be here we have my first product so first product not that i did not capitalize the first letter but because we formatted that field as soon as i validate now it contains the right formatting so 20 the quantity i validate it's calculated here calculated there very good perfect 4 for 15 000 shillings i validate it calculate 300 000 300 000 as well perfect next my second product we have 30 quantity um the unit cost is 12 000 or 100 2000 i wrote okay we validate that with the tab key and it calculates it automatically wonderful that's great all that has been calculated fantastic three million 360 which is the sum of hundred and three million sixty thousand let's say i want to give a big space and i have some some uh other product here i use the tab key to navigate from one field to another field i have sixty quantities and the unit cost is 85 000 and i validate and it computes that value correctly so the product of 60 times 85 000 and we have the sum of all what's above of all the different amounts displayed here with eight million four hundred sixty thousand all right this works very well and that's how we can create an invoice inside word and even including calculation and restricting the user to interact with those calculations and keep our invoice very professional we can even format that field to display uggs or ush whatever well again we can customize this the way we want so if we wanted to have u g x instead we can display that as ugs and the formatting is exactly the same syntax as we would have inside excel so the first part within the before the semi column represent the positive numbers then after the semicolon it represent the negative number and we could add also zero value how should a format be formatted zero value and text if we had text as well so let's keep it simple as well so for now um and imagine if we had negative value it would be formatted between bracket and we have the the ug that display before the numbers okay great that's it we have created our invoice okay it's it's not as dynamic as the first one as we did but okay it's already pretty good it's a very good start we have created our invoice we inserted some form fields we formatted the form fields we accept text only and format the text entered with a capital letter um you know proper case or whatever they call it um date restricted to date only we have seen okay it's very important format date correctly such that it's being recognized as a date uh restrict to a number and we cannot enter text when we have numbers uh performing calculation as well okay so the product of what's on the left and the sum of what's above the sum of what's above as well with a specific formatting displaying the value with the comma separator and the currency displayed at the beginning as well awesome wow we did it all right it's time okay we have spent about an hour together a great time we have created a wine voice now i hope i have convinced you to create invoices inside uh word and leave excel aside so okay the next step okay would be to use mailings and connect uh your your so we will need to stop protection uh mailing so the next step would be okay to connect your word document with uh some recipients so we could use an existing list locate locate our excel file so here we can choose any data source so we have okay excel are part of the data source we can connect to so we can connect to an excel file um and and use values fields that we will have within an excel file so let's say you want to invoice a certain customer we could then okay replace it here with the name of the customer that we want to invoice for which name would be present in a specific list inside excel and all values purchased by that very customers inside here as well so we can enrich our document with um fields that we have available from the insert tab quick parts and within the quick parts we have well access to the document properties so not not the custom one but the classic one and then we have fields as well that we can insert here we can insert a field but right now i again okay i cannot access it okay so insert tab a quick part fill now i can access it okay yeah because i had everything selected before so we could insert fields and we have access to formulas also advanced whatever that means ask author but that that that could be for another day to go through all the different fields that are available to create very dynamic documents and very powerful documents there are many things that can be used to create very powerful documents all right very good okay great i thought you enjoyed the the session uh just having a quick look if we have any specific message otherwise i guess i guess we are going to end the session if you have any question please feel free to send me and i will be happy to answer them anyway this is going to be recorded and it will be available on our youtube channel i would like to thank also very much uh marvin secandy for this great collaboration he's giving me always a very okay i like words because it is really very good and very helpful to set up all those meetings and uh i really want to appreciate his collaboration input friendship and and uh work with with him so marvin secandy from uh vinix software school um he's also the administrator of the excel whatsapp group in uganda who is a great supporter for all those events and it is mainly thanks to him that that those events exist because quite often i'm very busy and i i it's not that i especially forget to put in place those events but yeah it takes a little bit time to set them up and sometimes okay when the day is very busy it's a bit difficult so again a a big thank you to marvin secandy for helping us setting up all the things and for his uh great collaboration encouragement and motivation all right great okay we are done with that um so if you have any specific requests well you know where to ask you send the chat send in the email subscribe to uh that group okay you can always register with us and you know keep informed and be aware of what's happening and when it's happening and and when to access those things okay all right okay thank you very much guys for watching and being with us today uh this is the end of this session uh i wish you a very good evening and wonderful weekend all right thanks um