Variance Charts in Excel Sales vs Targets or Actual vs Budget

What is Function in Excel ?

What is function?

The definition of Function is formulas that are predefined, user need to enter only the arguments to get the desired result.

Let us understand what Function is and what could be the proper definition of Function using simple example. Suppose we need to find out the maximum of any number series. We will use MAX function; we will type MAX and within the bracket, we will enter the number series and we will get one of the numbers which is the maximum number from these number series. Here MAX() is a function.

Excel has many functions, some of them needs to be feed by arguments and some are NOT.

Also, few functions don’t need to be feed with arguments but are also known as functions, like TIME() and NOW().

Generally, a function ended by opening and closing bracket where we put our arguments and we call them parentheses (brackets).

Therefore, the definition of function needs little modification, and we can define function as ‘function is formulas which is predefined and may or may not require arguments.’

Why use Function?

Human tendency is to reduce the repetitive and frequent jobs. Suppose we need to calculate the SUM of few numbers, to get the sum we have to write 10 lines of (programming) code. (A programming code is set of instructions to the computer, the code uses the compiler to converts them to zeros and ones, the language of computer and the computer CPU responds accordingly.)

So, every time we want SUM of two or more numbers, we have to write 10 lines of the same code. Now imagine, if we write these 10 lines of codes somewhere and name then as SUM and whenever require we can call SUM and which in turn will call those 10 lines of programming codes and we can avoid writing 10 lines of codes again and again.

We need to modify the definition of Function again.

The definition of Function should be like, “Functions are predefined formulas which are set of programming codes to evaluate some calculations using arguments which may be or may not be required.”

One more thing about functions, there are two types of Functions; inbuilt which comes with any application, like Excel and can also be created using programming tools like C, C++, JAVA etc. In  Excel, Functions can be created using VBA coding which we will learn soon.

Example of function which takes arguments are SUM(), COUNT(), IF() etc. and example of functions which may not require arguments in excel are TIME(), NOW(), DATE() etc.

I hope I am able to make you understand what function is.

Thank you for reading. Happy learning.

Slicers in Pivot Table - Quickly Filter Data

 If you are using pivot table reports for management or even for your own analysis and, you are not using Slicer then, you are not fully utilizing the pivot table report.

In this article I will discuss about slicers in pivot table. How to insert slicer, how to customize slicer, where and when you need to use slicer.

We can use slicer both in Pivot tables and Tables.

But what is slicer?

Slicer is a tool which provides buttons, that you can click to filter tables, or PivotTables. Slicers are a powerful new way to filter pivot table data.

It's very easy to add Slicer in your pivot table. Let understand this tool in this pivot table.

Slicer in Pivot Table or Table

To insert slicer, go to the Analyze menu in the ribbon, then, click the button Insert Slicer. A new small Insert Slicer window appears.

Slicer Menu

If you notice carefully then, you will find that all the table headers are showing in this window with check boxes in beginning.

Let suppose, we choose Segment. 

A new small floating window appears, and all the segments are showing in this window, like, Channel Partners, Enterprise, Government etc. These are all buttons. 

Now, if you click on any button, say for example, Channel Partners then all the relevant data regarding Channel Partners will be shown and the rest data will be hidden.

Slicer in Pivot Table Selecting One Button

Now, if you need enterprise, click Enterprise button the data will show the related data. I hope you have understood the working of slicer in pivot table.

Let us check this drop-down list, it is showing something is selected from the entire list. Click on this you will note that Government segment is selected and rest are deselected, therefore, slicers acts like filters and it is very easy to handle.

Slicer as Filter

If you need to display all the segments then click on this clear filter button with delete sign, top right corner. All the buttons will be available for selected again. 

Clear Slicer Selection

There is a small button in the left side of the filter button, this is Multi-select button. If you want to use multiple buttons from slicer, then use this button to activate multi select. Let us do it.

Click any one button first then click this multi select button to activate, and then click on another button, both these buttons will be selected.

Please remember, firstly you need to select one button from all the slicer buttons then click on the Multi-select button, otherwise this will deselect the buttons you click.

Alternatively, you can use my method, I select any one of the buttons then I hold press the control button and then select the next buttons, like this. You can use the method which is comfortable to you.

You can use multiple slicers, more than one slicer. 

Select any cell in pivot table, then go to Analyze menu and again click on the Insert Slicer button. Insert slicer window will appear again. Select as per your need. Here I will select Name.

For example, if you want to show the performance of Johnson, click Johnson from the Name slicer, all the relevant data related to Johnson will be displayed, from this if you want to show only Government segment data, you only need to click on the Government button from the Segment slicer.

The pivot table will show the data of Johnson only for Government business.

Let me discuss few more things which are very useful in slicer. I have few excel sheets in the same workbook; these are copied from the same pivot table.

Now in this pivot table, we have already selected Johnson and Government but these sheets are not reflecting the changes made by slicers in Sheet 1.

If you want to affect the changes in Sheet 2 or Sheet 3 or Sheet 4, you have to connect this slicer to other pivot tables. This is very easy process.

You have to instruct slicer that these sheets are to be linked by a connection.

Click on any slicer and you will find a new menu as Option at the last of the menu items. Click on this button, Report Connection.

Slicer Report Connection

A new window, Report Connections and within bracket your slicer name. Here you will see the list of your sheets along with the pivot table name, in our case pivot table name is PivotTable1. Tick the checkbox of Sheet 2. Click OK. Now your slicer is connected with Sheet 2. Whatever selection you made in Sheet 1 slicer it will display the same selection in Sheet 2.

Remember, it is not mandatory that all the pivot table should be of same structure to use slicer to control multiple tables. You can design pivot tables as per your need, filters will be applied as per the slicer.

Now I will discuss few basic settings in slicer. You can change the color of slicer from slicer styles, preformatted styles are available in the Slicer Style group, choose any, the color will change.

Slicer Styles

If you need to show the slicer items in more than one column, you can do it through Format slicer. Right click on the slicer and select the second last option Size and Properties option. Format slicer will open, from position and layout option you can increase the number of columns, you can fix the position of the slicer and many more.

You can change the caption of the slicer, select any slicer and click Slicer Settings from this button or you can get it from the pop-up menu after right click.

Format Slicer

This is very good tool in pivot table and Table. Please use this tool in your pivot table report, it will increase the value of your report and the navigation in the report will be easy.

I hope you have learned how to use slicer in pivot table, please feel free to ask any question in the comment section. I will respond to your question.

Thank you for reading.

End Date of The Month : EOMONTH Function

Suppose we have to get the number of days from a certain days given few months and the day will be end of the last month.

Like the image below.

EOMONTH Function

If we break our problem,

            1.  We need the end date of the month or the last date of the month

            2.     The last month will be current month and the number of months provided.

            3.     Next step is to get the difference between the Start date and the ‘Month end Date’

In this scenario, EOMONTH function needs to be used.

 

Excel has a function which will give the last date of the month, supplied number of months.

 

The EOMONTH function is a built-in function in Excel which is a Date/Time Function.

 

Syntax :

 

The syntax for the EOMONTH function in Microsoft Excel is

 

=EOMONTH( start_date, month )

 

Parameters or Arguments

 

start_date : A valid date to return the day.

This function can work after the start date or before the start date.

Month : Count of months in number

Returns : The EOMONTH function returns a serial date value

Therefore, please remember to format the cell or range of cells as date.

In this above example, we need to type the formula in the C3 cell.

 

The formula is =EOMONTH(A3,B3).

 

EOMONTH Function

You can notice one month is given negative sign. It will calculate the date before the start date.

 

Thank you for reading...

Free Macro to Send emails with Attachments

Hello Friends,

No discussion on Excel today (!), rather I am giving you one FREE MACRO developed by me to send bulk mails which can be downloaded from the link below this article.

" Please ask for a copy of the macro in the comment box, I will send the link to download the macro free of costs. This is to protect the macro from some malicious cracker. "

"Please remember Cracking a Macro is Easy but making a Macro needs your Planning, Imagination, Knowledge and Skill..." . I will let you know hoe to make this step by step, but it will take time to master Excel like a Pro with compact knowledge.

    Auto Mailer Macro Free

Why I made this Excel Macro or Excel VBA Code ?

Recently I needed to send few mails with recipient’s data, I thought I could use Mail Merge to get the job done. Yes, it is done but “How to mark ‘CC’ ?”.

I didn’t find it. I Google it, nope, no help.

So, I thought I could use Excel Macro (VBA) to get the job done. Three hours it took to complete this macro using which I can send bulk email from excel with attachment.

After that I made some improvements and decided to provide this excel macro to send emails with or without attachments as FREE DOWNLOAD to you all because I understand the pain you feel sending emails one by one.

I have given you the option to send one universal attachment (Planning to give the option to attach different attachments for different recipient) in this excel macro.

One Request

Please DO NOT use this free macro to send spam mails. It is your responsibility how to use it.

Also, I am not responsible for any problem which may arise after sending mails. This macro is tested by me at least 100 times and I am getting 100 times positive result. Please check two or three mails before sending final mails.

This is a free excel macro to send emails automatically once the data is feed in the excel sheet, do not sale or do not purchase from anybody.

Please write to me in the comment box to improve this Excel Macro – Automatic Mails from Excel without using MS Word Mail Merge. I will add some more features to this FREE MACRO.

Please read this GUIDELINES carefully then enjoy your Office Work.

DOWNLOAD FREE email MACRO HERE   

v This is free version-1 macro to send emails using Outlook where you can’t send (?) mails in CC or BCC; unlike other Add-ins you don’t need to install

v As this is a macro, you need to ‘ENABLE’ macro clicking ‘Enable Macro’ in options below Formula bar or you can do this using File à Options à Enable macro.

v You will notice 25 columns of which one is ‘Identifier’ which is not being used in this macro (you can use it for your understanding) but append your recipient’s mail ids in TO, CC & BCC column header.

v Please note that do not place any special character including space(s) in the TO, CC, BCC column, leave it blank.

v After entering data in the first 3 or 4 columns you can enter data which you need to plot in the mails. You can use maximum ‘21’ field in the mail body as per this version.

v It is strongly recommended to use your Own Field / Column names replacing Data_1, Data_2, Data_3 etc., to post the required data in the Mail Body.

v After cross checking the data click on the button named Click Me for Show Control Panel.

v In the Auto Mailer system

Ø Enter No of e-mails to be sent

Ø Enter the Subject Line

Ø Start typing your mail in ‘Mail Body’ space or you can alternatively copy and paste from MS-Word (RECOMMENDED) or any document.

Ø Place your Cursor where you want to place your appropriate data, tick the data from ‘Insert Data’ data feeder.

Ø You can add one universal attachment in every mails.

Ø Please cross check and click on Send Button to start sending emails.

DOWNLOAD FREE EXCEL MACRO SEND BULK EMAILS

Please ask for a copy of the macro in the comment box, I will send the link to download the macro free of costs. This is to protect the macro from some malicious cracker.

Thank you… Keep visiting and commenting…