Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021 Beginners
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021 Beginners
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > How to Create Invoices Using Microsoft Excel 2013

How to Create Invoices Using Microsoft Excel 2013

Creating effective, powerful invoices has long been a primary function of Excel. In Excel 2013, it is even easier to move information across tables and even workbooks, saving time and effort.

Instead of having to find the customer information from another worksheet and type it in, you can tell Excel to automatically dig this up and insert it into your current workbook. Add information to one cell and let Excel find it elsewhere.

Easier, more efficient, and all around more powerful for creating business documents, Excel 2013 is a highly useful improvement over its predecessors.

Watch the free video here, transcripts for the entire video follow:

Need to master Microsoft Excel 2013? Get 19 hours of Microsoft Excel 2013 training – click here.

Video transcripts:

Hello again and welcome back to our course on Excel 2013.  By now we’ve covered quite a lot of the basics of Microsoft Excel.  You hopefully have done some of the exercises that I provided and you’re starting to feel more confident in your use of Excel.  One rather unfortunate thing about Microsoft Excel is that it is a big, very powerful, and quite complex product.  And if you’re still quite new to it, there are many, many more things to learn.  And I think one of the dangers is trying to take in too much at once and there’s always a good case for getting a bit of practice with what we’ve covered already.  So what I’m starting in this section is a small case study to show you how we can use some of the things you’ve already learned together with some new or perhaps more detailed things related to Functions to assist in the production of a business document.

Now the business document we’re going to produce is an invoice for our plumbing supplies company.  You’ve seen it already earlier on in the course, but now we’re going to start to look at making it not only more flexible and more powerful but also something that could be more likely to be used in a business environment.

So first of all, let me explain a little bit about this invoice.  We have the word Invoice at the top.  There’s our name, Ocean Plumbing.  Our address is 2324 Main St. South Park.  And this particular invoice is going to Zak Stephens of West Beach Bathrooms.  There’s his address.  It’s going by courier.  The order date is that date.  The order number is that.  The purchase order number from the customer is that.  Contact department there is purchasing and the account number for this customer, 2973, terms are 30 days.  Now if I were regularly invoicing West Beach Bathrooms, I’d have all of this information in a database.  And for the purposes of this exercise, my database is one of the sheets in this workbook.  Now normally I wouldn’t set things up like that but in order to demonstrate how to do this, let’s just go along with my approach for the moment.

So let’s assume that the worksheet called Customers here is actually my database of customers.  Now I’ve only put four on there at the moment.  I’d hopefully have more than four customers, but there you are, account number 2973, West Beach Bathrooms, Zak Stephens is the contact.  Their terms are 30 days.  They do get a discount and there is their address.

Microsoft Excel 2013 training courses

Now if I were preparing an invoice for West Beach Bathrooms in reality, I wouldn’t type in all of these individual lines and pieces of information for West Beach every time I do an invoice.  I might copy an old invoice but it will be much better if I could automatically fill in these cells in Excel.  And what I’m going to show you how to do now is how to use one of the cells to help to fill in many of the other cells.

Now first of all, what is it about this customer that helps us to identify their information?  And in the case of a customer of a company it will usually be an account number.  So if I have the account number, in this case 2973, then I can use that account number to find the other information.  Now let me just flip back to the Customer Sheet again.  On the Customer Sheet the account number is in the first column.  What I’d like to be able to do, go back to the invoice, is if I instead of putting 2973 here put say 2972, it would bring up the other information for the other customer.  So there was a customer 2972, that’s a company called Bathtime.  There’s my contact there.  I’d like to automatically bring up that customer’s details and create an invoice for them if I put their account number on here.  Let me just put that back to 2073 and then we’ll see how we do that.

Now the first field, the first cell that I’m going to apply this principle to is that one, D5.  It’s currently the name of my contact at the customer company.  It’s currently got typed into it Zak Stephens, but instead of Zak Stephens I’m going to put in there a formula.  And the formula will actually be a function.  Click on equals, go to Formulas, and the sort of function it’s going to be is a Lookup Function.  Now Lookup and Reference Functions are ones that get information basically from elsewhere.  And the Lookup Function we’re going to use here is one called VLOOKUP.  There are two direct functions.  There’s a VLOOKUP function and an HLOOKUP function.  We’re going to go for VLOOKUP.  Just read the screen tip there.  Looks for a value in the left most column of a table and then returns a value in the same row from the column you specify. By default, the table must be sorted in ascending order.  Now we are going to be using account number, therefore one of the requirements is that this table of account numbers must be in ascending order.  That’s pretty straightforward because we can always sort a table if we need to.

So it’s the VLOOKUP function that we want.  Now when you choose VLOOKUP in Excel 2013, it brings up this very convenient dialog and we can use the dialog to fill in the rest of this.  Now, first of all, what’s the value we’re going to look up?  Now the value we’re going to look up is the account number which is in C17.  If I click in there, where is it going to find it?  Now where it’s going to find it is in the Customers Sheet.  So if I click on the Customer Sheet and select from the beginning of the table of customer information down to the end, it’s a very small amount of information at the moment.  But if I select that, it gives me the table array.  What it’s saying is I want you to look up what’s in C17 in the table.  It doesn’t actually have to be setup as a table.  It can just be a range.  The table, customers, that’s on the Customers Sheet, A2 to G5.  And as you can see from the marching ants, A2 to G5 covers all of my customers.  Now when VLOOKUP does the look up, it always looks up in Column 1.  It will look for whatever’s in C17 on the Invoice Sheet in Column 1, so it will only look in the account numbers.  But the next question is which column will the answer be in?  Here I want in this particular cell the customer contact name and the contact name is in Column C.  So which index number that’s 1, 2, 3.  That is Column 3, click on OK, and see what I get.  I get Zak Stephens.  Zak Stephens is, that’s the value but the formula is equals VLOOKUP.   C17, that is what do you want to look up?  I want to look up the account number.  There it is, 2973.  Where do I look it up?  You look it up in customers bang or exclamation mark, A2:G5; so it’s on the Customer Sheet and it’s in the range A2 to G5.  Which column number?  Column 3.

Simon Sez IT computer training course

So far, so good.  Let’s see if that works.  Let me click in the C17 field and let me now change that from 2973 to 2972, tick, and what I get now is Amit Nehraj in the contact name field.  So it’s automatically and let’s do another one, 2971, tick.  It’s automatically changing the name here when I change the account number there.  Now you can obviously see what the problem here is and that is that we’re sorting out the contact names automatically based on the account number but we’re not doing everything else.  And that’s where there’s a little job for you to do on some of the rest of this invoice.

So what I want to do now is to just take another look at that Customers Sheet again.  Note the columns there:  account numbers are in Column 1, company name in Column 2, contact Column 3, terms of business in 4, discount in 5.  The first two lines of the address in Column 6 and 7 and there could, of course, be many other columns with other information about this particular customer.  But I’m only going to use these at the moment. And we’re going to now look at the terms of business column.

Now let’s suppose that the next thing I’m going to do is to put a similar Lookup in here.  One option is to literally copy that cell.  You might look at it and think, well, I’m copying Dorothy Walinski.  I don’t want it to say Dorothy Walinski but don’t worry about that at the moment.  Let’s do a Control-C to copy it and let’s do a Control-V to paste it and, of course, we get an error.  Now, hopefully if you look at what’s in the formula bar, you can see what the error is.  Because the error is that with this Lookup, when I’ve moved the Lookup to a different cell, it’s basically used its referencing to change not only the cell reference for the item to look up, the account number in this case, but also the range on the Customer Sheet for the information about customers.  So let me undo that and let me go back to the original formula for Dorothy and you should know that what we need to do there is to put in absolute references.  Now we can get over it to some extent in terms of the Customer Sheet by referencing it as a table, but let’s put in the absolute references.  Just tick that to make sure that Dorothy still works.  Dorothy still works.  Let’s do a Copy.  Let’s Paste that in here.  Well that’s better because we now get Dorothy in there.  But you should know why we get Dorothy in there and that is because if I tick in this particular cell, so I’m now in E18, the terms of reference are not in Column 3, they’re in Column 4.  So if I change that 3 to a 4 and tick and I get 7 days for Dorothy Walinski.  Now bear in mind this is 7 days for account number 2971.  If I go back to customers 2971 terms are 7 days.

So what we have here is Example 13.  We’ve just effectively automated the terms and the contact name.  What I’d like you to do is to do the same for the name of the client company and for the first two lines of their address.  Now obviously this will still be a rather strange invoice because part of it, such as the phone numbers, won’t change.  But if you were feeling really, really keen and enthusiastic and if you’ve got the time, you could maybe extend that table out to include telephone numbers or anything else indeed that should be specific to individual customers.  You’d, of course, have to change your VLOOKUPs to use the bigger table.  But all I really need you to do is to have a go at the name of the company and the two lines of the address, address line 1 and address line 2.  And then you can test it as here just by changing the client number.  If I put this back to 2973 watch what happens in D5 and E18.  When I change that, I get the contact name and the terms change back again.  So my answer to that question is Example 14 and I’ll see you in the next section.

Simon Sez IT Excel training

Get our complete Microsoft Excel 2013 training courses for beginner, intermediate and advanced learners. Click here to learn more. >>

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

MASTER MICROSOFT EXCEL AT SIMONSEZIT.COM

START MY MEMBERSHIP

Similar Posts

Working with User Accounts in Windows 8

Making Changes to Resources in Microsoft Project

Inserting Pictures in PowerPoint 2016

Advanced Formulas in Excel (1-Hour Recorded Webinar)

QuickBooks Workforce – Pricing Plans and Much More [2022]

How to Subtract in Excel?- 4 Different Scenarios

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)