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 keysShortcut Excel KeysAn Excel shortcut is a technique of performing a manual task in a quicker way.. 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.
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 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. 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 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..
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 the Helper column.
Now open the sort option by pressing ALT + D + S.
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.
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 very quickly by using a recent tool called “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.” 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 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. will expose you to some of the cool tricks.
- We have various other cool techniques as well.
- Smarter ways of using excel depends 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 –