Excel is the best spreadsheet application. It has thousands of formulas that can be used at multiple places making the tasks more manageable. Although we usually use formulas to solve the necessities, some functions, formulas, tricks, shortcuts, and hacks in Excel remain unknown.
Excel offers many formulas that are less known but do wonders. Here we will explore some of these formulas in excel, excel tricks, and excel shortcuts that should make you a pro at Excel.
The Five Craziest Excel Formulas in Excel
1. Proper formula in Excel
Data cleaning is the first step before working on any data. Such cleaning tasks often require the appropriate usage of capital and small letters. Proper function in Excel is the solution to the problem where you need not go to each cell to fix the capital letters. All you need is to create a separate column, enter the function, =PROPER(cell_reference_to_be_corrected) that’s it. That’s all you do. You have solved the problem and saved time as well.
2. Roman formula in Excel
Rarely do you come across situations where you need to use roman numerals? In such situations, all you need is the ROMAN formula in Excel. While using roman sounds fun, it can be tiring & confusing once you reach 100. So here is the solution to converting numbers into roman numerals. All you need is to create a separate column, enter the function, =ROMAN(cell_reference_to_be_corrected) that’s it! Now you don’t require conversion charts anymore.
3. Concatenate formula in Excel
Concatenate is the most straightforward and most used function in Excel. It is used to combine the cell values of two different cells in one cell. It is different from what is known as cell merge. Let us suppose you have two cells A1 that has text- “Bless” and cell A2 that has the text- “You!” to combine it in cell A3, the function will be, =CONCATENATE(A1,” “A2) and the output will be Bless You!
4. Randbetween formula in Excel
The Randbetween function in Excel generates random numbers between two values, one upper limit and another lower limit. This is a valuable function for people playing around with numbers and trying their hands on Excel. Let us suppose you want to generate random numbers between 100 to 1000, the process will be, =RANDBETWEEN(100,1000), and the output will be any random number between 100 and 1000.
5. Choose formula in Excel
The choose function in Excel helps you pick among the options created and return the required option. Say you have three options- 100, 200, and 300. To pick 200 among these the function will be, =CHOOSE(2,100,200,300). You can also replace the options with cell references. If you want to choose the third option, the function will be, =CHOOSE(3,100,200,300). The chosen formula is often used for scenario analysis by financial modelers.
The Five Tricks to Help You Excel at Excel
Excel is a complete package of functions and tricks, making it the most used spreadsheet globally. However, while some functions of Excel are most widely used, some are used less frequently. Here are five Excel tricks that you can use to work like a pro.
Find total working days between two dates, including holidays.
For an employee, work becomes more fun when they know that on so and so dates he could rest as it is a holiday! Thus, many employees and even children going to school are interested in calculating the total number of working days, excluding the holidays. For this, we have a simple function in Excel, i.e., NETWORKDAYS. The syntax is =Networkdays(start_date,end_date,[holidays]), and by mentioning the holidays and the start/end dates, you can find out how many days you need to work more.
Restricting input with data validation function
To have specific data with predefined restrictions, we can use data validation. For example, let us suppose you are collecting data for a survey report, and you want the respondents to type in the age between 18 to 40 years, and anyone entering the age below or above the set bar needs to be rejected. Then, all you need to do is, go to Data > Data validation> Settings and choose the criteria per your requirement. You can even prompt a message as a warning, like, Kindly enter your age if you are in the age gap of 18 to 40 yrs.
Transpose Data from a Row to a Column
We often have a certain set of data whose visibility becomes an issue due to more rows or other factors. For such data, you can use the transpose function of Excel, which converts the rows to columns and columns to rows making the data display better. All you gotta do is, select the data > CTRL+C> Home > Paste > Transpose. Now you will observe that all your data, in rows, have been shifted automatically into columns.
Input values starting with 000
Try typing in a number in Excel starting with 0 or 00, say, 00234. Excel will automatically remove the zeros and put just the number as the output, i.e., 234. We often require such zeroes at the beginning of a number while setting codes or unique IDs. You can fix a quote mark [‘] in front of the numbers to make the 00 appear.
Add more than 1 row or column.
To add more than one column, what you need to do is, select the number of rows you want to add, right-click, and insert. This will automatically add the number of rows that you selected. For example, say you selected three rows before inserting. This will add up to three new rows. And so on.
The Five Shortcuts to Make You Faster in Excel
Have you ever thought about how your colleague does the same task in 5 minutes, which takes you more than an hour?
The answer lies in becoming an expert. Most businesses look for hiring Excel experts to get their work done efficiently. It could also become an income source for you.
If you are still stuck, working with a mouse for performing even the most straightforward function like drag & drop, then my friend, trust me, you are in Big Trouble. To which the solution lies here.
Everything and anything in Excel can be accomplished using shortcuts and formulas in excel. Like a cut, copy, paste, there are many other shortcuts that can be performed in Excel to increase efficiency and reduce the time of achieving a task.
Five shortcuts that will reduce your working time are:
How to scroll between multiple sheets in the same workbook
Often working on Excel, we have substantial unorganized or sometimes organized data sets scattered over multiple sheets that need to be used together to come to a valuable fact. In such a scenario, we need to go back and forth multiple sheets, all at once. While some use the mouse to go to each sheet, some prefer using the keyboard shortcut to scroll among various sheets.
Shortcut to scroll among sheets: Ctrl + Page up/ Page down arrows.
Shortcut to scroll among sheets in Mac: Command + Page up/ Page down arrows
And using the simple control and page up/down keys, you can save the time to go to the mouse click-drag-click and repeat the same action.
How to show the active cell on the worksheet
While working on huge data sets can sometimes be confusing, we work in 1 cell, scroll to another cell, and then get lost: where was I? Which cell was I working on?
Do you need to memorize the cell reference before scrolling down to cell A5678 (column A, row 5678)? Certainly not. All you need to do is use the shortcut key to navigate back to the active cell (cell you were working upon) if you have scrolled so far. This one’s for you,
Shortcut to go to Active cell: Ctrl + Backspace
Shortcut to go to Active cell in Mac: Command + Backspace
Pressing Ctrl and the backspace keys together will quickly navigate you to the active cell you were working on.
How to display the “Go-To” Dialogue box
We often provide names to sheets, tables, ranges, and charts in Excel. What is the usage of such names or named ranges?
You can go to a named range table, sheet, or address using the Go To window. The go-to window also has a special option on the left corner. The special tab allows you to find possibly blank cells, have formulas, differences, etc. The special tab is where the real power lies.
Shortcut to Go To window: Ctrl + G
Shortcut to Go To window in Mac: Command + G
How to hide columns or rows
Sometimes specific rows in Excel contain sensitive information that needs to be hidden. In such a scenario, we can hide rows or columns.
Shortcut to hide rows: Ctrl + 9
To hide rows in Mac, follow the shortcut: ^ + 9
Press Ctrl + 0 to hide columns
In Mac, hide columns with shortcut: ^ + 0
The hidden rows and columns do not print.
How to start a new line in the same cell
Entering data in a single cell but different lines is mandatory for the people who have a lot of information to be put under one cell.
Shortcut for line break inside a cell: Alt + Enter
Knowing this shortcut is a must for you if you want to write large paragraphs in a cell.
Initially, using the shortcuts could be challenging, but there is no looking back once you develop the momentum. You will experience a particular swiftness in the way you accomplish tasks. The pace of performing different activities in Excel will reduce drastically.
Try using the five handy shortcuts in Excel to fasten your pace. Or else, you can always get Excel online help (excelhelpdot org).
Image Credit: Pixabay; Pexels; Thank you!
