Excel Hacks

Excel Hacks and Tricks

All the advanced excel users work efficiently and increase their productivity because of their extensive knowledge of spreadsheets. One of the important weapons in their arsenal is “Hacks” they use regularly to solve tricky situations.

In this article, we will show some of the powerful hacks that you can use at your workplace regularly in excel.

Excel-Hacks

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Excel Hacks (wallstreetmojo.com)

Top 6 Hacks and Tricks Shortcuts in Excel

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

You can download this Excel Hacks Template here – Excel Hacks Template

#1 – Use Shortcut Keys to Work Efficiently

If you want to advance to the next level, you 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 the work to go for a trip, and suddenly your boss sent a file which looks like the below one.

Excel Hacks Example 1

He wants you to total for each year!!! (For example purpose I 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: Select the entire data and press the F5 key to open the “Go-To” dialogue box.

Excel Hacks Example 1-1

Step 2: Click on “Special” or press the shortcut key ALT + S.

Excel Hacks Example 1-2
Shortcut key for Special window

To see the “Go to Special” window.

Excel Hacks Example 1-3

Step 3: In the Special window, choose the option “Blanks.” To select the “Blanks” shortcut key is “K.”

Excel Hacks Example 1-4

Step 4: Click on Ok, it has selected all the blank in the selected range of cells.

Excel Hacks Example 1-5

As we can see in the above image, all the blanks cells are selected.

Step 5: Now, all we need to do is press the Auto Sum shortcut key “ALT + =.”

shortcut for Autosum
Excel Hacks Example 1-6

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

#3 – Create a Replica of the Sheet

Often times, we require to create a replica of the worksheet that we are working on. In such a case, we just need to use this smart and cool technique.

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

Excel Hacks Example 3

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

Excel Hacks Example 3-1

#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 till the cell where we need to insert serial numbers.

But imagine the situation where you need to insert 1000 serial numbers we cannot dragging until we find the 1000th cell.

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

Excel Hacks Example 4

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

Excel Hacks Example 4-1

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

Excel Hacks Example 4-2

Now hit enter to go the entered cell.

Excel Hacks Example 4-3

From this cell, press the shortcut key Shift + Ctrl + Up Arrow key to go the above last used cell, so it will select the range of cells from A2 to A1000.

Excel Hacks Example 4-4

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

Excel Hacks Example 4-5

#5 – Insert Blank Alternative Rows

Assume you have data like the below.

Excel Hacks Example 5

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

Excel Hacks Example 5-9

This is a small data, and you can do it manually, but when it comes to large data, it isn’t that easy. So here we have a trick.

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

Excel Hacks Example 5-1

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

Excel Hacks Example 5-2

Now select the data, including the Helper column.

Tricks Example 5-3

Now open the sort option by pressing ALT + D + S.

Tricks Example 5-4

In the sort window, make sure the “My Data has headers” checkbox is ticked. And select the “Helper” column from sort by option and under “Order” choose “Smallest to Largest,” then click ok.

tricks Example 5-5

See the magic Alternate rows are inserted.

tricks Example 5-6

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 very quickly by using a recent tool called “Quick Analysis Tool” from Excel 2013 version onwards.

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

tricks Example 5-7

Click on this option to see various quick analysis tools.

tricks Example 5-8

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

Things to Remember

This has been a guide to Excel Hacks. Here we discuss the top 6 most powerful hacks in excel, which include sum numbers, create a replica of sheets, insert alternative blank rows, etc. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>