Excel Hacks

Advanced Excel users work efficiently and increase productivity because of their extensive knowledge of spreadsheets. One of the important weapons in their arsenal is "Hacks," which they regularly use to solve tricky situations.

This article will show some of the powerful hacks that you can use at your workplace regularly in Excel.

Below are some hacks and tricks we can use in Excel.

#1 - Use Shortcut Keys to Work Efficiently

If we want to advance to the next level, we need to start using and get familiar with shortcut excel keys. Please refer to our previous articles on Excel shortcut keys.

Excel Shortcuts Part 1

Excel Shortcuts Part 2

#2 - Quickly Sum Numbers

Assume on a beautiful Friday evening, you are almost ready to log off from work to go for a trip, and suddenly your boss sends a file that looks like the one below.

He wants you to total for each year!!! (For example purpose we have taken a small sample size)

You are stunned the moment you see this, so it takes a minimum of 15 minutes to add everything. So how do you do it in less than 1 minute?

Here you go!

Step 1: To open the "Go-To" dialog box, we must select the entire data and press the F5 key to open the "Go-To" dialog box.

Step 2: Now, we must click on "Special" or press the shortcut key ALT + S.

To see the “Go to Special” window.

Step 3: In the "Go To Special" window, we must choose the option "Blanks." To select the "Blanks," shortcut key is "K."

Step 4: Click on "OK." It has now selected all the blanks in the selected range of cells.

All the blank cells are selected, as we can see in the above image.

Step 5: Now, we need to press the Auto Sum shortcut key "ALT + =."

It has summed all the cells above them. How cool is this?

#3 - Create a Replica of the Sheet

Often, we require creating a replica of the worksheet that we are working on. So, we need to use this smart and cool technique in such a case.

Select the worksheet that we need to create a replica of.

Now hold the "control" key, use the mouse's left side, drag it to the right of the worksheet, and release it to create a sheet replica.

#4 - Insert Serial Numbers Easily

Serial number insertion is not a stranger thing to all the excel users. The usual technique we all have used is to enter the first three numbers and drag the fill handle in excel to the cell where we need to insert serial numbers.

But imagine the situation where you need to insert 1,000 serial numbers we cannot drag until we find the 1,000th cell.

So, here we have a cool technique to deal with it. First, we must enter number 1 in the first cell.

Now in the below cell, put the formula =A1 + 1.

Now copy cell A2 and enter the required cell in the name box.

Now, press the "Enter" key to go to the entered cell.

From this cell, press the shortcut key Shift + Ctrl + Up arrow key to the above-used cell to select the range of cells from A2 to A1000.

Now, we must type the shortcut key of fill down Ctrl + D to insert serial numbers until the A1000 cell.

#5 - Insert Blank Alternative Rows

Assume we have data like the below.

We want to insert blank rows after every row like the below.

It is small data, and we can do it manually. But when it comes to large data, it is not that easy. So, here we have a trick.

We must insert a new dummy column next to the data and insert serial numbers.

Now copy the serial number from 1 to 6 and paste it below the last cell.

Now select the data, including the "Helper" column.

We need to open the sort option by pressing ALT + D + S.

Ensure the "My Data has headers" checkbox is ticked in the sort window. And select the "Helper" column from the "Sort by" option. Then, under "Order," choose "Smallest to Largest," then click "OK."

See the magic. Alternate rows are inserted.

Here, we have what is supposed to be a tricky task turned into a smarter one.

Now get rid of the "Helper" column.

#6 - Quick Analysis Tool

We can analyze the data quickly using a recent “Quick Analysis Tool” from Excel 2013.

When we select the numerical data, we can see the small icon just at the end of the data.

Click on this option to see various quick analysis tools.

As we can see, we have a variety of options here. So please use all the things we need and save a tremendous amount of time at the workplace.

• Regular use of excel can expose us to some cool tricks.
• We have various other cool techniques.
• Smarter ways of using Excel depend on our hacks in tricky situations.