Excel Archives - Finally Learn

Excel is the most important computer app in business. It is used in every industry and by many professionals. Many jobs require prospective candidates to have advanced Excel skills. Microsoft Excel is the leading digital spreadsheet that manages, calculates, and presents data. It is one of the most fundamental tools for data analysis. This guide will show the basics for getting started with Excel for Windows and Mac. Knowing Excel is an important life skill! Lesson 1 Introduction to Excel for Beginners Getting Started With Excel Excel cells are arranged in rows and columns. Excel uses a grid of columns and rows to create cells. The columns are labeled with letters A, B, C, D, etc. The rows are named with numbers 1, 2, 3, etc. A cell is an intersection of columns and rows. The cell at the intersection of column B and row 3 is called B3. In the example below, cell B3 is selected. Cell B3 Excel cells can contain numeric values such as 123, text labels such as Sales, and calculated values such as 12,300. Workbooks usually contain areas for inputs that include labels and variable values and areas for outputs for calculations and results. Number Formatting Excel can handle both numbers and text. It has many formatting options for numbers. Excel is precise to 15 digits even if the display shows less digits. So, 5.12 would be displayed when 5.123456789012345 is the actual stored value. Excel counts dates beginning with January 1, 1900. So, January 1, 1900 is day 1 and January 1, 2020 is day 43,831 for Excel. Excel Keyboard Shortcuts Excel uses several modifier keys to perform different functions. These keyboard shortcuts are useful for any Excel user. These are necessary to be an advanced user of Excel. See Excel Keyboard Shortcuts for Windows and Mac. https://youtu.be/FvU4onTHvA8 For the complete lesson, see Lesson 1: Introduction to Excel for Beginners. Lesson 2 Excel Statistical Functions Statistics Statistics is the study of mathematics used to explain groups of data, or data sets. Statistical methods are used to compare different groups or data sets. The following table lists the basic statistic terms. StatisticDescriptionExcel FunctionMeanarithmetic average of a set of numbersAVERAGEMedianvalue that occurs in the middle of a data set when arranged from lowest to highest, half the values above and half below the median valueMEDIANModevalue that occurs most frequently in a data setMODE.MULT, MODE.SNGLStandard Deviationmeasure of how widely the data values are dispersed from the arithmetic meanSTDEV.P, STDEV.S There are many Excel functions for statistical analysis. Basic Statistical Functions FunctionDescriptionAVERAGEIFSaverage based on a logical test (replaces AVERAGEIF)COUNTIFScount based on a logical test (replaces COUNTIF)LARGEnth largest valueMEDIANmedian or the middle value in a setMODE.SNGLmode or most frequent numberRANDrandom number from 0 to 1RANDBETWEENrandom number between numbers specifiedRANK.AVGrank in a list of numbers, any ties are averagedRANK.EQrank in a list of numbers, ties are shown as first numberSMALLnth smallest valueSTDEV.Pstandard deviation of a populationSTDEV.Sstandard deviation of a sampleSUMIFSsum based on a logical test (replaces SUMIF) For the complete lesson, see Excel Basics Lesson 2: Statistical Analysis. Lesson 3 Excel Graphs and Charts Excel Graphs Excel can display several types of graphs to visualize data. There are at least 26 different chart types in Excel. The following table shows the common chart types. GraphDescriptionAreatrends over time or by categoryBubblethree values, the third value graphed as the size of a bubbleColumnvertical displayBarhorizontal displayCombocombines two or more chart typesLinetrends over time or by categoryPiecomponents of a total amountStockstock prices and and volume trends over timeX Y (Scatter)compares pairs of numeric values on the x and y axes For the complete lesson, see Excel Basics Lesson 3: Visualizing Data With Graphs. Lesson 4 Excel Logic Functions There are 11 logical functions in Excel. They are on the Formulas ribbon and the Logical button. They include IF and IFS functions. Excel Logical Functions Excel Logical Functions The following table shows all the Excel logical functions. FunctionDescriptionANDreturns TRUE only if all arguments are TRUEFALSEreturns the logical value FALSEIFchecks whether a condition is met, returns one value if TRUE and another value if FALSEIFERRORreturns a specified value if expression is an error and the function value otherwiseIFNAreturns a specified value if the expression resolves to #N/A, and the function value otherwiseIFSruns multiple tests and returns a value corresponding to the first TRUE resultNOTchanges TRUE to FALSE, or FALSE to TRUEORreturns TRUE if any arguments are TRUESWITCHreturns a result on the first matching valueTRUEreturns the logical value TRUEXORreturns a logical “exclusive or” of all arguments For the complete lesson, see Excel Basics Lesson 4: Using Logic in Decision Making. Lesson 5 Excel Text Functions Excel is famous for working with numbers and calculations. However, Excel has many text functions. It is the most useful app for working with cleaning text from a database. Excel Text Functions Here are the basic text edit functions in Excel. FunctionDescriptionCLEANremoves all non printable charactersTRIMremoves spaces from a text string except for single spaces between wordsLEFTextracts a given number of characters from the leftLENcounts characters in a cellMIDextracts from the middle given a starting point and the number of characters neededRIGHTextracts a given number of characters from the rightTEXTconverts a value to text in a specific number format For the complete lesson, see Excel Basics Lesson 5: Working with Text Lesson 6 Time Value of Money using Excel Financial Functions Time value of money (TVM) is the idea that money is worth more today than in the future. Time value of money is a basic concept in finance. Excel can calculate many TVM problems. ConceptExcel FunctionDescriptionPresent ValuePVlump sum at the beginning of a problemFuture valueFVlump sum at the end of a problemPaymentPMTannuity, stream of equal payments per period (+ or -)Number of periodsNPERnumber of periods (months, quarters, half years, years)Interest rateRATEinterest rate per period (annual interest rate / periods per year) For the complete lesson, see Time Value of Money using Excel Financial Functions. Lesson 7 Excel Lookup Functions Excel can select a specific value from a long list of values. These are called Reference and Lookup functions. These functions allow the use of different input values based on criteria to produce an output. Data is often stored in a list on a worksheet. A list that categorizes values is called a lookup table.The lookup and reference functions allow you to find a value based on the lookup table criteria. The most important lookup functions are: VLOOKUPHLOOKUPXLOOKUPLOOKUP For the complete lesson, see Excel Basics Lesson 7: Lookup and Reference Functions. Lesson 8 Excel What If Analysis What if analysis is the process of changing values to see how the changes affect the outcome of the formulas in a spreadsheet. This is a powerful data analysis feature of Excel. The important What If Analysis tools in Excel are: Goal seekData tablesSolverPivot tablesPivot charts For the complete lesson, see Excel Basics Lesson 8: What If Analysis. Lesson 9 Excel Data Analysis Using Excel is a great way to start data analysis. Excel can handle large data sets with tools like pivot tables, pivot charts, and regression analysis. For the complete lesson, see Excel Basics Lesson 9: Data Analysis. Excel Resources Excel Video Tutorials Mr. ExcelBill Jelen is Mr. Excel has been on Youtube for over 10 years and has thousands of great videos. This is a great resource with so many good teaching videos. Excel is FunMike Girvin has over 3,000 videos on Youtube. He has over 86 million views. Wow. It is a great resource for financial and statistical help. I have learned so many things from this channel. Leila GharaniLeila Gharani is my new favorite Excel channel. She is great especially for finance and data visualization. She is very helpful on creating amazing charts and her videos are high quality. Excel CampusThis video channel is from Jon Acampora. He has a great video series on creating Excel dashboards with pivot tables, pivot charts, and slicers. He also has lots of information about VBA and macros in Excel. Finally LearnThis is my video channel. I have videos on accounting, finance, and Excel.