Excel is the best spreadsheet app. It has thousands of formulas that can be used in various places, making tasks more manageable. Although we usually use formulas to solve the needs, some functions, formulas, tricks, shortcuts and hacks in Excel remain unknown.
Excel offers many lesser known formulas that work wonders. Here we will explore some of these Excel formulas, Excel tricks, and Excel shortcuts that should make you a pro in Excel.
The five craziest Excel formulas in Excel
1. Proper formula in Excel
Data cleansing is the first step before working with any data. These cleaning tasks often require the proper use of uppercase and lowercase letters. The proper function in Excel is the solution to the problem where it is not necessary to go to every cell to correct capital letters. All you need is to create a separate column, enter the function, = PROPERTY (cell_reference_to_be_corrected) that’s it. That is all you do. You have solved the problem and also saved time.
2. Roman formula in Excel
Do you rarely 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 is fun, it can get tiring and confusing once you hit 100. So here is the solution to converting numbers to 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 no longer need conversion charts.
3. Concatenate formula in Excel
Concatenate is the simplest and most widely used function in Excel. It is used to combine the cell values of two different cells into one cell. It is different from what is known as cell fusion. Suppose you have two cells A1 that have text: “Blessed” and cell A2 that has text: “You!” to combine it in cell A3, the function will be, = CONCATENATE (A1, ”“ A2) and the output will be Blessed!
Four. Randbetween formula in Excel
The Randbetween function in Excel generates random numbers between two values, an upper bound and a lower bound. This is a valuable feature for people who play with numbers and try Excel. Suppose you want to generate random numbers between 100 and 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 choice function in Excel helps you choose between the created options and return the required option. Suppose you have three options: 100, 200, and 300. To choose 200 from these, the function will be = CHOOSE (2,100,200,300). You can also replace 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 in Excel
Excel is a complete package of functions and tricks, making it the most widely used spreadsheet in the world. However, although some Excel functions are used more frequently, some are used less frequently. Here are five Excel tricks you can use to work like a pro.
Find the total number of working days between two dates, including holidays.
For an employee, work becomes more fun when he knows that on this or that date he could rest, since it is a holiday! Thus, many employees and even children who go to school are interested in calculating the total number of working days, excluding holidays. For this, we have a simple function in Excel, that is, NETWORKDAYS. The syntax is = Networkdays (start_date, end_date,[holidays]), and by mentioning holidays and start / end dates, you can find out how many days you need to work longer.
Entry restriction with data validation function
To have specific data with predefined restrictions, we can use data validation. For example, let’s say you are collecting data for a survey report and you want respondents to enter the age between 18 and 40, and anyone who enters the age below or above the set bar should be rejected. Then all you need to do is go to Data> Data Validation> Settings and choose the criteria based on your requirements. It can even display a warning message, such as, Please enter your age if you are in the 18 to 40 age gap.
Transpose data from a row to a column
We often have a certain dataset whose visibility becomes a problem due to more rows or other factors. For such data, you can use Excel’s transpose function, which converts rows to columns and columns to rows so that the data is better displayed. All you have to do is select the data> CTRL + C> Home> Paste> Transpose. You will now notice that all of your data, in rows, has been automatically moved to columns.
Input values starting with 000
Try typing a number in Excel that starts with 0 or 00, say 00234. Excel will automatically remove the zeros and put only the number as the result, that is, 234. We often require such zeros at the beginning of a number when setting codes or ids unique. You can fix a quote [‘] in front of the numbers so that 00 appears.
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 you selected. For example, suppose you selected three rows before inserting. This will add up to three new rows. And so on.
The five shortcuts to make it faster in Excel
Have you ever thought about how your colleague does the same task in 5 minutes, which takes more than an hour?
The answer is to become an expert. Most companies are looking to hire Excel experts to do their jobs efficiently. It could also become a source of income for you.
If you are still stuck, working with a mouse to perform even the simplest function like drag and drop, then my friend, trust me, you are in big trouble. To which the solution is here.
Everything and anything in Excel can be achieved using shortcuts and formulas in Excel. Like cut, copy, paste, there are many other shortcuts that can be done in Excel to increase efficiency and reduce the time to complete a task.
Five shortcuts that will reduce your work time are:
How to move between multiple sheets in the same workbook
Often when working in Excel, we have large data sets that are disorganized or sometimes organized scattered across multiple sheets that must be used together to arrive at a valuable fact. In such a scenario, we need to go back and forth several sheets, all at the same time. While some use the mouse to go to each sheet, some prefer to use the keyboard shortcut to navigate between multiple sheets.
Shortcut to move between sheets: Ctrl + Page up / page down arrows.
Shortcut to scroll through sheets on Mac: Command + Page Up / Page Down Arrows
And by using the simple control and the page up / back keys, you can save time going to the mouse, clicking, dragging, clicking, and repeating the same action.
How to display active cell in worksheet
While working with huge data sets can sometimes be confusing, we work in one cell, we move to another cell, and then we get lost: where was it? What cell was he working in?
Do you need to memorize the cell reference before scrolling 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 on) if you have scrolled so far. This one is for you,
Shortcut to go to active cell: Ctrl + Backspace
Shortcut to go to active cell on Mac: Command + Backspace
Pressing Ctrl and the backspace keys at the same time will quickly take you to the active cell you were working on.
How to display the “Go To” dialog box
We often give names to sheets, tables, ranges, and charts in Excel. What is the use of such names or named ranges?
You can go to a table, sheet, or named range address using the Go To window. The access window also has a special option in the left corner. the special tab allows you to find possibly blank cells, have formulas, differences, etc. The special tab is where the true power resides.
Go to window shortcut: Ctrl + G
Go to window shortcut on 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 on Mac, follow the shortcut: ^ + 9
Press Ctrl + 0 to hide columns
On Mac, hide columns with shortcut: ^ + 0
Hidden rows and columns are not printed.
How to start a new line in the same cell
Entering data in a single cell but different lines is mandatory for people who have a lot of information to be placed under a cell.
Shortcut for line break within 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 going back once you build the momentum. You will experience a particular speed in the way you carry out tasks. The pace of performing different activities in Excel will be drastically reduced.
Try using the five handy shortcuts in Excel to pick up your pace. Otherwise you can always get online help from Excel (excelhelpdot org).
Image Credit: Pixabay; Pexels; Thanks!