Excel Hacks (Table of Contents)
Introduction to Excel Hacks
Microsoft Excel is a most commonly used tool to perform the task, excel hacks help out to work at a faster pace for data entry, formatting, sorting, deduplication, manipulation, organizing & consolidation of data & data analysis. Being familiarized with Excel is almost many company requirements in their project & business work, most of the people dedicate a significant amount of time to learn Excel. There are multiple hidden complex features and functions in excel which we have to decode to get a better result and perform a day to day task at a faster pace.
Definition of Excel Hacks
Excel Hacks is a procedure or shortcut steps that significantly increase your productivity & saves your time on your day-to-day task.
How to Use Excel Hacks?
Here we discuss the list of most of Microsoft Excel’s significant shortcut buttons and main combinations with examples.
Example #1 – Use of Important Keyboard Shortcuts
1. Selection of Data
Selection of all the data with a single click or with the help of CTRL + A, it helps out to select a whole data range in a worksheet.
2. Column Width Size Adjustment
When you are working on a huge data set, this column width adjustment and it’s resizing is important, here you have to select the multiple columns then you need to hover your cursor of mouse towards right side of the column where you want to resize it and double click on it to get the desired result or you can use shortcut keys i.e. ALT + HOH (Column Height) & ALT + HOW (Column Width)
3. Insertion of Date or Time or Comments in Excel
4.9 (2,423 ratings)
View Course
In some scenario’s we need to enter today’s date & time, for that instead of looking into the calendar or time at the bottom, we can use a below-mentioned shortcut key.
For date: CTRL + ; or For time: SHIFT + CTRL + ;
Insert a Comment: Short cut key will be SHIFT + F2 (Note: Prior to performing this, double click on cell reference for edit mode).
4. Addition of New Line in a Cell
Suppose, you are entering address details, you need to add multiple lines data in the same cell, for that you need to enter short cut key “Alt + Enter”, it will move the cursor down so that you can enter or type the text in the next line.
5. Addition or insertion of multiple columns or rows
In some cases, you want to insert additional rows or columns, to add extra datasets, for this you need to select or highlight the columns or rows, do a right-click & select Insert option. so automatically the rows or columns get inserted. (Note: for example, if you want to insert ten number of columns or rows, you need to select the same number of rows or column and then do a right-click & select Insert option, so that ten rows or columns get inserted.
6. Autosum the Selected Data Range
Suppose, I have a numeric data range, so I want to do a sum up for that data range, to perform this task at a faster pace, you need to select the data range.
After pressing on “ALT key and Enter” it will give the Total value of that range.
7 Addition of Cell Border
Here we want to add cell border across the data range.
After pressing on “Ctrl + Shift + &” so that automatically cell borders get added.
8. Number Formatting
In case, you want a dollar sign to be displayed in front of each number, then you can select a data range.
After click on “Ctrl + Shift + $”, so that the entire numeric value data range will be displayed with the dollar sign.
9. Make a Copy of your Worksheet in the Same Workbook
Suppose, you have a quarterly report in sheet1 of a workbook, so for the next quarter sales data you need to re-create it from scratch, which is very difficult and time-consuming, for this you can use “Move or Copy” option under worksheet.
To perform this, right-click on the tab of your worksheet at the bottom and select “Move or Copy” a popup appears, where it asks, where you’d like your sheet to be moved, in that select move to end and click on the tick box, or create a copy and click on ok, so that the same sheet with sales data will be created at the last worksheet in a workbook Simultaneously, you can create this worksheet in a new workbook as well, with an option under a workbook dropdown.
10. Freeze Top Row
In the worksheet, I want to freeze the top or first row, for that I need to click on cell B2 and enter a short cut key ALT + W + F + R so that the first-row gets to freeze, if I want to remove freeze pane from top row, I need to enter a short cut key ALT + W + F + F.
11. Addition of Filter for a Data Range
Suppose, you want to filter data in a worksheet.
After using a short cut key “Ctrl + Shift + L”, you can observe a clickable dropdown menu on each cell in the first row.
Example #2 – Usage of Important Formulas
1. To Count Blank Cells in the Data Range with the help of COUNTBLANK Formula
Here in the below-mentioned example, I want to count a blank cell in the data range (A2 to A7).
After using the COUNTBLANK formula i.e. =COUNTBLANK(A2:A7).
2. To Count Only Text Value in the Data Range with the help of COUNTA Formula
In the below-mentioned example, I want to count only a cell containing text values in the data range (A12 To A17), I can use the combination of count formula i.e. COUNTA formula will count everything including text & numeric value.
After using the COUNT formula it will count only the numbers i.e. = COUNTA (A2:A7)-COUNT (A2:A7), the difference between these two will result in an output value 2.
3. To know the Formula Text or which formula you have used
In some case, if you to know which formula is present in a cell, then you can check it out with the help of =FORMULATEXT(cell reference) formula. Here I have used cell A8 (as cell reference).
After using the formula output is shown below.
4. To Pullout Only Decimal Values with the help of MOD & INT Function
I have a cell value 98.23 in the cell “A2”, I want only decimal portion in the cell “B2”, to get this I can use the formula MOD & INT function.
After using the formula the output is shown below.
Things to Remember
With the help of the symbols or arithmetic operators explained in the above examples in user or predefined excel formula, you can quickly perform various required calculations between numbers, cells, entire rows, and columns or range of cells.
Recommended Articles
This is a guide to Excel Hacks. Here we discuss How to Use Excel Hacks with excel shortcut keys along with practical examples and downloadable excel template. You can also go through our other suggested articles –
