Excel For supply chain professionals at all levels, knowing valuable Excel Tips & Tricks is essential. I’ve come across several candidates that have a good resume & Profile but when they are asked to use basic Excel formulas like vlookup or pivot table. some of them are unable to do that.Excel is one of major points in Supply chain Interview. In every supply chain interview it is must asked that how candidate is good in excel. If you work in supply chain management, you need to be aware that Excel Tips & Tricks are quite useful in the day-to-day operations of any Supply Chain Management and Logistics activity. The utilisation of Excel Tips & Tricks is an important element of this day-to-day routine. And after you’ve mastered it, you’ll be able to design amazing supply chain models.
Excel for Supply chain Professional – Let ‘s Discuss 10 basicExcel Formula that A Supply chain Professional must need to know.
1- VLOOKUP Function
The VLOOKUP function allows you to search the table for a specific value and then output the value that corresponds to it. This Excel Tips & Tricks saves time for experts by removing the possibility of human error while searching a large database for a certain value. Add a column to your spreadsheet where you want the found data to be displayed to apply this Excel Tricks. Select the first blank cell in the column and select Insert>Function, then type VLOOKUP and hit ENTER. A dialogue box will appear, allowing you to provide four values for your lookup.
The Pivot Table Excel Tricks allows you to categorise tabular data. The Pivot Table enables analysing large data sets and identifying evocative movements simple. To make a Pivot Table, first select your data, then place it in a table, and then select a Pivot Table. You can also create a new spreadsheet and use the Pivot Table command to organise the data.
3-Conditional Formatting Formula
Conditional Formatting is one of the most effective strategies for detecting specific trends in a data set. Select your data and go to Format>Conditional Format to utilise this Excel trick. A dialogue window called “Manage Rules” will appear, where you can choose formatting rules based on your needs.
Even if you copy the identical formula in neighbouring rows, the Dollar Sign ensures that the exact column and row are kept with equal values. We’re actually referring to a cell five columns to the left with a comparable row in this Excel Tip & Tricks. This is referred to as the Relative Formula. When a relative formula is duplicated, the values in the formula are fine-tuned based on where it is transferred. However, there are times when we want the values to remain unchanged, and we can do so by converting the formula to an Absolute Formula. We add dollar signs before the row and column values to make a relative formula absolute (=$A$5+$C$5).
The COUNT function adds up the total number of cells with numbers in a given set of data, i.e. COUNT (A1:A10). This Excel Tip & Tricks function only works with numbers and counts cells that contain only numbers.
If your data meets specific criteria, COUNTIFS allows you to quickly divide it and acquire a count of the total cells in a given set of numbers. The COUNTIF function, i.e. =COUNTIF, is used in this Excel Trick (range, condition). Most set of numbers describes the cells you want to include with the standards specified, whether an expression, number, or text string, as with most Excel functions.
Table formatting allows you to turn a set of numbers into an interactive database. This makes it easier to make data-driven decisions. To create table formatting, pick Home>Styles>Format as Table from a cell in your dataset. You can then create an interactive and customizable worksheet from there.
SUMIFS is almost identical to COUNTIF, except that it sums up the provided data. You can use this Excel Tips & Tricks to create more than just a condition. =SUMIFS (sum range, condition range1, condition1, condition range2, condition2…) is the SUMIFS basic syntax.
The AND function is a logical function that checks many conditions and returns TRUE if all of them are true. Otherwise, a FALSE is returned. This Excel Tips & Tricks is frequently used in conjunction with an IF statement. Where the IF statement performs specific actions based on whether the AND statement is TRUE or FALSE.
The OR function works in the same way as the AND statement. This Excel trick scans for a variety of situations. Nonetheless, just one assertion must be TRUE in order for additional conditions to be TRUE. Because only one of the statements must be TRUE, the entire sentence is TRUE.
Excel shortcut keys- Excel for Supply chain Professional
- F2 — Edit the selected cell.
- F5 — Go to a specific cell.
- F7 — Spell check selected text and/or document.
- F11 — Create chart
- Ctrl + Shift + ; — Enter the current time.
- Ctrl + ; — Enter the current date
- Alt + Shift + F1 — Insert new worksheet.
- Shift + F3 — Open the Excel formula window.
- Shift + F5 — Bring up the search box
- Ctrl + A — Select all contents of a worksheet.
- Ctrl + B — Bold highlighted selection.
- Ctrl + I — Italicize highlighted selection.
- Ctrl + C — Copy selected text.
- Ctrl + V — Paste
- Ctrl + D — Fill
- Ctrl + K — Insert link
- Ctrl + F — Open find and replace options.
- Ctrl + G — Open go-to options.
- Ctrl + H — Open find and replace options.
- Ctrl + U — Underline highlighted selection.
- Ctrl + Y — Underline selected text.
- Ctrl + 5 — Strikethrough highlighted selection.
- Ctrl + O — Open options.
- Ctrl + N — Open new document.
- Ctrl + P — Open print dialog box.
- Ctrl + S — Save.
- Ctrl + Z — Undo last action.
- Ctrl + F9 — Minimize current window.
- Ctrl + F10 — Maximize currently selected window.
- Ctrl + F6 — Switch between open workbooks/windows.
- Ctrl + Page up & Page Down — Move between Excel worksheets in the same document.
- Ctrl + Tab — Move between two or more open Excel files
- Alt + = — Create the formula to sum all of the above cells.
- Ctrl + — Insert the value of above cell into the current cell.
- Ctrl + Shift + ! — Format number in comma format.
- Ctrl + Shift + $ — Format number in currency format.
- Ctrl + Shift + # — Format number in date format.
- Ctrl + Shift + % — Format number in percentage format.
- Ctrl + Shift + ^ — Format number in scientific format.
- Ctrl + Shift + @ — Format number in time format.
- Ctrl + (Right arrow) — Move to next section of text.
- Ctrl + Space — Select entire column.
- Shift + Space — Select entire row.
- Ctrl + W — Close document.
Excel For Supply chain Professional: The Beginner’s Guide to Learn and Master Excel Basics, Formulas, Functions, and New Feature
With Excel, you have a plenty of useful options. These Excel features are intended to add in the development of your skills and the improvement of your effectiveness and efficiency. Whether you’re a newone or a seasoned Excel user, you’ll find some useful Excel Tips & Tricks to help you with your current Supply Chain data collection and analysis needs. So, pick up a few new Excel tricks today and watch as your output skyrockets!!
Other Post you might Interested
- What is the MOQ (minimum order quantity)?
- What is RFQ and how can I use it? A comprehensive guide with examples
- 6 Website to Liquidate Inventory & Earn Dollars
- Lead time Definition -6 Reason to know