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.

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:

Header background Image

Company name

Company details

Invoice Number

Invoice Date

Details of client Name

Product Description

Item Description

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.

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 :

Creating a Histogram Using Data Analysis Tool pack

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.

Steps to create a Histogram chart in Excel 2016:

Select the entire data set.

Click the Insert tab.

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

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.

Let’s start its
customization process:

Customize this chart by right-clicking on the vertical axis and selecting Format Axis.

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

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.

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.

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.

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.

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:

Click the File tab and then select ‘Options’.

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

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

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

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

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.

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:

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.

In the ‘Data Analysis’ dialog box, select Histogram from the list

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.

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

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.

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.

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.

Important:

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.

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.

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.

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.

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 😛

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.

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.

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.

Click the Insert tab in the ribbon.

Click on the Symbol icon

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).

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.

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:

Using Data from Cells.

Entering Data Manually.

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.

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

Select a cell where you
want to create the drop down list.

Go to Data –> Data Tools
–> Data Validation.

3. In the Data Validation dialogue box, within the Settings tab, select List as the Validation criteria.

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??

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

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.

About Deepak Jha

I am a Marketer & passionate Excel practitioner. I am using MS excel from past 6 years in my daily routine work, I have tried many things to automate my excel work so i thought why not I help others to automate their work through excel so I started You Learn Excel blog. Just to make things easier for others.