Excel for Commerce | Blog | Page 3

01Jan2015: How to sort horizontally in Excel? (from left to right)

Sorting horizontally in Excel:

Sorting horizontally in excel (from left to right) is required when we have a table/data as shown in the image below. In the below image, we want to sort horizontally based on employee name. This blog explains how to sort multiple columns horizontally from left to right. Let’s take a sample data where we have employees with the name John, Adam and William with their sales figures for the months January to June. As…

15Dec2014: Time Card Calculator – Payroll template (with California Overtime rules)

Do you handout payslips to your employees on a weekly basis? Are you having a tough time calculating the pay based on California Overtime rules? Wondering how does the 7^th day rule work in California?

Then this time card calculator is for you. It will help you in calculating weekly payroll taking care of all the rules. It takes care of California Overtime rules (labor laws) and also the 7^th consecutive working day rule.

Below…

09Dec2014: How to use SUMPRODUCT function in Excel

SUMPRODUCT function in Excel returns the sum of the products of the corresponding ranges or arrays. Let’s check the syntax for this function.

Formula Syntax: SUMPRODUCT (array 1, , …)

The arrays in the formula are the ranges of the cells that we wish to multiply.

SUMPRODUCT function in Excel:

Let’s take an example, we have a data with number of units sold and the price per unit from January…

02Dec2014: How to create dynamic dropdown list in Excel?

Dynamic dropdown list in Excel:

Dynamic dropdown list in excel falls under Data Validation. This can be accessible in Data Tools section in Data tab in excel ribbon as shown in the image below. Data Validation picks from a list of rules to limit the type of data that can be entered in a cell. dynamic dropdown list will limit the choices by using the named ranges and we need to use INDIRECT function to create the list.

22Nov2014: Excel shortcuts: Tips and tricks to speed up in Excel

Learning Excel keyboard shortcuts will improve your speed and productivity in a big way. Most of the daily routines in the excel worksheets can be done using keyboard shortcuts. This blog contains nearly all the shortcuts which can be helpful while working with Excel.

Excel shortcuts and tips:

How to know the Excel shortcut key combinations (Alt + []):

Shortcut keys can be accessed by hitting ‘Alt’ key on the keyboard. After hitting ‘Alt’ key, the next combination keyword…

18Nov2014: INDIRECT function in Excel: How to dynamically refer multiple sheets?

INDIRECT function in Excel returns the reference specified by a text string. The syntax for this excel function is as follows.

Formula Syntax: INDIRECT( ref_text, )

ref_text can be the range of cells or a named range.

can be TRUE or FALSE. TRUE is used to indicate that ref_text is in R1C1 style and FALSE is used to indicate ref_text is in a1 style. If we omit this argument, it assumes ref_text…

13Nov2014: Introduction to Advanced Filter in Excel

Advanced filter in Excel depends on the user defined criteria that can applied to many columns of data. Advanced Filter is used to perform complex filtering which is not possible using the basic auto filter function.

To access Advanced Filter, click on DATA tab in Excel ribbon and click on Advanced in sort and filter section as shown in the image below.

In…

11Nov2014: How to apply Conditional formatting in Excel?

Conditional formatting in Excel is used to highlight a cell or value/text in the cell, when certain conditions are met. Conditional Formatting can quickly spot trends and patterns in your data with bars, colors and icons by visually highlights important values.

Conditional formatting is based on the cell rules we amend. We can do a whole lot of things with this type of formatting. Pre-defined rules are available to select from the list to be used…

15Oct2014: How to use LOOKUP functions in Excel – by our Excel Expert

The process of looking up specific values in a data set is the most common task in Excel. If your intent is to become an Excel Expert someday then you will need a good understanding of all the lookup formulas available in Microsoft Excel. There are 6 lookup functions in excel: LOOKUP, VLOOKUP, HLOOKUP, (INDEX & MATCH) and OFFSET.

Once we have a data repository in Excel, we may want to generate reports or extract meaningful…

09Oct2014: Is Wrap text not working in Excel? Read the following blog by Excel Expert.

Is wrap text not working in Excel? The reason is Microsoft Excel cannot wrap merged cells automatically. We need to follow a few steps to make the wrap text work in Excel. Go ahead and read the blog to see how the issue can be resolved.

Step 1: Merge the cells and enter the text

In this example, we merged cells E1 and F1 and entered text as “How to wrap text in the merged cell using a simple trick in Excel”. As…

Dynamic dropdown list in Excel: