Looking for examples of work in Excel? Looking for clear instructions that will allow you to master the great variety of Excel tools quickly and easily? We bring to your attention Tutorial for working in Excel with simple examples. Here you will find answers to a variety of questions and learn how to use Excel in practice. The tutorial consists of five sections:
Each section contains several chapters. In each chapter you will find an introductory lesson that reveals the basic concepts and provisions, and some simple and understandable examples. Want to learn more about Excel? Start learning Microsoft Excel immediately with Office Guru! With us it will be much easier for you!
This Excel course is a translation of a very popular English-language self-learning site – excel-easy.com, which is visited by tens of thousands of people every day! I think that he will not leave you indifferent!
Introduction
This section is intended for users who are not familiar with Excel.
Microsoft Excel is one of the most widely used applications in history. Hundreds of millions of people around the world use Microsoft Excel. Excel can work with data of any type and perform financial, mathematical and statistical calculations.
- Range: A range in Excel is a set of two or more cells. In this chapter, you will find an overview of some very important range operations. Examples: Fibonacci Sequence, Custom Lists, Notes, Hiding Rows and Columns, Ignore Empty Cells, Transpose, Union and Intersect.
- Formulas and functions: A formula is an expression that calculates the value of a cell. Functions are predefined formulas available in Excel. Examples: Percent Change, Formula Names, Dynamic Named Range, Paste Options, Status Bar, Quick Operations.
Basics
This section provides basic concepts for working in Excel.
- Menu Ribbon: When you start Excel, the Menu Ribbon displays the Home tab. Learn how to collapse and customize the Ribbon. Examples: Quick Access Toolbar, Developer tab.
- Workbook: A workbook is an Excel file. When you start Excel, an empty workbook is automatically created. Examples: Save as Excel 97-2003, View Multiple Workbooks, Workspace, AutoRecovery.
- Worksheet: A worksheet is a set of cells in which you store and process data. By default, each Excel workbook contains three sheets (in Excel 2010 and earlier). Examples: Scale, Split Sheet, Freeze Regions, Group Sheets, Consolidate, View Multiple Sheets, Check Spelling.
- Cell Formatting: When formatting cells in Excel, we only change the appearance of the content, without changing the value itself. Examples: Decimals, Monetary vs. Financial, Date and Time Formats, Fractions, Text to Number, Number to Text, Custom Number Formats, Format By Sample, Cell Styles, Themes.
- Find and Select: In this chapter, you will learn how to use two very useful tools in Excel: Find and Replace and Go To. Examples: Features of the Find Tool, Delete Blank Rows, Row Differences, Copy Only Visible Cells, Search Bar.
- Templates: Instead of creating an Excel workbook from scratch, you can create a workbook based on a template. There are many free templates out there just begging to be used. Examples: Calendar, Budget, Meal Planner, Invoice, Automated Invoice, Default Templates.
- Data Validation: Use data validation in Excel and ensure that users enter only the correct value into a cell. Examples: Discard Invalid Dates, Budget Limit, Avoid Duplicate Entries, Product Codes, Drop Down List, Dependent Drop Down Lists.
- Keyboard shortcuts: Keyboard shortcuts allow you to work faster by using the keyboard instead of a mouse. Examples: Function keys, Key Tips.
- Printing: This chapter will teach you how to print Excel sheets and set some important print settings. Examples: View Modes, Page Breaks, Headers and Footers, Page Numbers, Print Headers, Centering on Page, Print Grid and Row/Column Headers, Print area.
- Data sharing and sharing: Learn how you can send Excel data to a Word document or other files. In addition, the many examples provided will show you how to share an Excel workbook. Examples: Excel Sheet to Word, Save to PDF, Share Workbook, SkyDrive, Excel Online, Import Data from Access, Microsoft Query, Import and Export text files, XML.
- Security: Encrypt the Excel file with a password. Only by knowing the password will it be possible to open this file. Examples: Workbook protection, Sheet protection, Cell lock, Read-only workbook, Mark as final.
functions
Learn how functions in Excel can save you time. If you are just getting started with functions in Excel, we recommend that you first read the Introduction to Formulas and Functions chapter.
- COUNT and SUM: The most commonly used functions in Excel are counting and summing functions. You can count the number and sum of values selected by one or more criteria. Examples: Count occurrences of text, Count Booleans, Count empty and non-blank cells, SUMPRODUCT function.
- Boolean Functions: Learn how to use Excel’s Boolean functions such as IF, AND, OR. Examples: Nested IF Functions, Roll the Dice Game.
- Cell References: Cell reference is a very important element in Excel. Understand the difference between relative, absolute and mixed links, and you will surely succeed! Examples: Copying an exact formula, 3D link, Creating an external link, Hyperlinks.
- Date and time: To enter a date in Excel, use the separator characters: slash (/), dash (-), or period (.). To enter a time, use a colon (:). Date and time can be entered in one cell. Examples: Function RAZDAT, Weekdays and working days, Days to birthday, Time sheet, Last day of the month, Holidays, Quarter, Day of the year.
- Text functions: Excel offers a huge number of functions for a wide variety of operations on text strings. Examples: Split a line, Number of instances of text, Number of words, Text by columns, Upper and lower case, Remove unnecessary characters, Compare text, FIND and SEARCH functions, Functions SUBSTITUTE and REPLACE.
- Search and Reference Functions: Learn all about Excel’s reference and array functions such as VLOOKUP, HLOOKUP, MATCH, INDEX, and SELECT. INDIRECT.
- Financial Functions: This section introduces the most popular Excel financial functions. Examples: Loans of various durations, Investments and annuities, Calculation of compound interest, Loan repayment schedule, Amortization.
- Statistical Functions: Here you will find an overview of the most useful Excel statistical functions. Examples: Negative numbers by zeros, Random numbers, RANK, PERCENTILE and QUARTILE function, FORECAST and TREND.
- Rounding: This section describes three of the most popular functions for rounding numbers in Excel. These are the ROUND, ROUNDUP, and ROUNDDOWN functions. Examples: Drop decimal places, Nearest multiple, Odd and even.
- Formula Errors: This chapter will teach you how to deal with some common errors in Excel formulas. Examples: IFERROR, ISERROR, Circular Reference, Formula Dependencies, Floating Point Error.
- Array Formulas: After studying this chapter, you will understand what array formulas are in Excel. Single-cell array formulas allow you to perform a series of calculations in one cell at once. Examples: Error Count, Unique Value Count, OR Count, Sum Every nth Row, Sum Largest Numbers, Sum Range with Errors, Sum with OR , Two-column search, Most frequently occurring word, System of linear equations.
Анализ данных
This chapter talks about the powerful tools that Excel offers for data analysis.
- Sorting: In Excel, you can sort by one or more columns. Arrange the data in descending or ascending order. Examples: Sort by color, Reverse list, Random list.
- Filtering: Set up a data filter in Excel to only see records that meet certain criteria. Examples: Numeric and text filters, Date filters, Advanced filter, Data form, Remove duplicates, Structuring data.
- Conditional Formatting: Conditional formatting in Excel allows you to highlight cells in a specific color depending on the values they contain. Examples: Rule Management, Bar Charts, Color Scales, Icon Sets, New Rule, Duplicate Finder, Coloring Alternate Rows, Compare Two Lists, Rule Conflict, Check list.
- Charts: The simplest chart in Excel can be more telling than a table full of numbers. You will see: charts are easy. Examples: Column Chart, Graph, Pie Chart, Bar Chart, Area Chart, Scatter Chart, Data Series, Axes, Chart Sheet, Trendline, Margin of Error, Sparklines, Combo Chart, Speedometer Chart , Thermometer chart, Gantt chart, Pareto chart.
- Pivot Tables: Pivot tables are one of the most powerful tools in Excel. A pivot table will allow you to highlight the essence of a huge data set.
- Tables: Tables are needed in order to analyze existing data quickly and easily.
- What-if analysis: What-if analysis in Excel allows you to substitute different values (scripts) into formulas.
- Find Solution: Excel has a tool called Find Solution. It uses various computational analysis methods to find the best solutions for optimization problems of all kinds.
- Analysis Pack: Analysis Pack is an Excel add-in that provides tools for analyzing financial, statistical, and technical data.
VBA
VBA (Visual Basic for Applications) is the name of the programming language for Microsoft Office. This section describes the features and examples of using VBA in relation to Excel.
- Creating a macro: With VBA you can automate tasks in Excel by writing so-called macros. In this section, you will learn how to create a simple macro.
- MsgBox: MsgBox is a dialog box in VBA that allows you to display informational messages to users of your program.
- Workbook and Worksheet Objects: Learn more about Workbook and Worksheet Objects in VBA.
- Range Object: A Range object is a cell (or cells) in your worksheet. This is the most important VBA object.
- Variables: In this section, you will learn how to declare, initialize, and display a variable in VBA.
- If Then Statement: Use the If Then statement in VBA to execute lines of code if a certain condition is met.
- Loop: Loops are the most powerful programming technique. A loop in VBA allows you to process a range of cells over and over again with just a few lines of code.
- Macro Errors: This section will teach you how to deal with macro errors in Excel.
- String Operations: In this section, you will find information about the most important functions for working with string elements in VBA.
- Date and Time: Learn how to work with date and time in VBA.
- Events: Events are actions taken by users that trigger the execution of VBA code.
- Array: An array is a group of variables. In VBA, you can refer to a specific array variable (element) using the array name and the element’s numeric index.
- Function and Sub: In Visual Basic for Applications, a function (Function) can return a value, while a procedure (Sub) cannot.
- Application Object: At the top of the object hierarchy is Excel itself. We call it the Application object. The Application object exposes many settings related to the Excel application itself.
- ActiveX Controls: Learn how to create ActiveX controls such as buttons, text boxes, combo boxes, and so on.
- Userform: In this chapter, you will learn how to create user forms in VBA.