Everything about Basics in Excel
Table of Contents
Excel has become much popular among professionals nowadays. And its popularity is increasing day by data with increasing data and data analysis requirements. Whether you want to merge two columns of data excel can do it, you want to run a complex calculation excel can do it using formulas, you want to build reports and dashboards again excel can do it. No doubt excel has many important features and adds a lot of weightages in any professional’s life. If you haven’t installed excel yet you can download it form here.
What is Excel?
Microsoft Excel is a software that is used to store and optimize data sets. Microsoft Excel is mostly used by Data Analyst, Marketers, Accountants, Management Consultants and other professionals. It is one of the most powerful data analysis and data visualization software. Some of the Competitors of Microsoft Excel are Numbers, Google Sheets, Think free, Zoho Sheet etc. Excel features are such that they will speed up your lot of tasks whether you are accountant, marketer or data analyst. Features like autofill and sorting are boon in bane while analyzing heavy data. Thus, it is very important tool especially in data science and finance.
You can use Excel to make Income statements, balance sheets, calendars, marketing reports.
Excel Formulas and Functions
Thera are many formulas available in excel but here we are enlisting the most used ones and the ones which are most basic in excel. All Excel formulas start with ‘=’ sign, followed by tag of the function you want to implement
- SUM – This formula is one of the most basic formulas available and is generally shown like = SUM (value 1, value 2, etc.). This function will add all the values in the bracket. If you use cell address in place of values then it will add all the cells between cell1 and cell2. For subtraction you can simply change the value to be subtracted to negative, for eg here value2 can be ‘-value2’.
- IF – This formula is generally denoted by IF(logical_test, value_if_true, value_if_false). In this statement if logical test is correct then value is 1 is output otherwise value 2 is output.
- Percentage – In this formula you can denote values in the form of B1/B2 which will give you a decimal value. You can then go to home and convert decimal in percentage.
- Multiplication – You can simply mention the formula as ‘=15*30’. In place of values, you can also use cell addresses.
- Array – Implementing calculations on single set of cells is easy but what if calculations have to be performed for various ranges and that too in a single cell. For Example, you want to calculate total sales revenue for below example, instead of writing all the individual formulas you can simply use array. This formula would be ‘=SUM (B2:B5*C2:C5)’
- Count – This formula doesn’t do any kind of math on data values. Rather, it counts the number of elements in the given range.
- Average – If you want to find average in excel you have to simply pass values or cell range in the function and you will get the average. For example, ‘=Average (B1:B10)’.
- SUMIF – This function will return sum of values that satisfy the given criterion within the range. It can be denoted like ‘=SUMIF (range, criteria, [sum range])’
- VLOOKUP – This is one of the most used formula when data on multiple spreadsheets are to be used. VLOOKUP helps us to combine data from two sheets and combine them. When using this formula, we should be certain that at least one column should be identical in both the sheets. The formula for this is ‘ VLOOKUP(lookup value, table array, column number, [range lookup]). Where lookup value is the value that is identical in both the sheets. Table is array is range of columns from which you are going to pull your data in sheet 2, including the column having same values of lookup value. The Colum Number tells you in which column the data that you want to copy is located. In range lookup, we generally use false so that we only copy data that has exact corresponding matches to lookup value.
Basic Excel Shortcuts
Analyzing data and building reports and dashboards in excel is a daily task for many professionals. It can take in huge time from your day, using keyboard shortcuts can really make things easy and fast. There are many shortcuts in Microsoft Excel, but we are mentioning the ones which are most basic in excel.
- Creating a new workbook (PC – Ctrl-N: Mac – Command-N)
- Selecting entire column (PC: Ctrl-Space | Mac: Control-Space)
- Selecting rest of column (PC: Ctrl-Shift-Down/Up | Mac: Command-Shift-Down/Up)
- Selecting entire row (PC: Shift-Space | Mac: Shift-Space)
- Selecting rest of row (PC: Ctrl-Shift-Right/Left | Mac: Command-Shift-Right/Left)
- Autosum selected cells (PC: Alt-= | Mac: Command-Shift-T)
- Inserting date in a cell (Control + ; (semi-colon))
- Inserting time in a cell (Control + Shift + ; (semi-colon))
- Show all the values as percentage (CTRL + Shift + % (PC and Mac))
- Show all the values as currency (CTRL + Shift + $ (PC and Mac))
- Insert Autosum formulas (Alt + (PC); Command + Shift + t (Mac))
- Insert a comment (Shift + F2 (PC and Mac))
As mentioned earlier there are many shortcuts in excel and different professionals might require different ones. We will soon cover a to z excel shortcuts in our upcoming articles.