Knowing useful Excel Tips & Tricks are important for supply chain professionals at all levels. I have come across some candidate who has a fantastic CV, but when asked to use simple Excel formulas like vlookup or pivot table, they struggle. I remember once candidate left halfway through the as he struggles to apply the Excel formulas required to get the desired outcome! So in nutshell,
Excel skills are MUST for Logistics and Supply Chain Professionals
The Supply Chain Management environment requires plenty of artistic vision, brainstorming, and inventiveness along with usual technical competencies. However, if you are a professional in supply chain management, then you should know that Excel Tips & Tricks comes handy during everyday operation of any Supply Chain Management and Logistics activities. The use of Excel Tips & Tricks forms a major part of this daily standard work. And when you master it you can create awesome supply chain models, I have done for Inventory Optimisation Model which helps users greatly to calculate Made-to-Stock (MTS)/ Made-to-Order (MTO) and Safety Stock.
A good portion of your job as Supply Chain Professional goes into data gathering, analysis and creating meaning inferences for the business they work for. All these activities imply spending a considerable period of useful time on the Excel spreadsheet environment. For this reason, we have compiled 24 excellent Excel Tips & Tricks every Supply Chain Management Professional should know, learn and hopefully master.
24 Recommended Excel Tips & Tricks to Master:
1) Create Forecast in Excel
Lot of people ask me how we can do forecasting in Excel. The good news is there is a special function for that! The FORECAST function in Excel returns a prediction of an impending value grounded on existing values inputted. The FORECAST function is designed to provide assistance in making future predictions like future consumer trends, inventory necessities, and sales. When you use the FORECAST function in Excel, a new worksheet is created that comprises of tables of both past and predicted values and a chart that displays this data. You can learn more about creating Forecast here.
2) Pivot Tables
The Pivot Table Excel Tricks, permits for tabular data categorization. The Pivot Table makes it easy to assess bigger data sets in pinpointing evocative movements. To create a Pivot Table, you start by picking your data, put it on a table, and then pick a Pivot Table. You can also start up a fresh spreadsheet and use Pivot Table command to arrange the needed data as well.
3) VLOOKUP Function
The VLOOKUP function permits for searching of the table for a certain value and then output its related value. This Excel Tips & Tricks saves professionals a lot of time by eliminating the chances of human error when searching a bulky database for a particular value. To use this Excel Tricks, you add a column to your spreadsheet where you want the found data displayed. In the column, pick the first blank cell and click Insert>Function, then input VLOOKUP and press ENTER. A dialog field will pop up permitting you to state four values for your lookup.
4) Conditional Formatting Formula
If you want to detect particular trends in a data set, Conditional Formatting is one of the best methods you can use. To use this Excel Tricks, select your data, click Format>Conditional Format. A “Manage Rules” dialog box would come up, here formatting rules can be picked to your conditions.
The CONCATENATE function is another Excel Tips & Tricks that looks simple, but can save useful time. Let’s say you have two columns of text that you want to merge. Rather than retype the text, you can use the CONCATENATE function to merge these two columns. To use this Excel Trick, start by introducing a new column where you plan to collect data. The syntax is =CONCATENATE. For instance, =CONCATENATE (A2, B2).
When you have lower rows of data in your worksheet, you can actually decide to change some rows into columns and vice versa. With the Transpose function, you simply can move the row data into columns, (or vice versa). To use this Excel Tricks, start with selecting the column/row you want to transpose. Next, you select Copy>Select the cells on your worksheet where you desire the first row/column to start> Right-click on the cell, pick “Paste Special.” You would have a module pop up where you would see the Transpose option. Select the box followed by OK.
7) IF Statement
With the If function, supply chain professionals can swiftly determine whether any data meets certain conditions. This Excel Tips & Tricks is designed around the “If True” and “If False” tests. This Excel Trick permits for two likely results. Nevertheless, the Nested IF function can also be used. A nested IF permits you to substitute the value_if_false of the function with alternative IF statement, consequently permitting you to simplify the data even more.
8) Dollar Signs
The Dollar Sign ensure the precise column and row is held with equal values even if you copy the same formula in adjacent rows. In this Excel Tips & Tricks, we are actually denoting a cell that is five columns to the left with the similar row. This is known as Relative Formula. Whenever a relative formula is copied, it will fine-tune the values in the formula based on where it is moved. But occasionally, we want the values to stay unaltered and we can achieve it by making the formula into an Absolute Formula. To modify a relative formula into an absolute formula, we put the dollar signs before the row and column values (=$A$5+$C$5).
Stocktaking: The Ultimate Guide to Creating Your Very Own Stocktake Procedure
9) Create Graphs
Creating charts and graphs is part of most Supply Chain Management Jobs. It is part of the best Excel Tricks used to envisage data in a clear and concise manner. But it is no longer a surprise that some professionals get a little bit scared by the prospect of using this Excel Trick. I want the best graphs, this Excel Tricks, works magic and it is pretty much a requirement these days.
The COUNT function sums the total number of cells in a given set of data that have numbers i.e. COUNT (A1:A10). This Excel Tips & Tricks function only works with numbers and counts the cells with numbers only.
11) COUNTIF Function
COUNTIFS allows you to swiftly divide your data and get a count of the total cells in a given set of numbers if they meet certain criteria. This Excel Tricks uses the syntax COUNTIF function i.e. =COUNTIF (range, condition). As with most Excel function, most set of numbers describes the cells you desire to include with the standards specified, whether an expression, number, or text string.
12) Table Formatting
This Excel Tips & Tricks is taken advantage of by only a few. Table formatting permits you to take a data range and transform it into an interactive database. This makes it easier to make decisions based on the data. To create table formatting, click on a cell within your dataset, and select Home>Styles>Format as Table. From there, you can produce an interactive and personalized worksheet.
The Sum function is undoubtedly the most commonly used function out there. This Excel Tricks simply sum up a group of numbers.
This Excel Tricks provides another method to extracting specific information. SUMIFS almost performs similar functions to COUNTIF, but it sums up the specified data. Using this Excel Tips & Tricks, you can lay down more than a condition. The SUMIFS basic syntax is =SUMIFS (sum_range, condition_range1, condition1, condition_range2, condition2…).
These Excel Tricks carries out its respective functions (AVERAGE) IF the set conditions are met. Sometimes, the AVERAGEIFS will do their particular functions based on numerous criteria set within the formula.
16) MAX and MIN functions
With the MAX and MIN functions, what it does is simply return the largest and smallest value from a given range. With this Excel Tips & Tricks, Excel would look through a range and pull in the highest and lowest value. All you need do is highlight the full range of cells you want to check. You can also use it for multiple ranges. You then insert the function MIN or MAX, to get the lowest or highest value.
17) Advanced Filters
If you have used advanced filters, you have probably discovered one of the powers of Excel! These particular Excel Tricks are actually very powerful. This is due to the fact that you can actually filter your data using both formulas and text-based filters. They are also much easier to pick up than regex. Advanced filters are perfect for large datasets.
With the TRIM function, you can get rid of any free space in a cell, except for solo spaces amongst words. This Excel Trick is extremely useful especially in situations where data are needed to be pulled from the database and due to some factors extra spaces are found on top or back of legitimate data. This can leave you frustrated especially when you are trying to make comparisons using IF or VLOOKUP statements.
19) RIGHT, LEFT, MID
These Excel functions (RIGHT, LEFT, MID) returns the specified number of characters from a text string. The RIGHT function gives the number of characters from the right, the LEFT function gives from the left, and MID function starts from the center of the test string.
20) AND function
The AND function is a logical function that checks several conditions and will return a TRUE value if ALL the conditions come TRUE. Otherwise, it returns a FALSE. This Excel Tips & Tricks, is usually linked with an IF statement. Where the IF statement completes certain actions depending on if the AND statement is FALSE or TRUE.
21) OR function
The OR function runs similarly to the AND statement. This Excel Trick looks out for several conditions. Nevertheless, it only needs ONE statement to be TRUE to make other conditions TRUE. And since only one statement needs to be TRUE, then the full statement is TRUE.
22) ROUND function
The ROUND function is used to round up any decimal to the number of decimals that you desire. It is better than formatting the cell in the sense that the actual number in the formula will remain the same. This Excel Tips & Tricks, puts the original formula in the Number field and the Num_digits are the same as the digits after the decimal place you want to display.
23) Keyboard Shortcuts
No Excel Tips & Tricks, would be effective without the addition of some supportive keyboard shortcuts. The keyboard shortcuts can really make things easier for some of the most used Excel functions. This shortcut includes
- CMD + SHIFT + T to sum a complete column of data,
- CMD + 1 shortcut to Format Cells,
- CMD + K faster technique to put a hyperlink, and
- F11 turn data into chart on a fresh sheet.
24) How to Program a Macro
Macro in Excel is a set of several programming commands with a single execution guideline created to aid in automating routine tasks. With Macro programming on Excel, you can have the steps of importing data and formatting it to meet your business reporting requirement. Macro programming are important because they make long physical processes easy by automating it.
They are a lot of useful possibilities available with the use of Excel. These Excel functions are designed to assist in developing your competencies and improve your effectiveness and efficiency. Whether you are just a beginner or a pro in Excel, you are sure to find some excellent Excel Tips & Tricks to match your present Supply Chain data gathering and analysis needs. So, learn some new Excel Tricks today and watch as your output improves significantly!!
If I have missed any functions you think Supply Chain folks should use more frequently, please do mention them in comments below.
Recommended Books & Courses:
Learn Excel 2016 Essential Skills with The Smart Method: Courseware tutorial for self-instruction to beginner and intermediate level
Excel: Excel Mastering Book: Learn Excel, Macros, Shortcuts, and Accounting (Excel Beginners Guide, Excel Mastering, Excel Macros, Excel Shortcuts)
About the Author- Dr Muddassir Ahmed
Dr MuddassirAhmed is the Founder & CEO of SCMDOJO. He is a global speaker, vlogger and supply chain industry expert with 17 years of experience in the Manufacturing Industry in the UK, Europe, the Middle East and South East Asia in various Supply Chain leadership roles. Dr. Muddassir has received a PhD in Management Science from Lancaster University Management School. Muddassir is a Six Sigma black belt and founded the leading supply chain platform SCMDOJO to enable supply chain professionals and teams to thrive by providing best-in-class knowledge content, tools and access to experts.