Some time ago I wrote an Excel Tutorial about getting a list of file names from a folder in Excel. In that tutorial, I showed various ways to get the list of file names from a folder (using the FILE function and VBA). However, the limitation of that method is that it can only get the file names from a folder, and not from the sub-folders within the main folder....
Open Excel’s Options menu to customize the ribbon. We can add in our custom tab or group. Now you will see an extra tab in your ribbon with all the new groups of actions you added.
In this tutorial, I will show you how to calculate the standard deviation in Excel (using simple formulas) But before getting into, let me quickly give you a brief overview of what standard deviation is and how it’s used. What is Standard Deviation? A standard deviation value would tell you how much the data set deviates from the mean of the data set. For example, suppose you have a group of 50 people, and you are recording their weight (in kgs)....
In this example let’s concatenate column 1 and have the list separated by commas. Using the CONCATENATE function we could use the formula: Using the & we could use the formula: Both not very practical as the range gets bigger. A better solution is to create a user defined function in VBA. Something like this will do the trick. With this VBA code, the formula needed becomes this. Much more simple....
It’s quite simple in Excel to insert a line and then customize and position it. In fact, you’ll be surprised how many options you get when you need to draw a line in Excel. You can easily draw a line to connect two boxes (to show the flow) or add a line in an Excel chart to highlight some specific data point or the trend. Excel also allows you to use your cursor or touch screen option to manually draw a line or create other shapes....
In this tutorial, I will cover all that you need to know about how to reference cells and ranges in Excel. Apart from the basic referencing on the same sheet, the major part of this tutorial would be about how to reference another sheet or workbook in Excel. While there is not much difference in how it works, when you reference another sheet in the same file or reference a completely separate Excel file, the format of that reference changes a bit....
You may want to quickly see what the total sum is or you may want is as a formula in a separate cell. This Excel tutorial will cover a couple of quick and fast methods to sum a column in Excel. Select and Get the SUM of the Column in Status Bar Excel has a status bar (at the bottom right of the Excel screen) which displays some useful statistics about the selected data, such as Average, Count, and SUM....
In case you prefer reading a tutorial over watching a video, below is a detailed written tutorial on unhiding sheets in Excel. When you work with data that is spread across multiple worksheets in Excel, you may want to hide a few worksheets. This could be to avoid the clutter or to not show some data to your client/manager by hiding some worksheets and only keeping the useful ones visible....
Get the Last Number in a List in Excel using Excel VLOOKUP Function The largest positive number that you can use in Excel is 9.99999999999999E+307 (I tried this in Excel 2010). That’s huge!! I don’t think you would ever need any calculation involving such a large number. And that is exactly what we can use get the last number in a list. Suppose you have a dataset (in A1:A14) as shown below and you want to get the last number in the list....
For example, if you appear for exams and all the exams carry a similar weight, then the average of your total marks would also be the weighted average of your scores. However, in real life, this is hardly the case. Some tasks are always more important than the others. Some exams are more important than the others. And that’s where Weighted Average comes into the picture. Here is the textbook definition of Weighted Average:...
Counting the total number of sheets in the active workbook or any other workbook on your system is an example of such a task. In this tutorial, I’ll show you some simple VBA code that you can use to count the total number of sheets in an Excel workbook. Count All Sheets in the Workbook There are multiple ways I can use VBA to give me the total count of sheets in a workbook....
When to use Excel DATEVALUE Function Excel DATEVALUE function is best suited for situations when a date is stored as text. This function converts the date from text format to a serial number that Excel recognizes as a date. What it Returns It returns a serial number for a date. For example, if we use the function DATEVALUE(“1/1/2016”), it would return 42370 which is the serial number for 01-01-2016....
When to use Excel WORKDAY.INTL Function Excel WORKDAY.INTL function can be used when you want to get the date after a given number of working days. For example, if I start a project today and it will take 20 working days to complete, then I can use WORKDAY function to get the completion date. This function is best used when you want to calculate the invoice due date, project due date, delivery date, etc....
This is especially helpful while planning a project where you have a tentative idea of how much time a task would take and want to know when it will be completed (by adding the specified number of weeks the project is going to take to the start date of the project/task) Doing this in Excel confuses many people because Excel has a specific way of dealing with date and time values....
It can be used while getting a user to fill a form, or while creating interactive Excel dashboards. Drop-down lists are quite common on websites/apps and are very intuitive for the user. Watch Video – Creating a Drop Down List in Excel In this tutorial, you’ll learn how to create a drop down list in Excel (it takes only a few seconds to do this) along with all the awesome stuff you can do with it....
You can easily group worksheets in Excel so that you can make the changes at one go (instead of going to each sheet one by one and making the changes). To give you an example, suppose you have a workbook that has multiple worksheets (one for each month). Now, in this data, you want to add a new item (Laptop) in cell A7 in all these sheets. With the ability to group worksheets in Excel, I can group all the sheets first and then I need to make this entry once and it will automatically be added to all the sheets....
Now you can’t use the XML file directly, as it’s not meant to be read by humans (but machines). In such a case, it would help to know how to convert the XML file to Excel so you can easily work with the data and analyze it. In this tutorial, I will show you two really simple ways to import an XML file into Excel using Power Query. What is an XML File?...
In this example, we will move the Data and Sort & Filter section. From the Excel Options menu, we can customize the location of all the tabs and sections within tabs. We can’t move individual items within a section though 🙁 Hurray, the Data tab and subsections have moved.
If you have a lot of hyperlinks and these are scattered in the worksheet, it could be a herculean task to find these. Doing it manually can take you forever. In this tutorial, you’ll learn how to quickly: Find all the hyperlinks in Excel. Find all the hyperlinks linked to a specific text. Delete All Hyperlinks at One Go. Find All Hyperlinks in Excel Suppose you have a dataset as shown below:...
When to use Excel SUMPRODUCT Function SUMPRODUCT function can be used when you want to first multiply two or more sets to arrays and then get its sum. What it Returns It returns a number that represents the sum of the product of two or more arrays. Syntax =SUMPRODUCT(array1, [array2], [array3], …) Input Arguments array1 – The first array argument whose components you want to multiply and then add. [array 2] – (optional) Second array arguments whose components you want to multiply and then add....
