Latest Tips and Tricks, shortcuts, formulas, or hacks in excel
Excel is the best spreadsheet application. It has thousands of formulas that can be used for the multiple places making the tasks more manageable. Although we usually use formulas to solve the necessities, many functions, shortcuts, formulas, tricks or hacks in Excel remain unknown.
Excel offers various 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 Four 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 for the appropriate usage of biggest and small letters. Proper function in Excel is the solution to the problem where you need not go to each cell to fix the letters. All you need is to create the separate column, enter the function, =PROPER (cell_reference_to_be_corrected) that’s it. That is all you do. You have solved the problem and saved time as well.
2. Concatenate formula in Excel
Concatenate is the most straightforward and more useful function in Excel. It’s used to combine the cell values of two different cells in one cell. It’s 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.
3. Roman formula in Excel
Rarely do you come across situations where you need to use roman numerals? That situation, all you need is the ROMAN formula in Excel. By using roman sounds fun, it can be tiring and confusing once you reach 100. So here is the solution to converting numbers into roman numerals. To create the separate column, enter the function, =ROMAN (cell_reference_to_be_corrected). Now you do not require conversion charts anymore.
4. Choose formula in Excel
The select function in Excel helps you pick among the options created and return the required option. Say you have three options- 100, 200, or 300. If, select 200 among these the function will be, =CHOOSE(2,100,200 or 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.
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.
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.
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.
Some Shortcuts to Make Faster in Excel
Anything and Everything in Excel can be accomplished by using shortcuts and formulas in excel. Such as a cut, paste, copy, there are some other shortcuts that can be performed in Excel to increase efficiency and reduce the time of achieving a task.
Some shortcuts that will reduce working time such as:
How to show the active cell on the worksheet
While working for the 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?
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,
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 or charts in Excel. What is the usage of such names or named ranges?
You can go to the named range sheet, table and address by using the Go To window. The go-to window also has the special option on the left corner. The special tab allows you to find possibly blank cells, have formulas, differences, and more. The special tab is where the real power lies.
How to start a new line in the same cell
Entering data in the single cell but different lines is mandatory for the people who have a lot of information to be put under one cell. Knowing this shortcut is a must for you if you want to write large paragraphs into the cell.
How to hide columns or rows
Every time specific rows in Excel contain sensitive information that needs to be hidden. In such case, we can hide rows and columns.
How to scroll between multiple sheets in the same workbook
For working on Excel, we have substantial unorganized and sometimes organized data sets scattered over multiple sheets that need to be used together to come to a valuable fact. In such cases, we need to go back and forth multiple sheets, all at once. Some use the mouse to go to each sheet; some prefer using the keyboard shortcut to scroll among many sheets.
By using the simple control and page up or down keys, you can save the time to go to the mouse click-drag-click and repeat the same action.
Lorem ipsum viverra feugiat. Pellen tesque libero ut justo, ultrices in ligula. Semper at. Lorem ipsum dolor sit amet elit. Non quae, fugiat nihil ad. Lorem ipsum dolor sit amet. Lorem ipsum init dolor sit, amet elit. Dolor ipsum non velit, culpa! elit ut et.
Lorem ipsum dolor sit amet elit. Velit beatae rem ullam dolore nisi esse quasi, sit amet. Lorem ipsum dolor sit amet elit.
