Learn MS Excel and Data Analysis

This course will cover everything that you need to know about excel. We’ll deal with both basic formulas and advanced formulas. For those who are just getting to know excel, for finance students, for financial professionals – this course is perfect to begin with but also to freshen up the knowledge.

Microsoft Excel is the industry leading spreadsheet software program, a powerful data visualization and analysis tool. Excel learns your patterns, organizing your data to save you time. Easily create spreadsheets from templates or on your own and use modern formulas to perform calculations.

A function in Excel is a preset formula, that helps perform mathematical, statistical and logical operations. Once you are familiar with the function you want to use, all you have to do is enter an equal sign (=) in the cell, followed by the name of the function and the cell range it applies to.

Every excel spreadsheet contains multiple rows and columns. When working with data, you will have to know how to add, delete, and modify both rows and columns.

Excel offers plenty of formatting options. A quick way of reducing and adding decimal places can be formed under Number tab, where you can add or deduct with just one click. In excel you can have various information such as date and time and those cells has to be formatted properly so that excel can read and calculate formulas easier. There are formulas that can calculate date range for you. For example, how many days left in a year. You can set date and time Depending on your region and you can set those under more number formats.

Your spreadsheets need to look professional so that everyone can read them easily. In the home tab you can find all the things you need in order for your spreadsheet look professional. You can change the font, font size, colors, alignment, and styles.

In order to copy a cell or a range of cells, you have two options: 1) Right-click on the cell and select “Copy” 2) Select the cell and use the “Ctrl + C” shortcut (preferred). Once you have copied the content of a cell, you can paste it into another cell. You can either do it with a right-click and then select paste or use the Ctrl + V shortcut, which is much faster.

The Paste Special dialog box (which you can open by using the “Alt + E + S” shortcut) provides many pasting options. For example, you can choose to paste only the formulas of the copied cells, paste the values of the copied cells, or paste only the format of the copied cells

What are macros in Excel? An Excel macro is a series of instructions. After you've created a macro, Excel will execute those instructions, step-by-step, on any data that you give it. For example, you can have macro for formatting your spreadsheet - always start your worksheet with the same formatting.

Every formula in excel can be started with an equal or plus sign.

Freeze panes let you view your data in an organized way when you have many rows and columns.

SUM formulas in Excel are the easiest and most commonly used. They let you sum the cells and sum only if certain conditions are met.

The COUNT function counts the number of cells that contain numbers and counts numbers within the list of arguments. Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers.

The average function returns the average (arithmetic mean) of the arguments. You can average the numbers if conditions are met with average if functions.

The IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. Check if a certain condition is true or false with IF function.

Use VLOOKUP and HLOOKUP when you need to find things in a table or a range by row / column. Mostly commonly used in finance to find product by name or code.

INDEX and MATCH are great VLOOKUP replacements. INDEX and MATCH is the most popular tool in Excel for performing more advanced lookups.

Goal seek is the art of what-if analysis tool. You can find this function under Data, Forecast, and then what-if analysis.

With goal seek you can find the answers or desired result for a given parameter by changing other parameters related to it.

Pivot tables are dynamic and interactive tables, and pivot tables can summarize thousands of rows of data in just a few seconds.

Choose function returns a value from an array corresponding to a supplied index number (position). This is a perfect function for financial modeling.

Min and max functions are very simple and quite intuitive.

Min returns the smallest number in a set of values, while max returns the highest number in a set of values.

Conditional formatting can help you add colors, icons, and visuals to your table and numbers.

It will help you read data faster as well.

Relative references change when a formula is copied to another cell.

On the other hand, absolute references remain constant no matter where they are copied. Fixing cell references make your job much easier because copying is much faster and help you organize your worksheets.

We can insert a chart by selecting the cells we want to present and then clicking on the insert tab….

If you are not sure which chart is the best for your data, you can click on the recommended chart.

Excel will recommend an excellent chart to showcase your data. Then you can pick which graph is the best fit.

We often want to combine numbers into the same chart and then analyze them. That is why we need a secondary axis in our charts.

Use the Find and Replace features in Excel to search for something in your workbook or worksheet, such as a particular number or text string.

With the DATE function, you can manipulate data and extract a certain number of days, years, months. And this is possible because excel stores date as numbers.