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.
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 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.
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.
Step 2: Click on “Special” or press the shortcut key ALT + S.
To see the “Go to Special” window.
Step 3: In the Special window choose the option “Blanks”. To select the “Blanks” shortcut key is “K”.
Step 4: Click on Ok, it has selected all the blank in the selected range of cells.
As we can see in the above image all the blanks cells are selected.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
Step 5: Now all we need to do is 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 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.
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.
#4 – Insert Serial Numbers Easily
Serial number insertion is not the 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.
Now in the below cell put the formula =A1 + 1.
Now copy the cell A2 and enter the required cell in the name box.
Now hit enter to go the entered cell.
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.
Now simply type the shortcut key of fill down Ctrl + D to insert serial numbers until the A1000 cell.
#5 – Insert Blank Alternative Rows
Assume you have data like the below.
Now you want to insert blank rows after every row like the below.
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.
Now just copy the serial number from 1 to 6 and paste below the last cell.
Now select the data including Helper column.
Now open sort option by pressing ALT + D + S.
In the sort window make sure “My Data has headers” checkbox is ticked. And select “Helper” column from sort by option and under “Order” choose “Smallest to Largest” then, click ok.
See the magic Alternate rows are inserted.
Here we have, what 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.
Click on this option to see various quick analysis tools.
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
- Regular use of excel will expose you to some of the cool tricks.
- We have various other cool techniques as well.
- Smarter ways of using excel depend on your hacks you know in tricky situations.
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 –