'Features' category


You are here: Excel » Features »

Subcategories

  • Advanced filter (7)
  • Data validation (28)
  • Excel table (22)
  • Named range (5)
  • Pivot table (16)
  • Solver (7)

    • test
    How to solve simultaneous linear equations in Excel
    This article demonstrates how to solve simultaneous linear equations using formulas and Solver. The variables have the same value in […]
    test
    How to delete empty rows
    I highly recommend you keep the original data and only copy the data excluding blank rows and paste to a […]
    test
    How to quickly select a cell range
    Selecting cell ranges in Excel can sometimes be a real pain scrolling forever it seems. There is a quick and easy […]
    test
    How to filter using OR logic between columns [Formula]
    The filter feature in Excel won't allow you to do OR logic between columns, however, you can if you allow […]
    test
    How to filter using OR logic between columns [Advanced Filter]
    Table of Contents How to filter using OR logic between columns Advanced custom date filter 1. How to filter using […]
    test
    How to use the COMBIN function
    The COMBIN function returns the number of combinations for a specific number of elements out of a larger number of […]
    test
    How to use Excel Tables
    An Excel table allows you to easily sort, filter and sum values in a data set where values are related.
    test
    How to use Pivot Tables – Excel’s most powerful feature and also least known
    A pivot table allows you to examine data more efficiently, it can summarize large amounts of data very quickly and is very easy to use.
    test
    How to automatically add new items to a drop down list
    A drop-down list in Excel prevents a user from entering an invalid value in a cell. Entering a value that […]
    test
    How to copy Excel tables programmatically
    The image above demonstrates a macro linked to a button. Press with left mouse button on the button and the […]
    test
    Optimize pick path in a warehouse
      As you probably already are aware of I have shown you earlier a vba macro I made that finds the […]
    test
    How to use the Subtotal tool
    What's on this page Prepare data Sort data Start the Subtotal tool Subtotals on multiple levels Dialog box settings Remove […]
    test
    How to filter chart data
    What if you want to show a selection of a data set on a chart and easily change that selection? […]
    test
    Lookup with any number of criteria
    This article demonstrates a formula that allows you to search a data set using any number of conditions, however, one […]
    test
    How to use the Scroll Bar
    This article demonstrates how to insert and use a scroll bar (Form Control) in Excel. It allows the user to […]
    test
    Prevent duplicate records in a worksheet
    This article demonstrates how to set up Data Validation in order to control what the Excel user is allowed to […]
    test
    Pivot Table calendar
    This article demonstrates how to build a calendar in Excel. The calendar is created as a Pivot Table which makes […]
    test
    Compare data in an Excel chart using drop down lists
    I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the […]
    test
    How to build an interactive map in Excel
    This article describes how to create a map in Excel, the map is an x y scatter chart with an […]
    test
    Highlight duplicates in a filtered Excel Table
    The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
    test
    How to sort a data set using three different approaches, built-in tools, array formulas, and VBA
    I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]
    test
    Highlight group of values in an x y scatter chart programmatically
    I will in this article demonstrate how to highlight a group of values plotted in an x y scatter chart […]
    test
    How to calculate totals of stock transactions based on dates
    Did you know that you can use a pivot table to summarize portfolio holdings at any point in time? If you trade […]
    test
    Run a Macro from a Drop Down list [VBA]
    This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
    test
    How to change cell formatting using a Drop Down list
    This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column […]
    test
    Prevent overlapping date and time ranges using data validation
    The picture above shows an Excel Table with Data Validation applied. An error dialog box appears if a user tries […]
    test
    Find smallest and largest unique number
    This article explains how to calculate the largest and smallest number based on a condition which is if the number […]
    test
    Create monthly time sheet using a Pivot Table
    Today I am going to demonstrate how amazing pivot tables are! Take a look at this time sheet. You can […]
    test
    Analyze trends using pivot tables
    Table of Contents Introduction to pivot tables Create pivot table Group data Analyze data (pivot table) Compare performance, year to […]
    test
    Hide specific columns programmatically
    This article describes a macro that hides specific columns automatically based on values in two given cells. I am also […]
    test
    How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas
    This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]
    test
    Copy Excel Table filter criteria programmatically
    I will in this article demonstrate a macro that copies criteria from one Excel Table and applies them to another […]
    test
    Perform thousands of conditions to a data set
    This article explains how to filter a data set based on extremely many conditions in an Excel defined Table, in […]
    test
    Populate drop down list with filtered Excel Table values
    This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]
    test
    Macro creates links to all sheets, tables, pivot tables and named ranges
    This article demonstrates a macro that automatically populates a worksheet with a Table of Contents, it contains hyperlinks to worksheets, […]
    test
    Count unique distinct values in a filtered Excel defined Table
    This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
    test
    Prepare data for Pivot Table – How to split concatenated values?
    This article demonstrates a macro that allows you to rearrange and distribute concatenated values across multiple rows in order to […]
    test
    Search two related tables [VBA]
    This article demonstrates a macro that automatically applies a filter to an Excel defined Table based on the result from […]
    test
    Disable autofit column widths for Pivot table
    I read this interesting article Quick Trick: Resizing column widths in pivot tables on the Microsoft Excel blog. It is […]
    test
    Normalize data [VBA]
    To be able to use a Pivot Table the source data you have must be arranged in way that a […]
    test
    Use a drop down list to filter and concatenate unique distinct values
    Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that […]
    test
    Add or remove a value in a drop down list programmatically
    This article demonstrates how to add or remove a value in a regular drop down list based on a list […]
    test
    Auto populate a worksheet
    Rodney Schmidt asks: I am a convenience store owner that is looking to make a spreadsheet formula. I want this […]
    test
    Use a drop down list to display data in worksheet header [VBA]
    Aynsley Wall asks: I have a spreadsheet that I use for 3 different companies. What I would really like to […]
    test
    Apply drop-down lists dynamically
    This article demonstrates how to automatically create drop-down lists if adjacent data grows, there are two methods explained here. The […]
    test
    Dependent drop-down lists in multiple rows
    This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]
    test
    Basic invoice template
    Rattan asks: In my workbook I have three worksheets; "Customer", "Vendor" and "Payment". In the Customer sheet I have a […]
    test
    Use a drop down list to search and return multiple values
    I will in this article demonstrate how to use a value from a drop-down list and use it to do […]
    test
    Change chart data range using a Drop Down List [VBA]
    In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
    test
    Change PivotTable data source using a drop-down list
    In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
    test
    Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]
    This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
    test
    Use hyperlinks in a pivot table
    Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]
    test
    Filter an Excel defined Table programmatically [VBA]
    In this tutorial, I am going to demonstrate how to filter an Excel define Table through a VBA macro. How it […]
    test
    Filter unique distinct records using criteria
    This blog post describes how to filter unique distinct records that meet a given condition in an Excel defined Table. This article […]
    test
    Extract unique distinct values based on a filtered Excel defined Table
    This blog post demonstrates how to filter unique distinct values from an Excel table dynamically. When you change or add […]
    test
    Remove common records between two data sets
    This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]
    test
    Count unique distinct values in an Excel Pivot Table
    ExcelBeginner asks: I have a small problem that I am not sure on how to solve. I now have a […]
    test
    Auto refresh a pivot table
    In a previous post: How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]
    test
    How to compare two data sets
    This article demonstrates how to quickly compare two data sets in Excel using a formula and Excel defined Tables. The […]
    test
    Count unique distinct records (rows) in a Pivot Table
    Excel 2013 allows you to count unique distinct values in a pivot table, this article explains how to use a […]
    test
    Create a dynamic named range
    A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]
    test
    How to create a dynamic pivot table and refresh automatically
    This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete […]
    test
    Dependent drop down lists – Enable/Disable selection filter
    Josh asks: I have this working right now with 6 drop downs/lists. I wanted to see if you possibly know […]
    test
    Cash drawer bill extractor
    Question: I need to setup a template to remove the largest available (Qty and denomination)bills and leave exactly $150 back […]
    test
    Filter duplicate records
    This article demonstrates how to filter duplicate records using a simple formula and an Excel defined table.
    test
    Create a drop down calendar
    The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]
    test
    Invoice template with dependent drop down lists
    This article demonstrates a basic invoice template I created. It lets you use dropdown lists to quickly select products on […]
    test
    Create dependent drop down lists containing unique distinct values in multiple rows
    Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple […]
    test
    Apply dependent combo box selections to a filter
    Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
    test
    Create a drop down list containing alphabetically sorted values
    This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]
    test
    Extract a unique distinct list based on a condition [Pivot Table]
    Anura asks: I have a list of credit card transactions showing the name of the cardholder, their Branch and the […]
    test
    Create dependent drop down lists containing unique distinct values
    This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]
    test
    Create a unique distinct list using Advanced Filter in a macro [VBA]
    Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]
    test
    Extract unique distinct values if value contains string
    The image above demonstrates a formula in cell F3 that extracts unique distinct values from column B if they contain […]
    test
    Using Excel Solver to schedule employees
    This is a question I found at the bottom of this page Using Solver to schedule your workforce Bank 24 […]
    test
    Find positive and negative amounts that net to zero
    I found this excel question: I am a Controller in a multinational company. We have many transactions (sales, credits, debits, […]
    test
    Identify numbers in sum using Excel solver
    Here is a useful feature I recently found googling for Excel solver examples. I have summed some random values from […]
    test
    Advanced custom date filter
    Question: How do I filter the last xx years or xx months in Excel? How do I exclude the current […]
    test
    Populate drop down list with unique distinct values sorted from A to Z
    Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]
    test
    5 easy ways to extract Unique Distinct Values
    First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
    test
    List all named ranges and their cell references
    This article shows you a way to display all named ranges you have in a workbook. This is a powerful […]

    Excel categories


    Home page
    Latest updated articles.
    Excel Functions
    More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.
    Excel Formulas
    More than 1300 formulas organized in subcategories.
    Excel Tables
    Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.
    Advanced Filter
    Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.
    Data Validation
    Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.
    Drop Down List
    Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.
    Named Ranges
    Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.
    Excel Solver
    The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.
    Charts
    An Excel feature that lets you visualize data in a graph.
    Conditonal Formatting
    Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.
    Pivot Tables
    Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.
    VBA
    VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.
    Macros
    A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.
    UDFs
    UDF stands for User Defined Functions and is custom built functions anyone can create.
    Archive
    A list of all published articles.