Syntax CHOOSE(Index, Value1, Value2, Value3,…) Index (required) – This is the index value for the item you want to return from the list. The index number refers to the order of the list. 1 will return the 1st item in the list, 2 will return the 2nd item in the list and so on.Value1 (required) – This is the first item in the list.Value2, Value3 etc… (optional) – These are the other items in the list....
Syntax DATEVALUE(Text) Text (required) – This is the date stored as text you would like to convert to a serial number. Example In this example we convert a few different text dates to the serial number.
A User Defined Function To Check If A Sheet Exists Within The Current Workbook This is a simple VBA function that will return true if the current workbook contains a sheet with the exact name passed through the function and returns false otherwise. This function is not case sensitive so Sheet1 and SHEET1 are considered to be the same (sheet names in Excel are not case sensitive). Here the VBA is formatted as a user defined function....
Worry not. Today’s prescription includes an article and a video on how to create dynamic chart titles in excel. Linking a Cell Value to the Chart Title Suppose you have the data as shown below and you have created a chart using it. If you want to change the chart title, you need to manually change it by typing the text in the box. Since the chart title is static, you would have to change it again and again whenever your data is refreshed/updated....
Something as shown below: The idea is to display the value right next to the Excel scroll bar, rather than having it somewhere in the back-end. This way the user can play with the data more effectively. Try it yourself.. Download the file Enjoyed this tutorial? You might also like these: Using Scroll Bar in Dynamic Charts. Creating Drop Down list with Search Suggestion in Excel. Creating Dynamic Filter in Excel....
Enable the summary statistics. Wow, the average salary for the Galactic Empire is $5,890,400, sign me up!
How can you get the Nth item from that list? One solution would be to use text to column and actually separate out each list into different columns then use an INDEX function on those column. The problem with this is in the process you’ve destroyed the original list and it’s now across many different columns. A simpler solution might be to create a user defined function in VBA for this....
In our example ISBLANK(Range) creates the following array. MATCH(FALSE,{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE},0) then looks for the first exact match to a FALSE value within this array. This returns the value 4 since the first FALSE value is in the 4th position and this is our first non blank cell is in the range.
Sometimes you may want to lock cells in Excel so that other people can’t make changes to it. It could be to avoid tampering of critical data or prevent people from making changes in the formulas. How to Lock Cells in Excel Before we learn how to lock cells in Excel, you need to understand how it works on a conceptual level. All cells in Excel are locked by default....
This error is caused when you improperly implement the concept of spilling an array in your Excel formula. This post explores this spilling concept and discusses the fixes for errors associated with spilling formulas. What Does Spill Mean in Excel? Excel was introduced to spilling arrays with the release of Excel 365 in January 2020. The basics of spilling are that you enter a single formula in a cell and the results are returned in multiple cells!...
Syntax XOR(Expression1, Expression2, Expression3, … ) Expression1 (required) – This is an expression that can evaluate to either TRUE or FALSE.Expression2, Expression3 etc… (optional) – These are additional expressions you want to test. Example In this example we test two expressions. As seen, the result is only true when exactly one of the expressions evaluated is true.
If you’re going to be using VBA or recording macros in Excel then you’re going to want to add the Developer tab to your ribbon. Excel’s Developer tab is hidden by default and you will need to go into the options to enable it. The Developer tab will allow easy access to various features not otherwise accessible. Open the visual basic editor (VBE).Record macros.Run your recorded macros and VBA modules....
Given that Pivot Tables are so dynamic and the data in the backend can change often, you need to know the right way to use conditional formatting in a pivot table in Excel. The Wrong Way to Apply Conditional Formatting to a Pivot Table Let’s first look at the regular way of applying conditional formatting in a pivot table. Suppose you have a pivot table as shown below: In the above dataset, the date is in the rows and we have store sales data in columns....
Something as shown below: There is a dual functionality to this – you can select a country’s name from the drop-down list, or you can manually enter the data in the search box, and it will show you all the matching records. For example, when you type “I” it gives you all the country names with the alphabet I in it. Download Example File and Follow Along Watch Video – Creating a Dynamic Excel Filter Search Box...
What it Returns It returns a serial number that represents a specific date in Excel. For example, if it returns 42370, it represents the date: 01 January 2016. Syntax =DATE(year, month, day) Input Arguments year – the year to be used for the date.month – the month to be used for the date.day – the day to be used for the date. Additional Notes (boring stuff.. but important to know) The result displayed in the cell would depend on the formatting of that cell....
When working with large data sets, if you scroll down or to the right of the worksheet you would lose track of the row/column headings. In such situations, you can use the Excel Freeze Panes feature to freeze the rows or columns in your dataset – so that the headers always visible no matter where you scroll in your data. Accessing Excel Freeze Panes Options To access Excel Freeze Panes options:...
When to use Excel NETWORKDAYS Function Excel NETWORKDAYS function can be used when you want to get the number of working days between two given dates. It does not count the weekends between the specified dates (by default the weekend is Saturday and Sunday). It can also exclude any specified holidays. A typical situation where you can use this function is to calculate the benefits accrued to employees overtime....
In this tutorial, you’ll learn how to use the For Next Loop in Excel VBA. If you’re interested in learning VBA the easy way, check out my Online Excel VBA Training. Using FOR NEXT Loop in Excel VBA ‘For Next’ Loop works by running the loop the specified number of times. For example, if I ask you to add the integers from 1 to 10 manually, you would add the first two numbers, then add the third number to the result, then add the fourth number to the result, as so on....
When I get a data file from a client/colleague or I download it from a database, I do some basic checks on the data. I do this to make sure there are no missing data points, errors or duplicates that may lead to issues later. One such check is to find and highlight blank cells in Excel. There are many reasons that can result in blank cells in a dataset:...
This is because Excel has a specific way of storing date and time values in Excel. While a full day is stored as a whole number in Excel, time values are stored as fractions/decimals. For example, 44927 represents the date 01 January 2023, and 44927.25 represents 01 January 2023 06:00 AM. Here, 0.25 is the fraction that represents the time. Since a day has 24 hours, 0.25 part of the day would mean 6 hours (24*0....
