Blog

What are Excel Functions?

Excel Functions – Data Form

Many users use Excel to manage lists in which the information is arranged in rows. Excel offers a simple way to work with this type of data through the use of a data entry from that Excel can create automatically.

Data Form command is not available in the Ribbon, to use the data form, you must add it to your Quick Access Toolbar (QAT).

1. Click on the drop down menu arrow of QAT and select More Commands. The Customize panel of Excel Options dialog box appears.

2. In the Choose Commands. From drop down list, select Commands Not in the Ribbon.

3. In the above dialog box select Form….. -> Click Add button -> Finally Click ok to close Desired ‘Form’ option is showing as icon on the top left bar just right side of the office tool.

Use of FORM tool

It will help to fill new data under existing Database. Besides you can delete or find any data based on your criteria.

  • Arrange the data so that excel can recognize its as a table.
  • Select any cell of your table and click on Form button on your QAT.
  • Excel will then show the Form dialog box as shown in the figure. Supply the required information and click on New to add a new record.
  • The formula fields are not editable through the data form.
  • You can use Delete button to delete a record. Find buttons to find a record, set criteria using Criteria button to find a record.

Information Function

ISBLANK ()

This function is used to check whether the cell contains a blank or null value. The syntax of the function is =ISBLANK(Value).

Let us check the function with a simple illustration. We have a worksheet that has three columns showing Bill Amount, Date of Payment, and Day of the Week on which the bill has been paid. In the figure below it can be noticed that we have used Text() function [=Text(B2,”ddd”)] to fin out the day of the week. But in spite of that the bill has not been paid (bill amount 3200), the day of the week showing Saturday (ref. figure).

To overcome this problem we just rewrite the formula using IsBlank function. Figure above showing the use of IsBlank() to get rid of the blank cell. The formula is: =IF(ISBLANK(B2),””,TEXT(B2,”dddd”))..

Logical Function

IFError()

This function is very effective to track and handling error. Through this function an error can be trapped and the error value can be replaced by a subsequent value. The syntax of the function is :IFERROR(Value, value_if_error).

Example 1

We have a dataset of cost of certain products and quantity used of these products. However, quantities of some items are missing (Over here only one). Now our task is to find out the cost per product/item.

So we took the easiest way by dividing cost by quantity. But there was an error (it can easily be noticeable on cell C3[#DIV/0!]) because the quantity of this product was not available. So we tried little harder yo solve this problem. We just not divided but checked whether it is resulting an error. If there is any error that would be replaced by 0 otherwise cell value would be the division of cost and quantity. The function would look like this: =IFERROR(E2/F2,0)

Custom Excel Templates

Template is basically a pre defined document. This can be used for many more time.

Template Creation

  1. When your workbook is set up to your liking, choose Office -> Save As.

Importing Data

Excel has some great analysis and presentation tools, but these tools require data. In many cases, the data that you need is available in an external database. For example, your company may have a database that contains customer information, sales data, and so on.

In Excel you can import any data from external data sources, like Access, Word, and Internet etc.

Importing Data from Tally

We can import data from Tally also. To import from Tally you need to open the company along with your excel file.

  • To import tally data, choose Data Tab -> Get External Data group -> From Other Sources -> From Microsoft Query. The following dialog box will appear for the database selection as. As Tally is opened you can notice TallyODBC_9000 is added in the list. Select TallyODBC_9000 from the list and click on OK.
  • In the next screen the dialog box will show you the available tables related to the tally company you opened. Select ledger table and click on the arrow as shown in the figure. Click next to get the next dialog box of the wizard.
  • In this screen you can filter data coming from the source. Let us filter the data in such a way to get only ledgers whose group is Sundry Debtors or Sundry Creditors. Select $Parent from Column to filter list and set the query as shown in figure. Click on Next
  • Now you can sort the table through the options given. Here we have selected Name for first order sort and Parent for the next order sort. Click on Next.
  • In the next screen selected the option Return Data to Microsoft Office Excel and click on Finish.
  • The screen after will ask for your viewing preferences and place where you want to your data. Select Table as viewing preference and set the first cell of the current worksheet as a place to put your data.