Top 10 Excel hacks for your Business

Ever used Excel? Well, most of you would say ‘Yes’ to this question. Excel is an important tool for every kind of business and it completes every requirement.

If you are ever in a situation, where you are doing a lot of manual work in the excel sheet then be sure that you are missing out on Excel Formulas. To keep you away from such situation, here are the Top 10 Excel Hacks that every business should know.

1. Text to Columns

What if you want to split the information present in one cell into two different cells? For instance, if you want to split the first and last name of a person, or extract company name from the email ID of a person. By using excel both of these functions are possible, just follow these easy steps:

• Select the cell that you want to split.
• Next, go to Data Tab and select ‘text to columns’ option.
• Select if you want to go with ‘delimited’ or ‘fixed width’

Delimited: Breaking up the cell into columns based on special characters like space, tab, @, commas, etc.

Fixed Width: Gives you the flexibility to split each cell at a different location. This option will ask for the location of split for every cell in the column.

2. Adding more than 1 row or column

As we start working on Excel, we realize the need to add more rows and columns, sometimes this addition can reach to hundreds. To avoid adding one by one, there is a simple way to do the same:

• Select the exact number of columns or rows you want to add in the sheet.
• And then right click and select ‘insert’.

3. Conditional Formatting formula

Conditional formatting formula allows you to change the color of the relevant cells on the basis of information in the cell. For instance, if you want to flag the numbers that are higher than 50%, that can be done.

• Select the group of cells where you want to do conditional formatting.
• Then, choose “Conditional Formatting” from the Home menu and select the logic from the drop-down. This will help you either select the pre-existing conditions or make your own rule.
• Select ‘ok’ when done and the results will appear.

4. If statement

If the statement is a tool that helps you to insert value in a cell based on existing data if the statement is true or false. For instance, if you have a supply list of 1000 items and you want to write high, low and medium in front of them based on their quantities then you can use If statement:

• =IF(logical_test, value_if_true, value of false)

Eg: =IF (size>1000, “high”,”0”)

• Hence, if the size of the consignment is less than 1000, the value that will appear in the cell will be “0” else “high”.

5. Dollar Sign

When you copy a relative formula from one cell to another, it’ll adjust the values in the formula based on where it’s moved. But sometimes, we want those values to stay the same no matter whether they’re moved around or not – and we can do that by making the formula in the cell into what’s called an absolute formula.

To change the relative formula (=A5+C5) into an absolute formula, we’d precede the row and column values by dollar signs, like this: (=$A$5+$C$5).

6. VLookup

What to do if you have two sheets to integrate, for instance, if you have names and email ID in Sheet #1 and address and contact number corresponding to email ID in another i.e Sheet #2? VLookup can help you with this:

• =VLOOKUP(lookup value, table array, column number, [range lookup])
• Lookup Value: The value of the email ID corresponding to which you want to insert the address and contact number.
• Select the table array from the sheet #2.
• Now select the value of the column number from the selected array, for instance, if the first field to be inserted is the address, which will be in the second or third column of the sheet #2. Insert 2 or 3 in the formula.
• The range lookup is just to ensure that one pulls only exact value match as a result.

7. Pivot Tables

Pivot tables are very helpful if you want to summarize a heavy and long data. For instance, from the 1000 set supply data, you want to figure out the number of the consignment that came from Delhi, you can use Pivot tables.

To create the Pivot Table, go to Data and select Pivot Table. Excel will automatically populate your Pivot Table, but you can always change around the order of the data. Then, you have four options to choose from.

• Report Filter: This allows you to only look at certain rows in your dataset. For example, if you want to create a filter for hosiery consignment, you can select the data set with the same.
• Column Labels: These could be your headers in the dataset.
• Row Labels: These could be your rows in the dataset. Both Row and Column labels can contain data from your columns (e.g. Name of the consignment can be dragged to either the Row or Column label — it just depends on how you want to see the data.)
• Value: This section allows you to look at your data differently. Instead of just pulling in any numeric value, you can sum, count, average, max, min, count numbers, or do a few other manipulations with your data. In fact, by default, when you drag a field to Value, it always does a count.

8. Summarize data with CountIf and SumIf function

Another great tool is “CountIf” and “SumIf”.

• CountIf: What if you want to count the number of times, silk consignment is mentioned in the data set? You can use CountIf function to do the same

=COUNTIF (range, criteria)

Here the criteria will be “silk”

• SumIf: And if you want to sum of silk consignments, you can use this formula.

=SUMIF(range,criteria,sum range)

Here the criteria will be “silk” and sum range will the “quantity of the consignment”.

9. Combining cells using ‘&’

Databases tend to split out data to make it as exact as possible. For example, instead of having a data that shows a person’s full name, a database might have the data as a first name and then the last name in separate columns. In Excel, you can combine cells with different data into one cell by using the “&” sign in your function.

The formula with variables: =A2&” “&B2

10. Using shortcuts to quickly format values

For a number with two decimal points, use Ctrl + Shift + !. For dollar (converting data into Dollar currency) use Ctrl + Shift + $. For percentages it’s Ctrl + Shift + %. For converting data into time use Ctrl+Shift+:. There are many more such hacks, and you can find them easily on the internet.

KredX is an Invoice discounting marketplace helping Businesses address cash flow issues, to learn more visit www.kredx.com