SUMMER SPECIAL EDITION EXCEL WITH “EXCEL“
Editor’s Note April 2018 Heartiest greetings to all! With great excitement and fervour, team Quantinuum presents “Quriosity” - the Quant magazine of SIMSR. This edition of Quriosity is summer special edition which focuses on some of the important articles on Excel. Excel is one of the famous and widely used tools in corporate for data analysis and data representation. The Excel as we know is just another Microsoft tool to do some basic mathematical operations and solve some LPP. But it’s a lot more than that. Excel can be used to do operations related to data analysis and representing data. Knowing excel better can surely make you look better than rest of your peer. With the last edition we brought to you the important formulas in excel. This edition has a lot more useful information about Excel. As the cover story we have the “Importance of Excel in Business” on page no 3 which covers the some of the important uses of excel in various business domains. As the sub article we have some of the “Important Shortcuts in Excel” on page no 6 which presents you the basic shortcuts which can help you to save a lot of time in the frequent operations you do in excel. We also have some “Useful tips for Excel” on page no 9 which presents you about the some of the useful tips in excel which can make you working with excel easier. Hope! These articles are useful to you in your internship as well in your future corporate life.
Happy Learning, Editorial Team!
QURIOSITY www.quantinuumsimsr.com
PAGE| 2
Content TOPIC
PAGE-NO
Quant Cover Story - Importance of Excel in Business
4
Excel Shortcuts
7
Excel Formulae’s
10
Excel Tips
16
Quant Connect
19
QURIOSITY www.quantinuumsimsr.com
PAGE| 3
Cover Story Importance of Excel in Business
M
icrosoft Excel was released in 1985
and has grown to become arguably the most important computer program in workplaces around the world. Whether you are budgeting, organizing client sales lists, or need to plan an office social gathering, Excel is a powerful tool that has become entrenched in business processes worldwide.
The benefits to a small business using Microsoft Excel may be fairly obvious – Excel offers a grid-like platform where you can enter and transform data into formatted information, as well as use features such as charts and graphs. But Excel offers far more than just ways to dress up the characters in its cells. Small businesses may find Excel far more of a workhorse than expected, with Excel "excelling" in its contributions to both financial and fiscally related areas of the company. Fact Check While it's possible to do some of the calculations required for an Excel spreadsheet in your head, on paper or with a calculator, you won't need to take that possible risk of entry error. Excel relieves you of the need to perform calculations with a couple of included features. Located on the "Home" tab's "AutoSum" button are instantly clickable ways to add an addition, averaging, division and
subtraction to your spreadsheets. For example, if you're totalling up payroll expenses for your 20-person staff, highlight the salary cells and click the "AutoSum" button to instantly see the total spent. Any time you click in to change a payroll number, the AutoSum feature automatically updates. Printing Press Although your business will have to take care of the actual printing, Excel can turn your small business into a small printing press without having to enlist graphic designers, desktop publishers or layout personnel. Found on Excel's "File" tab's "New" option is a large collection of templates. Use these templates to create materials without having to start from scratch, saving you setup time on forms such as receipts, invoices, labels, and timesheets, as well as more creative items such as cards, booklets, presentations, and fliers. All of Excel's templates are completely customizable, which means your business can benefit from all of the setups but still leave personal stamps such as inserting a logo or changing the colours and fonts on the materials.
QURIOSITY www.quantinuumsimsr.com
PAGE| 4
Accessible Records
Finance and Accounting
One of the main benefits of the program is how you can always access the information on your spreadsheets. Unlike keeping data in a ledger, which can get messy with cross-outs, erasures, and liquid paper, Excel spreadsheets remain in their easy-toread format and are available – even enlargeable with the zoom features – any time you need to get your information. You can update records by creating templates, which you open, add a line or two of information to, then resave with a new version number, giving you ways to both create, and cover, your tracks.
If you walk through the finance or accounting department at any major corporate office, you will see computer screens filled with Excel spreadsheets outlining financial results, budgets, forecasts, and plans used to make big business decisions.
Collection Spot Excel offers innumerable ways to assist in the day-to-day business functions of finance and math, but it can also serve your business as a catch-all. If you're preparing one spreadsheet from multiple documents including other spreadsheets, images, portable document format files and other files, Excel can help your business corral everything into one single file. Use the "Insert" tab to add other objects directly onto the spreadsheet, keeping everything together. Add extra sheets to the spreadsheet at the bottom of the work page, or keep everything on one sheet and take advantage of the scroll bar. Collecting multiple files in one spreadsheet may make the Excel file slightly larger, but you then only have one file to send rather than many.
This is the area of business with the biggest reliance and benefit from Excel spreadsheets. Advanced formulas in Excel can turn manual processes that took weeks to complete in the 1980s into something that takes only a few minutes today. Most users know that Excel can add, subtract, multiply, and divide, but it can do much more with advanced IF functions when coupled with VLOOKUP, INDEXMATCH-MATCH, and pivot tables. Marketing and Product Management While marketing and product professionals look to their finance teams to do the heavy lifting for financial analysis, using spreadsheets to list customer and sales targets can help you manage your sales force and plan future marketing plans based on past results. Using a pivot table, users can quickly and easily summarize customer and sales data by category with a quick drag-and-drop. All parts of the business can benefit from strong Excel knowledge, and marketing functions are not exempt.
QURIOSITY www.quantinuumsimsr.com
PAGE| 5
Human Resources Planning While database systems like Oracle (ORCL), SAP (SAP), and Quickbooks (INTU) can be used to manage payroll and employee information, exporting that data into Excel allows users to discover trends, summarize expenses. And hours by pay period, month, or year, and better understand how your workforce is spread out by function or pay level. HR professionals can use Excel to take a giant spreadsheet full of employee data and understand exactly where the costs are coming from and how to best plan and control them for the future. The Bottom Line Excel is not going anywhere, and businesses will continue to use Excel as a primary tool for diverse functions and applications ranging from IT projects to company picnics. A working knowledge of Excel is vital for most office based professionals today, and stronger Excel skills can open the door to promotion and leadership opportunities. Excel is a powerful tool but cannot function alone. It takes a savvy computer user to take advantage of everything Excel has to offer to provide the best results for their company.
QURIOSITY www.quantinuumsimsr.com
PAGE| 6
Excel Shortcuts As we all know that Microsoft Excel is a very powerful application for spreadsheet processing and a pretty old one. Each new version of Excel came with more and more new shortcuts and seeing the full list (over 200!) you may feel a bit intimidated. Don't panic! Following few shortcuts will help you to spend a little less time navigating, formatting, selecting, and entering formulas for your data. Managing and Navigating through workbooks Use the arrow keys on your keyboard to activate cells on the worksheet Shortcut
Description
Shift + arrow keys
Highlight a range of cells in a worksheet.
Ctrl + PageUP/PageDown
To open other worksheets in a workbook.
Shift + F11
Inserting New worksheet.
Ctrl+N
Create a new workbook.
Ctrl+O
Open an existing workbook
F12
Save the active workbook under a new name, displays the Save as dialog box
CTRL+W
Close the active workbook.
Home
Return to the 1st cell of the current row in a worksheet.
Ctrl+Home.
Move to the beginning of a worksheet (Đ?1 cell)
Ctrl+End
Move to the last used cell of the current worksheet, i.e. the lowest row of the rightmost column.
QURIOSITY www.quantinuumsimsr.com
PAGE| 7
Formatting data Shortcut
Description
F2
To edit a cell press Fn+F2, cell will be activated
Ctrl + B/I/U
Hold the CTRL key while pressing the letter B/I/U for Bold/Italic/underline
Ctrl+1
Open the "Format Cells" dialog.
Alt + H + W
For wrap text
Ctrl + 9
Hold down the CTRL key while pressing the number 9 key.
Ctrl + Shift + )
Highlight cells above and below the hidden row(s), then hold down the CTRL key and the SHIFT key while pressing the open parenthesis key “(“ on your keyboard.
ALT key and then the letters H, I, and C one at a time.
A column will be inserted to the left of the activated cell
ALT key and then the letters H, D, and R one at a time.
The row with the activated cell will be deleted.
ALT key and then the letters H, M, and C one at a time.
Merge and centre
ALT key and then the letters H, M, and U one at a time.
Unmerge Cell
Activate a cell location.
For Entering Multiple lines of Text.
Type the first line of text. Hold down the ALT key and press the ENTER key. Type the second line of text and press the ENTER key. QURIOSITY www.quantinuumsimsr.com
PAGE| 8
Selecting data Shortcut
Description
Ctrl+A.
Select the entire worksheet. If the cursor is currently placed within a table, press once to select the table, press one more time to select the whole worksheet
Ctrl+Home then Ctrl+Shift+End
Select the entire range of your actual used data on the current worksheet.
Ctrl+Space
Select the entire column
Shift+Space
Select the entire row.
Working with Formulas
Shortcut
Description
Tab
Autocomplete the function name. Example: Enter = and start typing vl, press Tab and you will get =vlookup(
F4
Cycle through various combinations of formula reference types. Place the cursor within a cell and hit F4 to get the needed reference type: absolute, relative or mixed (relative column and absolute row, absolute column and relative row).
Ctrl+`
Toggle between displaying cell values and formulas.
CTRL+'
Insert the formula of the above cell into the currently selected cell or the Formula Bar.
QURIOSITY www.quantinuumsimsr.com
PAGE| 9
Excel Formulae’s Excel is one of the most important tools used in the corporate world to handle different forms of data. Excel has the capability to solve a lot of problems and perform various analyses using a lot of formulas that are inbuilt in it. We have bought to you a set of excel formulas which will be helpful in handling the data during your internship. And these are just few of the formulas which Excel has. We suggest you to explore the formulas tab of excel to get to know about the various formulas which can be useful in your domain. Cleaning and Formatting Functions: Cleaning and formatting is the first step of any data analysis process. This is important so as to get your analysis and interpretation right. Formatting helps to display your data in a better way so that it appeals to the reader. 1) Upper: Description: Upper is a function which converts the text you have to uppercase. You can give the reference of a cell or give a word directly within double quotes. Helps to format the text copied from another source. Usage: UPPER(B23) or UPPER(“Hello world”) Output: HELLO WORLD 2) Lower: Description: Lower is a function which converts the text you have to lowercase. You can give the reference of a cell or give a word directly within double quotes. Helps to format the text copied from another source. Usage: LOWER(B23) or LOWER(“Hello world”) Example: hello world 3) Proper: Description: Proper is a function which converts the text you have to a case where every first letter of the word is in the upper case and all the remaining letters of the word are in the lower case. You can give the reference of a cell or give a word directly within double quotes. Helps to format the text copied from another source. Usage: PROPER(B23) or PPROPER(“hello world”) Output: Hello World QURIOSITY www.quantinuumsimsr.com
PAGE| 10
4) Trim: Description: Usually when we copy data from another source, there might be chances of having white spaces before or after the text. And this might cause problems when you are handling numbers. Use this function to remove the trailing and leading white spaces from any cell or string directly. Usage: TRIM(B23) or TRIM(“ 2345 ”) Output: 2345 5) Left: Description: Sometimes you might need only a selected number of characters to perform a particular operation. This function helps you to extract a particular set of characters from a string from the left side. Usage: Left(<Word/Cell Ref>,<no of letters to extract>) Example: Left(“Hi all”,2) Output: Hi 6) Right: Description: Sometimes you might need only a selected number of characters to perform a particular operation. This function helps you to extract a particular set of characters from a string from the right side. Usage: Right(<Word/Cell Ref>,<no of letters to extract>) Example: Right(“Hi all”,3) Output: all 7) Mid: Description: Sometimes you might need only a selected number of characters to perform a particular operation. This function can help you to extract a particular set of characters from a string. The index of the numbers starts from 1. Usage: Mid(<Word/Cell Ref>,<Starting index>,<no of letters to extract>) Example: MID(“E9838L”,2,4) Output: 9838 8) Repeat Description: This function is used to repeat a character for a particular number of times in a given cell. This can be used in the form of a cell chart, typically a bar chart which will help you to present you data in a better way. QURIOSITY www.quantinuumsimsr.com
PAGE| 11
Usage: REPT(<character in â&#x20AC;&#x153;â&#x20AC;?>,<no of times>) Example: REPT("|",A1/40) Output:
9) CONCATENATE In case you want to take values from two or more cells and combine them into one cell, use the concatenate operator (&) or the CONCATENATE function
Sorting and Logical Operators: You might be aware of some of the sorting and logical operators which will help you to sort data and logical operators like if, else, if else, but there are a few other operators which will be helpful in some of the special cases where you have to simplify to if else function. Description: You would have used min function to find out the smallest number from an array but have you tried to get the second smallest or third smallest number from an array? This function will help you to do that. Usage: SMALL(<array/group of Cell Ref>,<n >) [n is the nth smallest value] Example: SMALL({1,2,3,4,5,6,7},2) or SMALL(B2:B5,2) Output: 2 1. Large: Description: You would have used max function to find out the largest number from an array but have you tried to get the second largest or third largest number from an array? This function will help you to do that.
QURIOSITY www.quantinuumsimsr.com
PAGE| 12
Usage: LARGE(<array/group of Cell Ref>,<n >) [n is the nth largest value] Example: LARGE({1,2,3,4,5,6,7},2) or SMALL(B2:B5,2) Output: 6 2. Choose: Description: This is a simplified version of using more than one if else function in Excel. For example, if you had to create something like a grade book this would come handy. You have to give a number according to which an option is selected. Usage: CHOOSE(<index>,<Value 1>,<Value 2>,<Value 3>..( [value could be a cell of string] Example: CHOOSE(2,”A”,”B”,”C”) Output: B Note: Here the number will be arrived by some other formula which will define which option to choose. 3. If: Description: This is a very simple function which can be used for various purposes like using two different formulas for two different scenarios, or formatting data by restricting your output to non-negative numbers or a particular text. Usage: IF(<condition>,<Value if true>,<Value if false>) Example: IF(B4>0,B5,0) Output: If B4 is negative the cell will have its value as zero else it will have the value which is in B4.
4. INDEX & MATCH The Excel INDEX function returns a value in an array based on the row and column numbers you specify. The syntax of the INDEX function is straightforward: INDEX(array, row_num, [column_num]) array - this is a range of cells that you want to return a value from. row_num - the row number in array from which you want to return a value. If omitted, the column_num is required. column_num - the column number in array from which you want to return a value. If omitted, row_num is required. =INDEX(A1:C10,2,3)
QURIOSITY www.quantinuumsimsr.com
PAGE| 13
The formula searches in cells A1 through C10 and returns a value of the cell in the 2nd row and the 3rd column, i.e. cell C2. The Excel MATCH function searches for a lookup value in a range of cells, and returns the relative position of that value in the range. For example, if the range B1:B3 contains the values "New-York", "Paris", "London", then the formula =MATCH("London",B1:B3,0) returns the number 3, because "London" is the third entry in the range. The syntax of the MATCH function is as follows: MATCH(lookup_value, lookup_array, [match_type]) lookup_value - this is the number or text you are looking for. This can be a value, a cell reference or logical value. lookup_array - a range of cells being searched. match_type - this parameter tells the MATCH function whether you want to return an exact match or the nearest match: • 1 or omitted - finds the largest value that is less than or equal to the lookup value. The values in the lookup array must be sorted in ascending order, i.e. from smallest to largest. • 0 - finds the first value that is exactly equal to the lookup value. In the INDEX / MATCH combination, you almost always need the exact match, so the third argument of your MATCH function is "0". • -1 - finds the smallest value that is greater than or equal to lookup_value. The values in the lookup array must be sorted in descending order, i.e. from largest to smallest. The MATCH function determines the relative position of the lookup value in the specified range of cells. From there, the INDEX function takes that number, or numbers, and returns a value in the corresponding cell. =INDEX (column to return a value from, MATCH (lookup value, column to look up against, 0))
QURIOSITY www.quantinuumsimsr.com
PAGE| 14
To find population of Japanâ&#x20AC;&#x2122;s capital using MATCH & INDEX as follows =INDEX($D$2:$D$10,MATCH("Japan",$B$2:$B$10,0)) The MATCH function searches for the lookup value "Japan" in column B, more precisely cells B2:B10, and returns the number 3, because "Japan" is the third in the list. The INDEX functions takes "3" in the second parameter (row_num), which indicates which row you want to return a value from, and turns into a simple =INDEX($D$2:$D$10,3). Translated into plain English, the formula reads: search in cells D2 through D10 and return a value of the cell in the 3rd row, i.e. cell D4, because we start counting from the second row.
Date Functions: Date is a function which will be of frequent use in your data. These dates will help you understand the deadlines and if you are working on a project, you would have to use a lot of date functions. Below are a few date functions which will help you with this. QURIOSITY www.quantinuumsimsr.com
PAGE| 15
1. Today Description: This function gives you the present date and the reason that this is useful is that it can be used to calculate the number of days left for the project. Usage: Today() Output: The output will be today in the format which is specified for the given cell. 2. Network Days Description: This function will help you to find out the number of working days between two dates. This can be used to find out the number of days you get from the start and the end of the deadline. Usage: NETWORKDAYS(I17,I19)[ I7- 4/17/2017 I9-5/17/2017] Output: 23 3. Workday Description: This function will help you to find the exact date which is the number of working days from today or any other day. Usage: WORKDAY(TODAY(),20) Output: 5/15/2017 Mathematical and Financial Functions: There are some mathematical functions varying from basic sum, average to complex functions. These functions come handy whenever you have to deal with numeric data. Also there are few financial functions which will help you by doing most of the calculations. Below are a few formulas which will help to reduce the number of steps required for calculations or the need of using a calculator. 1. Convert Description: This function can convert a measurement from one unit to another. It could be distance units, weight units and even date. Usage: Convert(<measure>, <from units>,<to units>) Note: The from units and to units have a specific code. For example gram is represented by “g” and ounces by “ozm”. Check this link for the specification. Link 2. FV: Description: This function is used to find out the future value of a set of payments given the number of years, rate of interest and payments. Usage: FV(<rate in percentage>,<no of year>,<payments>) Example: FV(10%,12,-1000) Output: 21384.28 QURIOSITY www.quantinuumsimsr.com
PAGE| 16
3. NPV: Description: This function helps to calculate the net present value of a series of payments. It requires the series of payments and the percentage of the rate of interest. Usage: NPV(<rate in percentage>, payment 1, payment 2, payment 3..) Example: NPV(10%,100,100,100) Output: 248.69 4. PMT: Description: This function gives you the monthly instalments that you have to pay for a particular principal borrowed at a certain rate of interest. Usage: PMT(<rate in percentage>,<no of payments>,<principle>) Example: pmt(10%,12,-100000) Output: 14676.33 Below is a summarized list of all the functions that have been mentioned in the article. Function Upper Lower Proper Trim Left Right Mid Rept Small Large Choose If Today Networkday Workday Convert FV NPV PMT
Description Converts text to uppercase Converts text to lowercase Converts text to first letter capital Removes trailing and leading spaces Trims a text from left side Trims a text from right side Slices a part of text from between Repeats a character a particular number of times Gives the nth smallest number from an array Gives the nth largest number from an array Simplified multiple if else function Used to execute a conditional statement Gives todayâ&#x20AC;&#x2122;s date Gives the number of working days between two dates Gives the date after a number of working days from a specified date Converts one unit to another Gives future value of a payment Gives the present value of a payment Gives each monthly installment for a specified cost of capital and number of payments
QURIOSITY www.quantinuumsimsr.com
PAGE| 17
We have referred to the website Chandoo.org for the above formulas. It has quite a number of formulas in Excel which can be used as per your need. This was recommended by our seniors, and these formulas were of great use to them during their internship. Excel is a major tool which everyone will be using during the course of their internship and that is why we have come up with our first article based on it. Please write to us about your feedback by mailing us at quantinuum.simsr@ somaiya.edu. Hope these formulas would be helpful to you during your internship. They would be of considerable use to present your reports in a systematic manner thereby creating a good impression about you. Also, using these formulas will reduce the time required for a particular task which will prove to be a differentiator with respect to other interns.
QURIOSITY www.quantinuumsimsr.com
PAGE| 18
Excel Tips Deleting Worksheets - Be very cautious when deleting worksheets that contain data. Once a worksheet is deleted, you cannot use the Undo command to bring the sheet back. Deleting a worksheet is a permanent command If you are working with someone who is using a version of Microsoft Office that is older than Office 2007, you will have to save your workbook under the Excel 97-2003 Workbook format. A person who is running Office 2003 will not be able to open workbooks that are saved under the Office 2010 or Office 2007 file types. You can save a workbook as an Excel 97-2003 file type by clicking the down arrow next to the â&#x20AC;&#x153;Save as typeâ&#x20AC;? box in the Save As dialog box The benefit of using the Wrap Text command is that it significantly reduces the need to expand the column width to accommodate multiword column headings. The problem with increasing the column width is that you may reduce the amount of data that can fit on a piece of paper or one screen. This makes it cumbersome to analyze the data in the worksheet and could increase the time it takes to make a decision. Why? Pound Signs (####) Appear in Columns When a column is too narrow for a long number, Excel will automatically convert the number to a series of pound signs (####). In the case of words or text data, Excel will only show the characters that fit in the column. However, this is not the case with numeric data because it can give the appearance of a number that is much smaller than what is actually in the cell. To remove the pound signs, increase the width of the column.
QURIOSITY www.quantinuumsimsr.com
PAGE| 19
Standard Order of Mathematical Operations Symbol
Order
^
First: Excel executes any exponential computations first
* or /
Second: Excel performs any multiplication or division computations second. When there are multiple instances of these computations in a formula, they are executed in order from left to right.
+ or â&#x2C6;&#x2019;
Third: Excel performs any addition or subtraction computations third. When there are multiple instances of these computations in a formula, they are executed in order from left to right.
()
Override Standard Order: Any mathematical computations placed in parentheses are performed first and override the standard order of operations. If there are layers of parentheses used in a formula, Excel computes the innermost parentheses first and the outermost parentheses last.
QURIOSITY www.quantinuumsimsr.com
PAGE| 20
Quant Connect Q
uantinuum, the Quant forum of KJ Somaiya Institute of Management Studies and
Research aims to empower students and professionals alike to organize and understand numbers and, in turn, to make good and rational decisions as future managers. The newsletter published monthly consists of a gamut of articles for readers ranging from beginners to advanced learners so as to further enrich the young minds understand the contributions made to the field of mathematics along with a couple of brain- racking sections of Sudoku to tickle the gray cells. For any further queries and feedback, please contact the following address: KJ Somaiya Institute of Management Studies and Research, Vidya Nagar, VidyaVihar, Ghatkopar East, Mumbai -400077 Mentor Prof. N.S. Nilakantan (+919820680741) â&#x20AC;&#x201C; Email â&#x20AC;&#x201C; nilakantan@somaiya.edu Team Leaders: Purav Shah (+917708521382) VVNS Anudeep (+919441201685) Yatharth Jaiswal (+919969698361) Editorial Team: VVNS Anudeep (+919441201685) Khushbu Mehta (+919930158610) Kapil Gupta (+917727936906) Aditya Sharma (+918302525599) Samoshri Mitra (+918697440265) Dropad Saxena (+919582337930) Akshay Nagpal (+918800114925)
QURIOSITY www.quantinuumsimsr.com
PAGE| 21
Quantinuum is soon coming with QSIC- The Quant summer internship competition which is ready to glorify the best analytic summer internship project. Get ready to showcase your project and win the cash prizes waiting for you.
QURIOSITY www.quantinuumsimsr.com
PAGE| 22