پیشنهاد فرادرس

آموزش نکات Microsoft Excel - سطح 2 - متوسط/پیشرفته

دسته بندی ها: آموزش اکسل ، آموزش های یودمی (Udemy) ، آموزش آفیس

در این دوره با نرم افزار Excel بیشتر آشنا می شوید.

سرفصل:

  • معرفی دوره
  • پروژه1 - ایجاد یک صفحه ورود اطلاعات برای پرکردن چندین قالب
  • برنامه ریزی
  • (سفارشی) تاریخ و زمان قالب بندی
  • محاسبات ساده با زمان
  • MROUND
  • FLOOR
  • CEILING
  • افزودن تایم
  • ایجاد یک قالب از یک تصویر
  • وارد کردن یک الگو از یک فایل اکسل موجود
  • HOUR
  • MINUTE
  • تبديل زمان به عدد اعشاری
  • توابع
  • تست منطقی ساده و تست منطقی ماتریس
  • ساخت رشته های پیچیده ی متن با یک فرمول
  • پروژه 2 - ایجاد پایگاه داده با اکسل
  • VLOOKUPs ساده
  • دریافت برخی از داده ها و تقسیم آن!
  • استفاده از اعتبار سنجی داده ها برای دریافت ورودی مناسب
  • وارد کردن داده ها از یک فایل متنی
  • وارد کردن داده ها از یک فایل ورد
  • کشیدن داده ها از منابع مختلف
  • و غیره
به این نوشته امتیاز دهید 1 2 3 4 5 بدون امتیاز
Microsoft Excel Essentials: Level 2 - Intermediate/Advanced Publisher:Udemy Author:Alan Jarvis Duration:07:40:26

Excel: From User To Super-User - Use Excel Like The Pro's, Easily, And Step-By-Step - Excel 2010 Excel 2013 Excel 2016

Welcome To The Course!
1 Lecture
01:58
Welcome, And Thank You For Choosing This Course
Preview
01:58

Project 1 - Creating A Data Entry Screen To Populate Multiple Templates
21 Lectures
01:59:19
In this lecture you will learn the importance of proving your concept.Before you spend hours creating the layout for your spreadsheet, adding graphics, word art, formatting and page layouts, you probably want to see if what you plan to do is even possible! The core concept of this project is:Can I enter data in one place, and have it populate in other places automatically.
Proof Of Concept
Preview
04:49
In this lecture we will look at planning ahead.
As we will be building multiple sheets that will populate automatically, it's useful to have an idea of which elements:
Must have data entered (the dynamic fields...the ones that change)
Are constant (never change and can be permanently "hard coded" in the templates we will populate)
Are calculations (we can use formulas to calculate the entry)
As we want to make this as time efficient and as easy to use as possible, knowing these three things up front will enable us to enter only the bare minimum information, and then let Excel do all the heavy lifting.
Planning Ahead
Preview
02:13
Now we have worked out all the elements that will need an entry, we can set up our simple data entry screen!
Creating Our Data Entry Screen
Preview
04:08
Time to start entering some data!In this lecture you will learn how to change Excels default formatting for dates and times into something more useful, including a neat trick to instantly tell which day of the week any date in the past or future falls on!
(Custom) Formatting Dates And Times
Preview
06:02
Okay, now we have some data entered, it's time to start building the calculations that we will use to populate our templates. In this lecture we will cover the straightforward calculations on time.
Simple Calculations With Time
Preview
02:35
We covered the easy stuff in the last lesson, now we're going to get to the more powerful stuff!
We'll take a quick look at the ROUND function, and learn why it isn't useful if you are working with time.
Then you will master these much more useful functions, which will allow you to work in defined increments of time:
MROUND
FLOOR
CEILING
Powerful stuff!
More (Useful) Calculations With Time
Preview
08:06
In this lecture you will learn how Excel deals with dates and times...and it's not how you think!
It's About Time (And Dates!)
Preview
08:28
In this lecture, you will master the skill of adding time with Excel.
This may seem simple enough, but I have seen many people give up on this and declare that it can't be done.
It can, and once you learn the trick to doing this, you will ALWAYS arrive at the answer you expect!
Adding With Time
03:39
In this lecture you will learn of the three ways to create a template in Excel.
By the end of this lecture you will know the fastest way (to cheat !) and create an exact replica of a template someone else has created in next to no time!
I wish someone had taught me this years ago!
Creating A Template From An Image
11:34
In this lecture, you will learn the fastest way to get a template into Excel...by importing one from a different workbook :)
Importing A Template From An Existing Excel File
02:21
Some people report time as time, like 09:30, and some report it as a decimal, like 9.5 (meaning nine and a half hours...or nine hours and thirty minutes!). In this lecture you will learn how to easily work with both formats, and understand how to calculate one from the other.
We will also look at how to strip out just the hours from a time, and how to extract just the minutes from a time using the following functions:
HOUR
MINUTE
We won't be using them for our templates, but at some point you may need to know how it's done :)
Converting Time To A Decimal
05:41
In this lecture we will start to develop our data entry screen with the fields we need to populate in our second template.
Nice and easy.
A Little Bit Of Simple Data Entry
03:11
In this lecture we use boolean logic to conditionally format cells.
This sounds more difficult than it is, and by the end of this lecture you will be able to easily build a simple (boolean) logic test, and use it to determine which formatting is applied to certain cells using Excel's built-in conditional formatting function.
Just so we're clear, the result of a boolean logic test can only have one of two answers: TRUE or FALSE. Simple!
Simple Conditional Formatting For A Cleaner View
04:42
In this lecture we will learn how to calculate our travel time, which we will then use to calculate our time away from home in our expenses template, using some other data we have already entered.
Calculating Time Out Of House Using Travel Time
04:47
In this lesson you will learn all about logical testing!
This is particularly useful if you want to return a certain value based on a value in a different cell.
You will learn how to do simple logical testing (if this, then that, if not, something else), and also how to nest logical tests so you can test for multiple results...all within the same formula!
Functions in this lecture:
IF
Simple Logical Testing And Nested Logical Testing
08:28
This is a big lecture, but stick with it...it will be worth it!
In this lesson you will master building perfectly formatted complex text strings. What does that mean? Well, you will not just learn how to join 2 words together with a formula, you'll learn how to create entire sentences (or paragraphs!). What's more, you will learn a trick to change the formatting on a specific area within the string.
This is particularly useful is you need to regularly provide commentary with a report. Rather than writing the whole thing from scratch every time, you can build the shell once, and have all the bits that change update themselves as needed automatically! Very efficient :)
In this lesson you will learn about the functions:
CONCATENATE
TEXT
Building Complex Text Strings With A Formula
12:10
In this lesson you will learn about the Form Control: Checkbox.
Checkboxes are those little boxes that you tick (check) or un-tick (un-check). We will also cover a quick cheat so we can replicate the template exactly!
You will learn how to add them, edit them, also learn some neat ways to get them all in the right place precisely without having to drag and drop (very useful).
Features used in this lecture:
FORM CONTROL: CHECKBOX
CELL LINKING
A Tick Box Exercise Of Sorts
07:11
Another big lecture, but you will learn how to combine several workbook elements to produce something really clever!
We cover a lot of ground in here...you will learn how to link a checkbox to a cell so it either checks or un-checks automatically depending on the value in the cell. Then, to ensure only one box from a range can be selected, we're going to do something really clever...we will build a limited list so the user can only select one of the three choices they should have. Then using that, we will build a logical test to determine which of the checkboxes should be ticked. Phew!
Features and functions used in this lecture:
CONDITIONAL FORMATTING: LIMIT TO LIST
DROP DOWN BOXES
IF (LOGICAL TESTING)
Auto-Populating Check Boxes
11:48
Time for an exercise!
I have deliberately missed out one element in this project, so you can have a simple practice exercise.
The element I have missed out is the date that we submit the expenses. So your mission, should you choose to accept it, is to:
Add an input field to the data entry screen, and
Have that entry automatically fill in the date box at the bottom of the expenses form, and
Format that date in a particular format
This lecture will self-destruct in 1 minute and 40 seconds :)
PRACTICE EXERCISE 1 - Time To Add A New Entry
01:40
In this lesson you will learn how to define a worksheet area, so the user can only see the area of the worksheet you want them to see, and even more usefully, learn how to lock your worksheet so they can only enter data where you want them to...so they can't break all your formulas!
Features and functions in this lesson:
CELL PROTECTION
WORKSHEET PROTECTION
LIMITING WHAT THE USERS CAN SEE, AND CHANGE
Defining A Working Area And Protecting Your Work
04:40
Time for a little more practice!
Using the skills you have mastered so far, I want you to finalise the Time Sheet.
Tasks:
Make the cells behind the image white
Make the image the working area (so that's all you can see)
Lock all the cells except the three indicated in the video
PRACTICE EXERCISE 2 - Set Up A Working Area And Limit User Entry
01:06

Project 2 - Building A Database With Excel
26 Lectures
02:15:22
Okay, this is where it starts!
Vlook-ups are frequently cited as an advanced skill in Excel, and in this short lecture you will learn what they are, and how to use them simply, and effectively.
Functions covered in this lecture:
VLOOKUP
Simple VLOOKUPs
04:16
Right, if you want to create a database in Excel, you're going to need some data!
In this lesson, you will learn how to use an existing data table to build our project. We'll also cover a nifty trick for splitting your joined up data in to separate columns!
You will also learn how the Paste Special Transpose works!
Functions used in this lesson:
DATA: TEXT TO COLUMNS (to split out a column of data)
PASTE SPECIAL: TRANSPOSE
Step1: Get Some Data In And Split It!
03:54
In this lesson you will learn how to limit the input used in the VLOOKUP, so you can only get a value that DOES appear in your table.
You will also learn how to add helpful messages to the cell (to help the user), and how to add an error alert (in case they still get it wrong!).
Functions used in this lesson:
DATA VALIDATION: LIMIT TO LIST
Using Data Validation To Get The Right Input
03:47
In this lecture you will learn how to build a simple database using the single data table we have.
As a useful aside, we will create these VLOOKUPs in a block...this is a really fast way of entering a bunch of complex formulas en-masse.
All we do then, is drag them to where we want them!
Let's Build Our Database!
06:12
MORE DATA...that's what we need!
In this lecture you will learn how to import another data table from a text file (which you can download).
Importing Data From A Text File
02:01
EVEN MORE DATA!
In this lecture you will learn how to import data from a simple old Word file.
SPECIAL NOTE TO 2016 USERS:The CTRL & V works differently in 2016 (it doesn't paste as it does in the lecture!). Instead, use Paste Special and choose the Unicode Text option...then you're good to go :)
Importing Data From A Word File
02:37
In this lecture you will learn how to pull from multiple data sources at the same time!
It may sound complicated, but as you will see, it's really easy!
Pulling Data From Multiple Sources
03:33
So now you've mastered pulling data from multiple sources, you can now learn how to pull a VLOOKUP value from another VLOOKUP value you already looked up!
Stay with me...this is really easy to do :)
Using OTHER Look-Ups To Look Up!
05:25
In the last lesson we looked at using another value returned from a VLOOKUP as a look up value, and in this lesson you will learn how to cut out that intermediary step and just do the job in one formula.
LOOKUP From A LOOKUP With No Intermediary Step
02:19
This is just a short lecture to clarify where the lookup value has to be in your table array (hint: it's always the first column in the table, but not necessarily the first column in the sheet!
Data Arrays Don't Have To Start At A1
02:58
At some point when using LOOKUPS, you are going to encounter issues!
This is generally for one reason... data that looks the same, but actually isn't!
We'll cover of these issues, and their solutions in this lecture.
Some Common Reasons VLook-Ups Fail
06:02
There are a couple of shortcomings in the Excel VLOOKUP function. The first of which we discuss here...VLOOKUPs can only read data to the right of your looked-up value!
One Inherent Flaw In Vlook-Up
01:10
Okay, here's your very first POWER USER lecture in this section!
I've lost count of how many times people have said to me "I wish I could VLOOKUP to the left!".
Now, although this isn't possible using VLOOKUP, it is possible if we combine two of the other built in functions available to us. And in about 6 minutes and 44 seconds, you will know exactly how to do this (and know something your friends don't).
Functions used in this lesson:
MATCH: To find a match in a data table (honestly!)
INDEX: To return an intersecting value from a row and column in a data table.
POWER USER - A Breakdown Of Looking Up Backwards
06:44
In this lecture you will learn ANOTHER way to look up backwards, this time using VLOOKUP, but with another of Excel's built in functions incorporated...
Functions used in this lecture:
VLOOKUP
CHOOSE
POWER USER - The Other Way Of Looking Up Backwards
06:49
In this lecture we will apply what we learned in the last lecture in to our main project database (where we have a lot more data to play with).
Functions used in this lecture:
MATCH
INDEX
Backwards Look-Ups In Action
03:47
If you ever have to write a database that will be used by others, there's one thing you must remember...users don't always do what you want them to!
That's the subject of this lesson. At the end of this lecture, you will know how to trap and deal with inconsistent user entry.
Functions used in this lecture:
SUBSTITUTE
POWER USER - Dealing With Inconsistencies In User Entry
08:19
In this lecture you will learn how to use VLOOKUP using a partial match!
This is really useful if you don't have the full string you are looking for (like with car registrations!)
You will even learn how to concatenate without using the concatenate function!
Functions used in this lesson:
WILDCARDS
POWER USER - Fuzzy VLOOKUPs
03:33
Up to now, we've looked at looking up from a single value...but what if you wanted to look up from more than one value at the same time?
We will cover the two methods of doing this...the way most people do it, using a helper column, and then the way power users do it, using the dark art of array formulas!!!
Functions used in this lecture:
CONCATENATE
HELPER COLUMNS
POWER USER - VLOOKUPs With Multiple Inputs
09:39
In this lecture you will learn about array formulas!
Array formulas are slightly different from normal formulas inasmuch as you have to hit CTRL + SHIFT + ENTER to enter them. For this reason, they are sometimes called CSE formulas.
Make no mistake, these are very powerful formulas. Only about 1 in 100 people I have met even know these exist!
Functions used in this lecture:
ARRAY FORMULAS!!!!
INDEX
MATCH
POWER USER - Looking Up From Multiple Inputs Using An Array Formula
04:45
If this short lecture you will learn how to use VLOOKUPs brother function HLOOKUP!
Now you understand VLOOKUPs, you'll master this one instantly :)
Functions used in this lecture:
HLOOKUP
VLOOKUPs Brother...HLOOKUP
05:04
In this lecture you will learn the holy grail of look-ups: How to return EVERY match (not just the first one!).
Now we are getting into seriously powerful array formula territory, and very few people can do what you are about to learn.
Functions used in this lecture:
ARRAY FORMULAS
IF
SMALL
ROW
INDEX
POWER USER - The Holy Grail - How To Return Multiple Values From A Single LookUp
13:33
This lecture covers the two main reasons the multiple return formula fails.
Both are very simple to solve, but to save you the frustration...here they are :)
What To Look For When THAT Formula Didn't Work
04:05
This lecture can save you an awful lot of time fiddling around with changing you column argument in your lookups.
There are two clever ways to do this, and you will master them both by the end of this lecture.
Functions used in this lecture:
COLUMN
HELPER ROWS or COLUMNS
The Fastest Way To Modify Your Column Numbers
07:25
This was actually a question from a student, Anat (thank you, Anat!)...but it sits so well in this section, that's where it is! The question is so good, I can't believe it's never been asked before!
So, you've built a load of VLOOKUPS off a huge data set, and then someone either inserts a column, or changes the column order...all of your formulas fail because they're now looking in the wrong place! Not anymore, with this simple trick!
POWER USER - Vlook-Ups With Moving Columns
03:05
Okay, we've covered a lot of ground in this section, and we're now going to put in all together in our project.
Putting It All Together
09:52
In this lecture you will learn how to add an info field to state how many records were queried, and how many results provided a match...just like real databases do!
Functions used in this lecture:
CONCATENATE
COUNTA
COUNTIF
The Finishing Touch: How Many Records Did I Find?
04:28

Section 3: Named Ranges
8 Lectures
49:10
In this lecture you will learn how to create a named range, what it's for, and how to use it.
Functions used in this lecture:
NAME BOX
NAMED RANGES
A Simple Static Named Range Using A Single Cell
03:44
In this lecture you will learn how to make a range of cells a named range, and incorporate it in to a VLOOKUP.
Functions used in this lecture:
NAME BOX
NAMED RANGE
VLOOKUP
Creating A Named Range Using A Range Of Cells
02:53
In this lecture you will learn how to use row labels to automatically create a bunch of named ranges automatically.
You will also learn how to quickly recall them using the name box, and how to delete them using the name manager.
Functions used in this lecture:
NAMED RANGES
NAME BOX
DEFINE NAMES: CREATE FROM SELECTION
Using Row Labels To Name Multiple Ranges
02:34
In this lesson, you will learn to add multiple named ranges simultaneously using your existing row and column headings.
We'll also look at how to used your named ranges in formulas. and a really powerful trick to perform LOOKUPS without using a look up of any kind!
Functions used in this lecture:
NAMED RANGES
NAME BOX
DEFINE NAMES: CREATE FROM SELECTION
VLOOKUP
POWER USER - A Magic Trick Using Row And Column Labels
04:55
Okay, time for another power user lecture!
In this lecture, you will learn how to create a dynamic named range. That means that as your data expands, you won't have to go back and manually alter you static named range to compensate...it will all happen automatically!
Functions used in this lecture:
DYNAMIC NAMED RANGES
COUNTA
OFFSET
POWER USER - Dynamic Named Ranges
08:06
In this lesson, you will learn how to modify your dynamic named range formula to compensate for headings and/ot titles in your data.
POWER USER - What To Do With Dynamic Names Ranges With Titles
05:22
Okay, this is actually a question from a student, but it fits so nicely in this section, I've added it here!In this lecture, we will look at how to create a dynamic chart using the skills we've covered in this section so far...cool stuff!
3035 - POWER USER - Dynamic Charts
Preview
10:27
This is another student question, from Natalia, that covers what to do if your data goes left to right (not top to bottom, as in the last lecture)...enjoy!
3040 - Horizontal Dynamic Named Ranges For Charts
11:09

Project 4 - What Can I Have For Dinner?
15 Lectures
48:55
Okay, you may have looked at the title of this section and wondered "What use is THAT?", will this short lecture will provide some context and tell you what I use it for all the time!!!
Welcome to "What Can I Have For Dinner?" or...What Would I Use THAT for?
01:25
In this lecture you will learn one of the ways to insert a hyperlink to another sheet in the same workbook.
This is easily adaptable to link to a website, or a different document.
Functions used in this lecture:
HYPERLINK
Hyperlinking To A Different Sheet In The Same Workbook
03:32
In this lecture you will learn how to create a macro.
In its simplest form, a macro is a method of recording a set of keystrokes, which can then be replayed exactly the same way at a later time.
You will learn how to add the developer tab to your ribbon (if you don't already have it), take a look at the VBA (Visual Basic for Applications) window and see the "code" we just created, and finally take a look at one of the ways to "execute' our macro.
Functions used in this lecture:
MACROS
Creating Our First Macro
05:37
In this lecture you will learn of two other ways to call your macro into action.
One by selecting it from the "Macros" pop up, and, probably more powerfully, to assign your macro to a form control button.
You will then learn how to replicate that button on every sheet so you can execute your code from anywhere in the workbook.
Functions used in this lecture:
MACROS
MACRO POP-UP
FORM CONTROL: BUTTON
Assigning A Macro To A Button
04:13
In this lecture we will add a dynamic named range from our ingredients list, that we can then use in a drop-down list we can use for selection.
Creating A List For Our Dropdown Using A Dynamic Named Range
01:39
In this lecture you will learn how to use formulas in conditional formatting to show instantly any items that do not appear in the dynamic named range we just created. This way we can instantly see if any "new" ingredients need to be added.
You will also learn the steps I go through to build formulas for use in conditional formatting rules, and test them so you're sure they work!
Finally, how to add additional tests so you can copy the conditional formats across blank cells without them being affected.
Functions used in this lecture:
CONDITIONAL FORMATTING
MATCH
ISERROR
ISBLANK
Using A Conditional Format To Know When A Value Is Missing
04:34
In this short lecture, you will learn how to copy your working conditional formatting across multiple sheets.
Functions used in this lecture:
FORMAT PAINTER
Copying Conditional Formats And Creating Our Drop Downs
02:50
In this lecture we will cover the first of the elements of our complex formula to calculate if a particular ingredient appears in a particular recipe.
The formula is complex, and made up of some functions we have used before, and one we haven't: INDIRECT.
This is a particularly powerful function, as it allows you to use a string to reference a particular cell, or sheet and cell, or workbook and sheet and cell.
Functions used in this lecture:
INDIRECT
Building Our Formula...INDIRECT Function
02:55
In this lecture you will learn how to build strings to create sheet and cell references which can then be used by other formulas. Incredibly useful to master!
You will learn how to spot the syntax of sheet references, and how to combine different elements to produce a finished working "path" to use in your formulas. This is the next stage in building the formula we began in the previous lecture.
Functions used in this lecture:
MATCH
&
INDIRECT
Building Strings For Indirect Sheet And Cell References
07:16
In this lecture, you will finish the formula by ensuring it can only return a 1 or a 0 (it is in the recipe, or it's not).
You will do this by utilising the formula we have built so far, and wrapping a couple of other functions around it!
Functions used in this lecture:
&
INDIRECT
MATCH
ISERROR
IF
It's A One Or A Zero
02:35
In this lecture we will look at how we use our 1's and 0's to rank our likelihood of making each of the recipes with the ingredients we have.
Finally, you will learn how to apply a traffic light icon set to our results (very useful for dashboards!).
Functions used in this lecture:
SUM
COUNTA
CONDITIONAL FORMATTING: ICON SETS
Working The Percentages And Adding Traffic Lights
04:06
In this lecture you will learn about the hyperlink function.
We have already added hyperlinks to this project, but the hyperlink function has some...quirks...that make it incredibly frustrating to use.
This is one of those instances where the built in help function does not help much, and even an hour on Google wouldn't get you very far!
This lecture will ensure you are ready with a solution whenever you need to use this function!
Functions used in this lecture:
HYPERLINK FUNCTION
POWER USER - The HYPERLINK Function (And Problem)
03:16
This is the first of 3 challenges for you to complete using the skills you have learned in this section.
Your task is to use the hyperlink function and finish off the entries in column C.
Sounds easy, right?
Exercise 1 - Fill In The Blanks
00:59
This is the second practice exercise:
Decide on a formatting scheme you are happy with for the recipe tabs, and then record it into a macro, and then use that macro to "execute' all the same changes on each of the subsequent recipe sheets!
Exercise 2 - Pretty It Up With A Macro
01:45
This is the final practice exercise. And though it sounds like a simple VLOOKUP, the task is to use the skills you have learned in this section to build your data array (where you want to look) using the sheet names in column A.
Hint: you will need to need to build a string that looks like the data array, and then incorporate the INDIRECT function to make it work!
Exercise 3 - Create A VLOOKUP Using A Built String With INDIRECT
02:13

Project 5 - Using Excel For Gantt Charts...Timelines And Project Plans!
5 Lectures
30:51
There are two methods of creating Gantts in Excel: Using a worksheet, or using a built in chart.
In this first lesson, you will lay the groundwork for designing a Gantt chart in a worksheet using the data from the HR file we used earlier.
You will lean a way to automatically start your Gantt dates where they should be, and you will also learn a really nifty trick to make the project instantly scalable!
Functions used in this lesson:
Creating A Gantt Chart Using A Worksheet
07:39
In this lecture we will look at building the first part of our logical test to return a binary output (it's either a 1 or a 0). We will do this by returning the start date using a VLookup, and then wrapping the first logical test around it.Functions used in this lecture:VLOOKUPIFSimple!
Building The First Part Of Our Logical Test
04:21
In this lecture we will add the second part of our logical test...the part where we test the end date.To do this, we will need to know how to test multiple criteria in one logical test.Functions used in this lecture:AND
Multiple Logical Tests At Once Using AND
08:28
Now the formulas are in, we just need to apply a little bit of conditional formatting, and we're done!
Conditional Formatting...Where The Magic Happens!
05:32
In this section we will look at the other way of creating a Gantt chart in Excel, by using the built in charting tool.
Gantt Charts Using The Built In Charting Tools
04:51

Bonus Section - Just For Fun
1 Lecture
07:14
Just for fun, this is how I created the random made up list of car registration plates we used in section 2.
Functions used in this lecture:
RANDBETWEEN
VLOOKUP
CONCATENATE
NAMED RANGES
How I Created Randomly Generated License Plate Numbers!
07:14

Student Questions Answered!
9 Lectures
01:02:37
The first student question in level 2 from Jon. Jon asked:Hi Alan, I have a scenario and I'm wondering whether it's possible to create formula. I have three columns of data, in columns A, B and C have values, what I'm trying to work out if it's possible to use a formula to look in A (Calls),B (Text) and C (Data) to determine whether there is a value in these separate columns so in essence what I want it to do is to say do these columns have zeros or do they contain values, if they contain values return the header names of each column in a string in column D or if they all return zero leave blank. Hope this makes sense. Many thanks JonIn this video, I'll take you through the process of creating a complex formula step by step to do just this!
Calls Text Data - Or How To Return a Column Title If Value is >1
06:42
Okay, another excellent question from Jonathan...he asked:
Hi Alan, I am wondering if it's possible to use the formula that you created but instead of values there's text in each cell? Many thanks Jon.
And here is the answer!
Enjoy.
Alan
Calls Text Data 2 - This Time Using Text!
09:02
Okay, time for another Student question answered video!This questions was about extracting a mobile phone number (always 11 digits) from a cell...the problem being it can be ANYWHERE in that cell.
Extracting Phone Numbers From A Cell
02:39
Back in the database section, we looked at how to use the CHOOSE function to get a "backwards Vlookup". A couple of days ago, someone asked what the CHOOSE function really does. As that was too big a question to answer in the Q&A;, I thought a new lecture was in order!
Here, we will look at 4 different uses for the CHOOSE function, and see how it can be used for several very useful things...enjoy!
What Is The CHOOSE Function Really Used For?
14:47
I had a question this morning from one of our community regarding casing...specifically, how do you make the first letter of a word in a cell appear in uppercase. I know we cover text manipulation in Level 2, but I figured it may be useful to give you a quick heads up on the basics of text manipulation, so that's what I did!
In this new lecture, which you will find in the Student Questions Answered" section, we will look at Excel's built in functions to convert text to upper case, lower case and proper case. Then for good measure, we'll take a look at extracting parts of a cells text from the left, the right and the middle, and finally we'll look at how to build the complex formula to capitalise only the first letter of the text in a cell.
That's quite a lot packed into 6 and a half minutes!
Casing And Text Functions
06:35
Okay, time for another student question answered...and it's another great one!This question is from Ruan, and Ruan asks if it's possible to create a dynamic chart from a table, and have the chart update depending on which value is selected from a drop down list on a different sheet! I told you it was a good one :)Now this would be simple if you could use the INDIRECT function for a data series. We'd just build a dynamic string, and reference that, and hey presto, it's done. Unfortunately, Excel doesn't allow you to use the indirect function this way, so I had to put on my thinking cap and find a different solution!We cover a lot of ground in this 10 minutes, but luckily it's all stuff you've already learned...just put together in a different way!Anyway, enjoy, and as always let me know if you have any questions!Alan 
Dynamic Charting From A Drop Down
10:02
Another Student question...this one from Doug V.Doug has a big column of data with names, and next to it, dollar amounts.What we need to do is extract a unique list of values from the main list, and then add a sum for each of those unique values...so all the individual amounts against each entry for that name are summed up neatly against our smaller, unique entry list. Nice and easy (when you know how!).Enjoy!Alan
Extracting a Unique List, And Summing The Money!
Preview
02:25
I had a question from Michael today, technically it was a VBA coding question, but it could be solved with a formula. Here's the scenario:
We have a data table, and we want to summarise it based on 2 criteria...you're thinking, "Yeah, that's just a SUMIFS, Alan"...BUT the data set has the two criteria in different axes!!!
It was such an interesting question, that I added a lecture to talk you through it :)Update 24/12/17: As Shashi pointed out in the Q&A; forum, the "MOT" category doesn't work...this was down to a simple typo on my part...one sheet said M.O.T and the other said M.O,T. (notice the extra dot at the end!). I have corrected the files, so they will look ever so slightly different to those used in the lecture...
SUMIF With Dynamic Sum Range
05:55
Another great student question from Karen!
It's actually a 2 part question, the second part is about automation of creating PDF documents, and I'll cover that in Level 3, but this part most comfortably fits right here in Level 2!
What Karen wanted to know is, can you VLOOKUP, or something similar, to pull a picture?
Well, yes you can! And here's how...
Enjoy
Vlookups With Pictures!
04:30

Thank You - Your Special Bonus!
1 Lecture
00:00
Get your next great course, at an amazing discount only available to existing students!
Get Your Next Course Now!
5 pages

پیشنهاد فرادرس