Updated August 22, 2023
Excel Hacks (Table of Contents)
Introduction to Excel Hacks
Excel Hacks is a procedure or shortcut step that significantly increases your productivity & saves time on your day-to-day task. Microsoft Excel is the most commonly used tool to perform the task; excel hacks help out work faster 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.
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 helps 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 its resizing is important; here, you have to select the multiple columns, then you need to hover your cursor mouse towards the 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
In some scenarios, we need to enter today’s date & time; instead of looking into the calendar or time at the bottom, we can use the below-mentioned shortcut key.
For date: CTRL + ; or For time: SHIFT + CTRL + ;
Insert a Comment: Short cut key will be SHIFT + F2 (Note: Before performing this, double click on cell reference for edit mode).
4. Addition of a New Line in a Cell
Suppose you are entering address details, and you need to add multiple lines of 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, right-click & select the Insert option. So automatically, the rows or columns get inserted. (Note: for example, if you want to insert ten columns or rows, you need to select the same number of rows or columns and then right-click & select the 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 faster, you need to select the data range.
After pressing the “ALT key and Enter”, it will give the range’s total value.
7 Addition of Cell Border
Here we want to add a cell border across the data range.
After pressing on “Ctrl + Shift + &”, cell borders are automatically added.
8. Number Formatting
You can select a date range if you want a dollar sign displayed before each number.
After clicking “Ctrl + Shift + $”, 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 sheet 1 of a workbook, so you need to re-create it from scratch for the next quarter’s sales data, which is very difficult and time-consuming; you can use the “Move or Copy” option under the 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 the 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 shortcut key ALT + W + F + R so that the first row gets frozen if I want to remove the freeze pane from the 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 shortcut 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 the 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 the count formula, i.e., the 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 of 2.
3. To know the Formula Text or which formula you have used
If you know which formula is present in a cell, you can check it out with the help of the =FORMULATEXT(cell reference) formula. Here I have used cell A8 (as a cell reference).
After using the formula, the output is shown below.
4. To Pullout Only Decimal Values with the help of MOD & INT Function
I have a cell value of 98.23 in cell “A2” I want only the decimal portion in 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 formulas, you can quickly perform various required calculations between numbers, cells, entire rows, and columns or ranges of cells.
Recommended Articles
This is a guide to Excel Hacks. Here we discuss How to Use Excel Hacks with Excel shortcut keys, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
