Excel formulas, functions, and advanced tricks in excel are essential for supply chain professionals. However, if you are not familiar with the useful excel functions and constantly ask fellow supply chain colleagues to help you with simple calculations and formulas, you need some help. Use these excel experts’ tips and tricks to get more out of your Excel skills.
Best 12 Basic Excel Tips and Tricks
An artistic mind, the ability to brainstorm and invent, and solid technical skills are significant advantages for people in the technical and management sectors. Unfortunately, the environment also requires adhering to standard operating procedures often defined in spreadsheets. But if you use Excel well, it can be a game-changer. To help supply chain professionals get better at what they do, Seekho has compiled MS excel tricks that every supply chain technical professional should know.
1. Forecast creation
You can often predict future trends and sales by looking at past trends and deals. The FORECAST function in Excel can help you do this. It would help if you used the FORECAST function in Excel when you have existing values, like past trends or sales, and want to predict what might happen in the future for the same.
2. Make use of pivot table
A shortcut can help you categorise data in your Excel spreadsheet. It’s called a “Pivot Table,” making it easy to look at more extensive data sets and identify trends. For creating a Pivot Table, one needs to start by selecting their data and putting it into a table on the spreadsheet. Then, you choose the type of Pivot Table you want to create from a list of options. You can also begin with a blank spreadsheet and use the Pivot Table command to arrange the required data on your own.
3. Make use of VLOOKUP
The VLOOKUP function in Excel searches for a value in a table and then displays the corresponding value. This function saves you time by helping you find a specific value in an extensive database with less chance of error. To use this function, add a column to your spreadsheet on the required place you want the search values to be displayed. Click on the first blank cell in the column and select Insert>Function. After this, input VLOOKUP and then press enter key. A dialogue box will appear, allowing you to enter four values that you can look up.
4. Condition formatting
To see trends in your data, you can use conditional formatting. Select your data, then click on the Home tab> Format>Conditional Formatting>Manage Rules. In this dialogue box, you can choose formatting rules based on the condition you want to apply.
5. Concatenate text
This function joins two text strings and saves time. If you want to merge two columns you’ve already created, use the CONCATENATE function instead of retyping the information to merge the text in these columns. Start by creating a new column where you want to place the combined data. The command is =CONCATENATE (B2, C2).
If you have rows of data in your worksheet, you can turn them into columns and vice versa. To do this with the Transpose function, select the column or row you want to transpose, copy it, select the cells where you want the first row of data to start, right-click on one of those cells and then choose Paste Special. You will see a pop up where you can select Transpose; select this option and click OK.
7. IF function
The Excel “If” function makes it easy to see if any data meets certain conditions. This trick explains how the “If” statement works and how you can use it in your spreadsheets. Using the “If” function, you can quickly see if any data meets certain conditions with two possible results: true or false. But there’s a better way! The “Nested IF” statement replaces the value ”_if_false” argument with another “IF” statement, simplifying your data even more!
The dollar sign ($) helps ensure that formula in one cell will retain its values when copied to other cells. Whenever we copy a formula with a relative reference, it automatically adjusts the values based on where it has been moved. But sometimes, we want to keep the values in a formula unaltered, and in those cases, we can use absolute formulas. When we modify a relative formula into a complete formula, we include the dollar signs before the row and column values.
9. Charts and Graphs
Using charts and graphs is an essential part of the corporate sector. Excel’s charting capabilities allow you to visualise data clearly, and concisely. But for some professionals, creating charts can be tricky. They may find it hard to get their head around how to use Excel’s charting tools, or they might struggle with the program itself. So, it’s better to know beforehand.
The COUNT function is a useful function to have when you’re working with numbers in your worksheets. It sums the number of cells in the particular data set containing numbers only – numbers and decimals, but not text.
11. Table formatting
If you’ve ever used Microsoft Excel, you know that formatting your data can be a challenge. But with table formatting, you can create an interactive database that makes it easier to make decisions based on the data. To use this feature, click on a cell on the dataset, and go to Home>Styles>Format as Table. Then you can make more informed decisions about your data.
12. Advanced filters
Using advanced filters, you will discover one of Excel’s best features. Advanced filters allow you to search for data using formulas and text filters. They’re much more accessible to master than regular expressions, too. Advanced filters are perfect for exploring large datasets.
So, in this article, we have discussed a few advanced excel tricks that can help you increase your productivity while using Excel. It doesn’t make sense just to learn the basic stuff and leave. Be professional and ready for challenges by practising these excellent tricks and commands before starting your professional journey.