I've set up a table with some team names that I want to use in a Data Validation list. The reason I formatted my list in an Excel table is because I want the range to dynamically update when I add or remove teams from the list.
Now, if you've ever tried to reference an Excel Table as your Data Validation lists... then you probably got the error: "The formula you typed contains and error". Here are two ways to fix this...
Full article: Excel Tables as source for data validation lists
Advanced Filters have a couple of advantages over regular filters:
- The filter criteria is contained on your worksheet so you can easily see what filters are applied. Whereas with AutoFilter you have to click on the down arrow to see what filters have been applied.
- Advanced Filters allow you to extract your filtered data to another place in your worksheet/workbook or extract a unique list of records.
Their downside is it's not as obvious that your data is filtered since they don't display drop-down lists for the column headers like regular filters.
Full article: Excel advanced filters
I have been asked to share my personal principles for Excel good practice - and I thought you might be interested to see them too. My ten Excel principles...
Full article: My ten principles for Excel good practice
The easiest and highest impact way to implement coding standards is to adopt a disciplined way of naming VBA variables. What follows is SpreadSheet SuperStar's variable naming rules:
- Use
Dimstatement to declare all VBA variables. - Use
Option Explicitin all VBA modules. - Assign every VBA variable a data type.
- Name VBA variables with descriptive names.
- Use data type prefixes for each VBA variable.
- One variable declaration per line of code.
- Declare all variables at the top of each subroutine.
- Style points: Order variables declarations by size.
Full article: Not available
The following are the top 5 Excel features I use on a constant basis to get the most out of my data:
- What-If analysis.
- Sparklines.
- Conditional formatting.
- Format as table.
- PivotTables.
Full article: 5 underutilized Excel features to take advantage of
In Office 2013 there are now 5 features designed to help you manage the use of Excel spreadsheets, including 2 new server-based applications:
- Audit and Control Management Server (new).
- Discovery and Risk Assessment (new).
- Spreadsheet Inquire.
- Spreadsheet Compare.
- Database Compare.
Full article: Not available
In this article, I'll demonstrate how you can use conditional formatting to identify all unlocked cells within a worksheet range. This can serve both as an input aid, so that users know which cells they can affect, as well as an auditing tool, so that you know which cells are protected and which aren't.
Full article: Use conditional formatting to identify unlocked cells
The Excel INDEX function returns the value of a cell. The INDEX function is often confused with the Excel OFFSET Function. The INDEX function is part of a larger group of Excel lookup functions which includes MATCH, VLOOKUP and HLOOKUP.
In this tutorial we will explore the INDEX function and how it compares to the OFFSET function and potential errors that can occur when using this function.
Full article: Not available
FREQUENCY(data_array, bins_array)
Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than the bins_array:
data_array- is an array of or reference to a set of values for which you want to count frequencies.bins_array- is an array of or reference to intervals into which you want to group the values in thedata_array.
Full article: How to use FREQUENCY function
