Excel Hacks and Tricks

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

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor 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.

#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 keysShortcut Excel KeysAn Excel shortcut is a technique of performing a manual task in a quicker way.read more. 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 excelFill Handle In ExcelThe fill handle in Excel allows you to avoid copying and pasting each value into cells and instead use patterns to fill out the information. This tiny cross is a versatile tool in the Excel suite that can be used for data entry, data transformation, and many other applications.read more 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 boxName BoxIn Excel, the name box is located on the left side of the window and is used to give a name to a table or a cell. The name is usually the row character followed by the column number, such as cell A1.read more.

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 ToolQuick Analysis ToolExcel’s quick analysis tools allow the user to quickly analyze any data without having to resort to the traditional way of inserting charts and tables. There are two ways to access: 1) Select the cell range data that contains values, and a yellow box with quick analysis tools displays on the bottom right-hand side 2) Press CTRL + Q.read more” 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.

Things to Remember

  • Regular use of excelUse Of ExcelIn today’s corporate working and data management process, Microsoft Excel is a powerful tool." Every employee is required to have this expertise. The primary uses of Excel are as follows: Data Analysis and Interpretation, Data Organizing and Restructuring, Data Filtering, Goal Seek Analysis, Interactive Charts and Graphs.
  • read more 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.

This article has been a guide to Excel Hacks. Here, we discuss the top 6 most powerful hacks in Excel, including sum numbers, creating a replica of sheets, inserting alternative blank rows, etc. You may learn more about Excel from the following articles: –

  • Excel RepairFormat Cell in ExcelCreate a Venn Diagram in ExcelShade Alternate Rows in Excel