Free Invoice Template In Excel

In this article we will learn about Invoice Template In Excel. If you are a small business or a freelancer, then you can use excel to create your invoice.

Excel gives you the ability to create your personal invoice to send clients/customers. As we use excel in our daily work so creating an invoice is not going to be tough for anyone.

invoice template for excel

It is easy to create an invoice template in excel but you have to have some knowledge about Macros, but it you don’t have macro knowledge then don’t worry just download the below given template and start creating invoice.

Personalize Excel Invoice Template: There are so many things through which you can make your branded excel invoice and make it more professional as per your brand.

Things you can Edit:

  1. Header background Image
  2. Company name
  3. Company details
  4. Invoice Number
  5. Invoice Date
  6. Details of client Name
  7. Product Description
  8. Item Description
  9. Product Amount

It’s easy and fast, don’t have to be so techie. Don’t wait and download the invoice template excel through below link and start using it now.

Let me know your experience in comments and do share with people who need this template like your Freelancer friends, Small business owners, Momprenuers etc.  

You May Also Like the Following Excel Tutorials:

How to Make a Histogram in Excel (Step-by-Step Guide)

Histogram is basically an data analysis tool which is available in Excel and you can use it whenever you are doing any kind of data analysis for available date set.

In simple words , A Histogram is Column Chart which is a built-in option of MS Excel. In Excel 2016 it’s available as a chart, but if you are using Excel 2013, 2010 or prior versions then you can use one of these methods :

  1. Creating a Histogram Using Data Analysis Tool pack
  2. Creating a Histogram using FREQUENCY Function

I promise you that I won’t be that hard, you just need to follow the below given methods.

Let’s jump in the process of creating histogram in excel:

Creating a Histogram in Excel 2016

If you are using the excel version 2016 then it will be few click away to create histogram for available data set as it is a built-in chart in Excel 2016.

Don’t worry if you are using Excel 2013 or prior version, as next two ways will help you in creating the Histogram in excel 2013 or prior version of excel.

Let’s assume we have below given data set and we have to create a Histogram chart for the below data set.

Students-Marks-to-create-histogram-in-Excel-2016

Steps to create a Histogram chart in Excel 2016:

  1. Select the entire data set.
  2. Click the Insert tab.
Insert-Tab-in-Excel-Ribbon

3. In the Charts group, click on the ‘Insert Static Chart’ option.

Insert-Static-Chart-Option-in-the-Ribbon

4. In the Histogram group, click on the Histogram chart icon.

The above steps would insert a histogram chart based on your data set (as shown below).

As we have the Histogram chart for our available data set, so now we have the option to customize it as per our needs.

Resulting-Histogram-Chart-in-Excel-2016

Let’s start its customization process:

  1. Customize this chart by right-clicking on the vertical axis and selecting Format Axis.
Format-Axis-for-Histogram-Bin-Options

2. This will open a pane on the right with all the relevant axis options.

Bin-Options-in-the-Histogram-Chart-in-Excel-2016

Histogram Chart Customization Options

 Once you have the Histogram chart then you have multiple options to customize it and present it as per your needs. Some of the well-known customization options are as follows:

Histogram Chart Customization by Category

You can customize Histogram charts by using category option, but you have to make sure to use it only when you have text categories.

For example, if you have online store or offline store with multiple products category, Now you want to know the sales data for each product category then you can use the histogram chart option and calculate the sum of sales for each category.

This could be beneficial when you have multiple repetitions in categories, and you want to know the total sum or total count of the categories.

Histogram Chart Customization by Automatic: This is one of the default options in Histogram chart, as when you create your histogram chart for your data set then it automatically creates Bins for you.

In our example it created 4 bins, but its upto us how many bins we want in our chart. We can change this by using the ‘Bin Width/Number of Bins’ options.

Histogram Chart Customization by Number of Bins

You can use this option to decide the number of Bins you want in your chart. If you specify 2 Bins or 6 Bins or 10 bins then it will automatically create your chart in your mention Bins.

Specifying-Number-of-Bins-in-histogram-chart

You have to careful that you only define either number of bins or bins width, don’t define both options.

Histogram Chart Customization by Bins Width

This option will help you in defining the width of Bins, you can decide how big you want your Bin.

Specifying-Bin-width-in-histogram-chart

Let’s create a chart and define Bins Width to 20, so it will create Bins as 36-56, 56-76, 76-96, 96-116 and it will look big as compare to normal bins.

Histogram Chart Customization by Overflow Bins

This option will give you the customization option through which you can define certain value and above that value all other value will be clubbed together in a histogram chart.

Overflow-Bin-Value-in-Histogram-Excel-2016

In our student data set, if we want to know that how many students have scored more than 60 or 70 or may be more then 75 then we can define overflow bin value to 75 than we will get similar chart as shown below.

Histogram Chart Customization by underflow Bins

This is similar to Overflow Bin but works opposite, underflow bin will help you in finding the low scored students.

Let’s say we want to know how many students have scored less then 40 percent, so once we define the underflow bin to 40 then it will show chart as given below.    

Underflow-Bin-Value-in-Histogram-Excel-2016

If you can want then you can further customize it by changing the title, removing grid lines, changing colors, etc.

Finally, you have your Customized Histogram Chart in Excel 2016, now let’s explorer other methods of creating the Histogram Charts.

Creating a Histogram Using Data Analysis Tool pack

I call it as all-rounder method of creating histogram chart, as it works with MS excel 2016, 2013, 2010 and all the prior version of excels.

If you ask me then I will suggest using Built in Histogram chart if you have Ms excel 2016.

To create a histogram chart using Data Analysis tool pack, you first need to install the Analysis Toolpak add-in in excel.

This add-in will enable you to quickly create the histogram chart in excel by taking the data and data range as inputs.

Install Data Analysis Tool Pack in Excel

To install the Data Analysis Toolpak add-in:

  1. Click the File tab and then select ‘Options’.
Histogram-in-Excel-Options

2. In the Excel Options dialog box, select Add-ins in the navigation pane.

Histogram-in-Excel-Addins

3. In the Manage drop-down, select Excel Add-ins and click Go.

Histogram-in-Excel-Excel-Addins

4. In the Add-ins dialog box, select Analysis Toolpak and click OK.

Histogram-in-Excel-Addins-dialog-box

This would install the Analysis Toolpak and you can access it in the Data tab in the Analysis group.

Histogram-in-Excel-Data-Analysis-Tab

Creating a Histogram Chart in Excel using Data Analysis Toolpak

After you install Data Analysis Tool Pak, now you can histogram chart in excel.

Let’s say we have a data set as shown below. As we are taking the same data set that we have used in our above examples, so it won’t be a complicated data for us.  

Students-Marks-to-create-histogram-in-Excel-2016

Now to create Histogram chart by using this data set, we need to define the data intervals in which we want to find the data frequency. These can be called as Bins.

You need to specify these bins separately in an additional column as shown below:

Create-Histogram-in-Excel-bins

As we have all our data set so now let’s create the Histogram chart.

  • Click the Data tab.
  • In the Analysis group, click on Data Analysis.
Histogram-in-Excel-Data-Analysis-Tab
  • In the ‘Data Analysis’ dialog box, select Histogram from the list
Histogram-in-Excel-Data-Analysis-Dialog-Box
  • Click OK.
  • In the Histogram dialog box:
    • Select the Input Range (all the marks in our example)
    • Select the Bin Range (cells D2:D7)
    • Leave the Labels checkbox unchecked (you need to check it if you included labels in the data selection).
    • Specify the Output Range if you want to get the Histogram in the same worksheet. Else, choose New Worksheet/Workbook option to get it in a separate worksheet/workbook.
    • Select Chart Output.
  • Click OK.
Histogram-in-Excel-Histgram-dialog-box

You will have the frequency table and chart in your specified location.

Histogram-in-Excel-frequency-table

Note: If you create Histogram without defining the Bins then it will automatically create the bins and use your provided data set.

Creating a Histogram Chart in Excel using FREQUENCY Function

The above two methods would give you the histogram charts but what if we have dynamic data set??

Now we will create dynamic histogram by using the FREQUENCY functions.

Let’s take the same old data set that we have used for this tutorial, now we need to create the bins to show the frequency.

Create-Histogram-in-Excel-bins

Here is the function that will calculate the frequency for each interval:

=FREQUENCY(B2:B41,D2:D8)

Note: This is not a simple formula, so don’t just use ENTER. You need to use Control + Shift + Enter.

Histogram-in-Excel-frequency-function

Follow These Simple steps to get the correct data results:

  • Select all cells adjacent to the bins. In this case, these are E2:E8.
  • Press F2 to get into the edit mode for cell E2.
  • Enter the frequency formula: =FREQUENCY(B2:B41,D2:D8)
  • Hit Control + Shift + Enter.

Now the results we got; we can create the histogram chart for our data set.

Histogram-in-Excel-chart

Important:

  1. This is an Array data set, so you can’t delete a part of the array. If you still want to delete, then you have to delete all the cells.

These are the ways through which you can create Histogram in excel, if you have Excel 2016 then use Built-in option and if you have prior version or in 2016 as well you can use other two methods.

You May Also Like the Following Excel Tutorials:

How to Merge Cells in Excel (Easy Step-by-Step Guide)

Have you ever tried to merge cells in excel???

There are many ways through which you can do merging cells in excel. Let’s start this simple step by step guide.

Combine excel cells using Merge & Center option

This is one the easiest way to combine excel cells, this step take less than a minute to give you results.

combine excel cells

Merge & Center is available in Home Tab section. Most of the Excel users use this option as they find it easy to perform when merging cells in excel.

There is only one issue that I find while using the Merge & Center option that It only merge the cells but not the text that is available in those cells.

Let’s assume that we have a small data set, Btw I love GOT so I used my favorite characters name in my example.

merge cells in excel

Now If I select cell A1 and B1 and use the option of Merge & Center then it will keep the data from left most cell (in our example it will be cell A1) and remove all the data from other cells.

When you perform the cell merging action then excel will show you a pop up which gives a small warning about data and letting you know the about the outcome. Just like shown below.

merging cells in excel

Once you click the Ok button shown on popup, then it will start merging the data as in our case A1, B1 data and give you the final result by keeping the data from leftmost cell.

Merge Cells in Excel without losing data:

In the above method we use the simplest method of excel cell merge but now we will use the different approach and it will keep all our data as well.

CONCATENATE Formula will help you in merging excel cell and keep the data as well.

Let’s try this formula for my Favorite GOT data set 😛

merge cell excel

Now we are combining the cells A1 and B1 and keeping a space character as the separator. If you don’t want any separator, then leave it as it is and use the formula =CONCATENATE(A1,B1)

You have the option to choose comma, semicolon options to choose as separator.

You can also use ampersand sign to merge the excel cell text, here is the formula for that =A1&” “&B1

Merge & Center Alternative

There is an alternative of Merge & Center option which you can use to merge your excel cell, you can use this option if you want to show merged cells in different column.  This option called as Center Across Selection option.

Here is how to use it:

  • Select the cells that you want to merge.
  • Press Control + 1 to open the format cells dialogue box.
  • In the Alignment tab, in the Horizontal drop-down, select Center Across Selection.
  • Click Ok.

This option won’t show you any error or warning message.

Why I not choose  Merge & Center option

When you use the merge & center option in merge cell task then you will find that you are no longer able to sort the data set and still if you try to sort the data set which have merged cells then it will give a pop up like below image.

merge cells excel

These are the some of the ways through which you can merge the excel data set. Choose the best way as per your needs and requirements and don’t forget that when you choose Center to Across to work option, your leftmost cell must have some data.  

Read More Excel Articles to Upgrade Your Knowledge

How to Insert a Check Mark (Tick ✓) Symbol in Excel Cell [Quick & Easy Guide]

How to Create a Drop Down List in Excel (NO BS Guide!)

How to Insert a Check Mark (Tick ✓) Symbol in Excel Cell [Quick & Easy Guide]

In Excel, you will find that there are two types of tick marks (✓) that you can insert- a check mark and a checkbox.

Don’t be confuse, these two are not same.

Let me differentiate them for you J

Check Mark Vs Check Box:

At first glance you might find a check mark and a check box similar but these are not similar , both of them are very different they way we insert and used in excel.

A Check Mark is just like a symbol that you can insert in a cell, just like any other text that you insert in cell. As check mark is the part of the cell like any other text, so when you delete the cell or copy it check mark also get copied or get deleted.

You can also change check marks design by changing the font and size.

A Check Box, is basically an object which placed on the cell when you insert. Which means if you delete the cell, the checkbox may not be deleted. As I said check box is just an object which means you can drag it anywhere in the sheet as per your needs.

You can easily find check boxes being used in many excel reports and dashboards, and check mark is simple symbol that can be part of your report.

I am sure now you clearly know the difference between check box and check marks, but in this article I am only covering only check marks. You can learn more about check box here in complete guide.

As we discussed that check mark is just an symbol, there are few ways that you can use to insert a check box in your excel sheet.

Inserting Check Mark Symbol in Excel

In this article, I will be showing you all the methods that I know.

The method you choose would be completely depend on how you want to use the check mark in your work.

Let’s jump in!

Copy and Paste the Check Mark in Excel



One of the easiest method to insert a check mark in excel, just copy the below check mark symbol and paste it in your excel cell.

There are two ways through which you can paste check mark in excel cell, first double click the cell and paste it. Second, press F2 on the cell and paste the symbol.

Once you have the check mark symbol, you can copy and paste it as many cell as you want.

Note: As I told you that, this is one of the easiest method and can be use in small task or in small data set reporting. If you want to use check marks in big reports then I would highly recommend to use formulas.

Use the Keyboard Shortcuts to insert Check marks(tick marks ) in excel:

You can use Keyboard shortcuts to insert check marks in excel, you might have to adjust the font size by your own. Let see the short cuts now.

Below are the two examples of keyboard shortcuts to insert check marks and cross symbol in excel sheet.

There are some more keyboard shortcuts through which you can insert check marks, checkbox and cross marks in your excel sheet file.

Using the Symbols Dialog Box to insert Check marks(tick marks ) in excel:

There is another way to insert a check mark symbol in Excel is using the Symbol dialog box which is already available in excel.

Here are the simple steps to insert the check mark (tick mark) using the Symbol dialog box in excel sheet:

  • Select the cell in which you want the check mark symbol.
Insert-Tab-in-Excel-Ribbon
  • Click the Insert tab in the ribbon.
Insert-Degree-Symbol-in-Excel-using-the-Symbol-option
  • Click on the Symbol icon
Select-the-check-mark-symbol-that-you-want-to-insert
  • In the Symbol dialog box that opens, select ‘Segoe UI Symbol’ as the font or any other which you like the most.
  • Scroll down till you find the check mark symbol and the double click on it (or click on Insert).
Select-the-check-mark-symbol-that-you-want-to-insert

The above steps would insert only one check mark in the selected cell, you can late insert it in multiple cells as per your needs.

I know this method is quite long but you don’t need know any shortcut to perform this. Once you have one symbol, then you can just copy it and paste it in multiple cells.

Using the CHAR Formula to insert Check marks in excel:

You can use CHAR formula to return a check mark in your excel.

If you use this formula (=CHAR(252)) then you will see check mark symbol in your excel sheet cell.

Important: When you use CHAR(252), you will see ANSI character in return so to get the desired results you need to change the font family to Wingdings.

You insert Check boxes, check marks and cross marks using CHAR formulas.

The best benefit that I see to use CHAR formula is that you can use it with other formula’s to get the desired results for your table.

For example, suppose you have a dataset as shown below:

You can use the below IF formula to get a check mark if the sale value is more than 5000 and a cross mark if it’s less than 5000.

Just copy the below formula in your cell and you will get the tick mark or check mark in your excel cell.

=IF(A2>5000,CHAR(252),CHAR(251))

NOTE: you need to convert the column font to Wingdings.

If you want to remove the formula and only want to keep the values, copy the cell and paste it as value. Follow this simple step:

  1. Right-click.
  2. Choose the Paste Special then.
  3. Click on Paste and Values icon.

You can use this formula when you have thousands of cell to work on.

Using Auto correct to insert Check marks(tick marks ) in excel:

I am sure that many of you were not aware about the auto correct feature of excel. Actually excel auto correct the misspelled word automatically J

In excel there is a pre-made list of misspelled words and when you type them it got automatically auto correct.

Here are the steps to use auto correct to insert the delta symbol:

  • Click on the File tab.
  • Click on Options.
  • In the Options dialogue box, select Proofing.
  • Click on the ‘Auto Correct Options’ button.
  • In the Auto correct dialogue box, enter the following:
    • Replace: CMARK With:
    • ✔ (you can copy and paste this)

Now whenever you type the words CMARK in a cell in Excel, it will automatically change it to a check mark symbol in your excel sheet.

Important: When you apply this change in your excel file, it will reflect on other application too like Word, Power Point etc.. so make sure you choose the word correctly and will be unique.

How to Create a Drop Down List in Excel (NO BS Guide!)

A Excel drop down list is one of the best way to give users multiple options to choose from per-defined list.

Pre-defined lists are not new, these are being used in many websites for Form filling, many apps use them in user registration forms.

There are several ways through which you can create Drop down list in excel and make your data more organized.

Let’s jump in and learn the best way through which you can create drop down list in excel.

How to Create a Drop Down List in Excel

In this section, you will learn the exacts steps to create an Excel drop-down list:

  1. Using Data from Cells.
  2. Entering Data Manually.
  3. Using the OFFSET formula.

Using Data from Cells:

Creating a drop down list using data from cells, you must need a list. Let’s assume that we have a list similar as given below.

How to Create a Drop Down List in Excel

Here are the steps to create an Excel Drop Down List:

  1. Select a cell where you want to create the drop down list.
  2. Go to Data –> Data Tools –> Data Validation.
3. In the Data Validation dialogue box, within the Settings tab, select List as the Validation criteria.
  1. As soon as you select your list, the source field appears as pop up.
4. In the source field, enter =$A$2:$A$6, or simply click in the Source field and select the cells and click OK. This will insert a drop down list in cell C2. 1. Make sure that the In-cell drop-down option is checked (which is checked by default). If this option in unchecked, the cell does not show a drop down, but you can manually enter the values in the list.

BONUS 1: If you want to create drop down lists in multiple cells at one go, select all the cells where you want to create it and then follow the above steps. Make sure that the cell references are same (as $A$2) and not similar like (A2, or A$2, or $A2).

Entering Data Manually:

First we have tried to create drop down list by using cell as data source field. In this example we will directly add the data in source field as we don’t need any cell to pull the data.

  • This approach is good for small data set drop down list like, we want to give a drop down for “True or False” may be for “Yes or NO “. Let’s create a drop down list now.
  • Select a cell where you want to create the drop down list (cell C2 in this example).
  • Go to Data –> Data Tools –> Data Validation.
  • In the Data Validation dialogue box, within the Settings tab, select List as the Validation criteria.
    • As soon as you select List, the source field appears.

·  In the source field, enter True, False OR Yes, NO

  • Make sure that the In-cell drop-down option is checked.
  • Click OK.

This will create your drop down list in the cell that you have selected.

The data that you have selected in the source field separated by comma will be shown in different lines in the drop down menu list.

Bonus 2:If you want to create drop down lists in multiple cells at one go, select all the cells where you want to create it and then follow the above steps.

Excel Drop Down List Using Excel Formulas:

In the above examples we have used data from cell and entered manually but in this we will use the excel formulas to create a drop down list.

I promise you that it won’t be too hard, so follow these simple steps.

Suppose we have a small data set shown as below:

Here are the steps to create an Excel drop down list using the Excel OFFSET function:

  • Select a cell where you want to create the drop down list (cell C2 in this example).
  • Go to Data –> Data Tools –> Data Validation.
  • In the Data Validation dialogue box, within the Settings tab, select List as the Validation criteria.
  • As soon as you select List, the source field appears.
  • In the Source field, enter the following formula: =OFFSET($A$2,0,0,5)
    • Make sure that the In-cell drop-down option is checked.
  • Click OK.

This will create a drop-down list that lists all the fruit names (as shown below).

Bonus 3: If you want to create a drop-down list in multiple cells at one go, select all the cells where you want to create it and then follow the above steps. Make sure that the cell references are same (as $A$2) and not like (A2, or A$2, or $A2).

Behind the scene-How OFFSET formula Works??

Excel drop down list

As you know that in above example, we used an Excel’s predefined OFFSET function to create the drop down list.

It returns a list of items from the range A2:A6.

Here is the actual syntax of the OFFSET function: =OFFSET(reference, rows, cols, [height], [width])

It takes five elements, where we have specified the reference as A2 (the starting point of the list). Rows/Cols are specified as 0 because we don’t want to offset the reference cell. Height is specified as 5 as we have taken five elements in our list.

Now, when we use this formula, it returns an array that has the list of the five fruits in A2:A6.

Note: You can check the array then just enter the formula in a cell, select it and press F9, you would see that it returns an array of the fruit names that you have taken in your list.

You May Also Like the Following Excel Tutorials: