My hope for this e-book is that you please share it with as many people as possible, and You can find more Microsoft Office training (including Excel, Word and. This book was set in Garamond by Box Twelve Communications. . MOAC represents the collaboration between Microsoft Learning and John In the Publish as PDF or XPS dialog box, ensure that the Save as type list. PDF Drive is your search engine for PDF files. Microsoft Excel Step by Step is designed for use as a learning and reference resource by home Microsoft .
|Language:||English, Spanish, German|
|Genre:||Children & Youth|
|ePub File Size:||28.33 MB|
|PDF File Size:||10.59 MB|
|Distribution:||Free* [*Sign up for free]|
In this introductory course to Excel, participants will explore Excel activities that go to use another file type, such as a PDF or Excel workbook. CFI's Excel Book is free and available for anyone to download as a pdf. Learn about the most important Excel shortcuts and functions to succeed in your career!. Excel is a program that you never quite finish learning about. I have used I have tried to arrange the book in a logical manner so you can.
This section is for users with no knowledge of Excel. Microsoft Excel is one of the most used software applications of all time. Hundreds of millions of people around the world use Microsoft Excel. You can use Excel to enter all sorts of data and perform financial, mathematical or statistical calculations. This chapter gives an overview of some very important range operations.
Count cells that contain specific text. Count cells that contain text. Count cells that do not contain. Count cells that do not contain errors. Count cells that do not contain many strings. Count cells that end with.
Count dates by day of week. Count dates in given year. Count if row meets internal criteria. Count if row meets multiple internal criteria. Count if two criteria match. Count items in list.
Count matches between two columns. Count multiple criteria with NOT logic. Count numbers that begin with. Count numbers third digit equals 3. Count occurrences in entire workbook. Count paired items in listed combinations.
Count rows that contain specific values. Count rows with at least n matching values. Count rows with multiple OR criteria.
Count sold and remaining. Count total matches in two ranges. Count unique numeric values in a range. Count unique numeric values with criteria. Count unique text values in a range. Count unique text values with criteria. Count visible rows in a filtered list.
Count visible rows only with criteria. Running count of occurrence in list.
Summary count of non-blank categories. Summary count with percentage breakdown. Calculate running total. Subtotal by color. Subtotal by invoice number. Subtotal invoices by age. Sum 2d range with multiple criteria. Sum bottom n values. Sum bottom n values with criteria. Sum by group. Sum by month. Sum by month ignore year. Sum by month in columns. Sum by week. Sum by week number. Sum by weekday. Sum columns based on adjacent criteria.
Sum entire column. Sum every n rows. Sum every nth column. Sum formulas only. Sum if begins with. Sum if between. Sum if by year. Sum if cell contains text in another cell. Sum if cells are equal to. Sum if cells are not equal to. Sum if cells contain an asterisk.
Sum if cells contain both x and y. Sum if cells contain either x or y. Sum if cells contain specific text.
Sum if date is between. Sum if date is greater than. Sum if ends with. Sum if equal to either x or y. Sum if equal to one of many things. Sum if greater than. Sum if less than. Sum if multiple criteria. Sum if not blank. Sum if one criteria multiple columns. Sum last n columns. Sum matching columns. Sum sales in last 30 days by ID. Sum through n months. Sum top n values. Sum top n values with criteria. Sum visible rows in a filtered list. Average and ignore errors.
Average by month. Average if criteria not blank. Average last 5 values. Average last 5 values in columns. Average numbers. Average numbers ignore zero. Average response time per month. Average the last 3 numeric values. Average top 3 scores. Average with multiple criteria. Weighted average. First in, last out times. Large with criteria. Larger of two values. Max of every nth column.
Max value ignore all errors. Maximum if multiple criteria. Maximum value. Maximum value if. Minimum if multiple criteria. Minimum value. Minimum value if. MAX IF. Smaller of two values. Approximate match with multiple criteria. Case sensitive match. Count missing values. Extract all matches with helper column.
Extract all partial matches.
Extract multiple matches into separate columns. Extract multiple matches into separate rows. Find closest match. Find longest string in column. Find longest string with criteria. Find lowest n values. Find missing values. First match in range with wildcard. Get address of lookup result. Get cell content at given row and column. Get first match cell contains. Get first non-blank value in a list. Get first text value in a list. Get information corresponding to max value.
Get last match. Get last match cell contains. Get location of value in 2D array. Get nth match. Index and match on multiple columns. Lookup and sum column. Lookup entire column. Lookup entire row. Lookup last file version. Lookup latest price. Lookup lowest value.
Lookup up cost for product or service. Lookup value between two numbers. Lookup with variable sheet name. Match first does not begin with. Match first error. Match first occurrence does not contain.
Match next highest value. Max if criteria match. Multi-criteria lookup and transpose. Multiple matches in comma separated list. Partial match against numbers with wildcard.
Position of first partial match. Position of max value in list. Force negative numbers to zero. If cell begins with x, y, or z. If cell contains.
If cell equals. If cell is blank. If cell is greater than. If cell is not blank. If cell is this OR that. IF OR.
If else. If NOT this or that. If this AND that. IF AND. IF with boolean logic. IF with wildcards. Invoice status with nested if. Nested IF function example. Only calculate if not blank. Return blank if. Tax rate calculation with fixed base. Win loss points calculation. Categorize text with keywords. Group arbitrary text values.
Group numbers at uneven intervals. Group times into 3 hour buckets. Group times into unequal buckets. If cell contains one of many things. Map inputs to arbitrary values. Map text to numbers.
Running count group by n size. Highlight 3 smallest values with criteria. Conditional formatting based on another cell. Conditional formatting column is blank. Conditional formatting date past due. Conditional formatting dates overlap. Conditional formatting highlight target percentage. Find duplicate values in two columns. Gantt chart. Gantt chart by week. Gantt chart with weekends. Highlight approximate match lookup conditional formatting.
Highlight blank cells. Highlight bottom values. Highlight cells that begin with. Highlight cells that contain. Highlight cells that contain one of many. Highlight cells that end with. Highlight cells that equal. Highlight column differences. Highlight data by quartile.
Highlight dates between. Highlight dates greater than. Highlight dates in same month and year. Highlight dates in the next N days. Highlight dates that are weekends.
Highlight duplicate columns. Highlight duplicate rows. Highlight duplicate values. Highlight entire rows. Highlight every other row. Highlight integers only. Highlight missing values. Highlight multiples of specific value. Highlight numbers that include symbols. Highlight row and column intersection exact match.
OR AND. Highlight rows that contain. Highlight rows with blank cells. Highlight rows with dates between. Highlight top values. Highlight unique values. Highlight unprotected cells. Highlight values between. Highlight values greater than. Highlight values not between X and Y. Shade alternating groups of n rows. Data validation allow numbers only. Data validation allow text only. Data validation allow uppercase only.
Data validation allow weekday only. Data validation date in next 30 days. Data validation date in specific year. Data validation don't exceed total. Data validation exists in list. Data validation must begin with. Data validation must contain specific text. Data validation must not contain. Data validation must not exist in list. Data validation no punctuation.
Data validation number multiple Data validation only dates between. Data validation require unique number. Data validation specific characters only.
Data validation unique values only. Data validation whole percentage only. Data validation with conditional list. Rank function example. Rank if formula. Rank race results. Rank with ordinal suffix. Rank without ties. Get decimal part of a number. Get integer part of a number.
Get number at place value. Round a number. Round a number down. Round a number down to nearest multiple. Round a number to n significant digits. Round a number to nearest multiple. Round a number up.
Round a number up to nearest multiple. Round a number up to next half. Round a price to end in. Round by bundle size. Round time to nearest 15 minutes. Round to nearest Round to nearest 5. Add business days to date. Add days exclude certain days of week. Add days to date. Add decimal hours to time. Add decimal minutes to time. Add months to date. Add workdays no weekends. Add workdays to date custom weekends. Add years to date. Assign points based on late time. Basic overtime calculation formula.
Basic timesheet formula with breaks. Calculate date overlap in days. Calculate days remaining. Calculate expiration date. Calculate number of hours between two times. IF MOD. Calculate retirement date. Calculate years between dates. Convert date string to date time.
Convert date to Julian format. Convert date to month and year. Convert date to text. Convert decimal hours to Excel time. Convert decimal minutes to Excel time.
Convert decimal seconds to Excel time. Convert Excel time to decimal hours. Convert Excel time to decimal minutes.
Convert Excel time to decimal seconds. Convert Excel time to Unix time. Convert text timestamp into time. Convert text to date. Convert time to time zone.
Convert Unix time stamp to Excel date. Count birthdays by month. Count dates in current month. Count day of week between dates. Count holidays between two dates. Count times in a specific range. Create date range from two dates. Custom weekday abbreviation.
Date is same month. Date is same month and year. Date is workday. Days in month. Days until expiration date. Display the current date. Display the current date and time. Dynamic calendar grid. Dynamic date list.
Extract date from a date and time. Extract time from a date and time. Get age from birthday. Get date from day number. Get day from date. Get day name from date. Get days before a date. Get days between dates. Get days between dates ignoring years.
Get days, hours, and minutes between dates. Get days, months, and years between dates. Get first day of month. Get first day of previous month. Get first Monday before any date. Get fiscal quarter from date. Get fiscal year from date. Get last day of month. Get last weekday in month.
Get last working day in month. Get month from date. Get month name from date. Get months between dates. Get most recent day of week. Everything from printing your Excel sheet to completing basic functions is covered here in an easy-to-read format.
The Basic Excel Functions page : Even advanced users need a refresher in the simpler functions in Excel, and this is a solid resource to have handy. For instance, you might need to find out what the Find and Replace function is. A quick search through this article gets you to the answer. These resources provide a refresher on mathematics with plenty of real-world use cases: Excel Advanced Formulas and Functions : This course has been viewed thousands of times, and it takes around six hours to complete, with videos, downloadable materials, and a transcript.
The exercise files improve the way you learn, since you have a chance to practice during the course. The Excel Math Functions : This list is a good starting point for anyone interested in learning even the most basic of functions. It goes deeper into the more advanced usage, so you get a whole range of what to expect when making your own formulas in Excel.
Some other functions you might find interesting include Len, Proper, Trim, and Rounding. The financial formulas section is particularly intriguing for those who would like to work in accounting or banking. This one has had over 9, students enrolled, with courses for mastering the top 25 most advanced math formulas in Excel.
It teaches real world examples for the formulas so you can see how to utilize them in the future. Dozens of PDF quizzes, practice files, and slides are available for download, and a beautifully structured course outline is there to walk you through some of the more complicated techniques.
Excel as a Financial Calculator : this article is particularly useful since not everyone is at work with a real financial calculator. So it makes sense to learn these functions in many fields. We like the key that compares some of the more advanced functions in Excel to buttons on a financial calculator.
This allows you to seamlessly transition from a calculator to the Excel spreadsheet. Excel Exposure keeps its articles on the shorter side, which is good if you want to absorb the information in small helpings. Advanced time and math calculations : Helpful guides primarily delivered in video format. All of the calculations are done through Excel, allowing you to see the visuals and practice them on your own time. You can use it for databases too.
Getting to grips with databases will help you to understand how to use some of the more complex functionality in Excel: Microsoft Office Tutorial on Importing Data into Excel and Creating a Data Model : One of the main reasons people use Excel is for data mining and creating databases in general.
This expands on the essentials of data insertion and organizing that data afterward. For example, you could start with learning how to import data using copy and paste, then go straight to creating a relationship between imported data. Building a database in Excel with a table or list : Wonder How To provides a few simple video tutorials for those interested in this topic.
Using Excel as your database : A nice tutorial from Chandoo. It includes animated GIFs to complement the text. This speeds up the process of learning, since the images are zoomed in, yet still moving, for a more engaging guide. It explains the code module too, which is an interesting take on the entire databasing process.
Be warned that some of the images are from older Excel versions. Some of the topics include working with formulas, using sliders to facilitate table filtering, and using data validation to restrict data entries. All of these topics come with some downloadable information and practice forms.
Best Excel Tutorial : Explores database functions with the most common function you would ever use when constructing or mining a database. For example, some of the headers include functions like Dmin, Dproduct, Dvarp, and Dvar. How to Create a Database in Excel : Begins with a database overview and moves onto other more advanced topics like filtering data and completing database formatting.
The formatting section is one of the most important parts, since the whole point of a database is for use in the real world. Microsoft Excel Functions To really understand how to use Excel, you need to be able to write functions. The tutorial is all shown on the video.
This is a great resource to bookmark, since the majority of functions used in the real world are outlined here. These are the true basics, so it might seem a little rudimentary to some.
It starts with Count and Sum, where you can click on that link and see the formulas that relate. The website has plenty of other categories for statistics, financials, and more. It serves as a quick reference for anyone who needs it.