Excel Function

The Excel Function archives at excelguru.io deliver practical, example-driven tutorials designed to help you move beyond basic formulas. This collection focuses on how to apply essential functions to real-world tasks, featuring in-depth guides on modern lookup tools like XLOOKUP and INDEX MATCH, conditional logic with IFS, COUNTIFS, and SUMIFS, as well as powerful data analysis functions such as SUMPRODUCT and FILTER. Each guide provides clear syntax breakdowns, side-by-side comparisons, and ready-to-copy formulas suitable for every Excel version from 2003 to Microsoft 365.

Whether you need to calculate employee tenure with DATEDIF, build dynamic reports that spill results automatically, or clean up messy spreadsheets using IFERROR, these tutorials offer step-by-step solutions. The content addresses common pain points like nested IF complexity, VLOOKUP limitations, and multi-condition aggregations, ensuring you can handle tasks ranging from commission tiers and grade scales to payroll sheets and date-based grouping—all without relying on helper columns or VBA.

Designed for business professionals, data analysts, and Excel users at all skill levels, this archive transforms how you work with data. Each post includes sample datasets, practical use cases, and expert tips to help you build cleaner, more efficient spreadsheets. Explore the full collection to master the functions that drive accurate reporting, streamlined workflows, and confident data analysis.

Goal Seek in Excel tutorial showing what-if analysis target values changing cells and business assumptions

Goal Seek: Find Required Input for Desired Output (Backwards Calculation)

Use Excel Goal Seek to work backward from a target result and find the required input value. This tutorial explains how to set a target cell, choose the changing cell, run Goal Seek, and apply it to practical examples such as sales targets, loan payments, profit goals, and pricing analysis. Ideal for Excel users, analysts, finance teams, students, and professionals who want a simple way to perform what-if analysis without complex formulas.

Linear programming in Excel tutorial showing Solver optimization decision variables constraints and objective function

Linear Programming & Optimization in Excel

Solve optimization problems in Excel with this practical linear programming tutorial. Learn how to define decision variables, set an objective function, add constraints, use Solver, and interpret the optimal solution for business, finance, operations, and resource planning scenarios. Ideal for Excel users, analysts, students, finance teams, and professionals who want to use Excel Solver for smarter decision-making.

Excel tutorial showing how to combine text from related rows using TEXTJOIN Power Query grouping and data consolidation

DAX CONCATENATEX: Combine Text from Related Rows

Combine multiple text values from related Excel rows into one clean result with this practical tutorial. Learn how to group related records, merge comments or names, use TEXTJOIN formulas, apply Power Query grouping, and prepare cleaner summary outputs from repeated rows. Ideal for Excel users, analysts, finance teams, admin staff, and professionals who need an easy way to consolidate text-based data.

DAX running total and YTD tutorial showing cumulative calculations date tables and Power Pivot time intelligence

Power Pivot: Calculate Running Total (YTD) with DAX

Create running totals and year-to-date calculations in Excel Power Pivot with this practical DAX tutorial. Learn how to use date tables, filter context, cumulative totals, YTD measures, and time intelligence functions to analyze performance over time. Ideal for Excel users, analysts, finance teams, and Power Pivot learners who want to build dynamic reports and track cumulative results accurately.

Power Query custom function tutorial showing reusable transformations parameters and Excel data automation

Power Query: Create a Custom Function (Reusable Logic)

Build reusable Power Query logic with this practical custom function tutorial. Learn how to turn repeated transformation steps into a custom function, apply it across multiple files or tables, pass parameters, clean data consistently, and reduce manual work in Excel. Ideal for Excel users, analysts, finance teams, data teams, and professionals who want to automate repetitive data preparation tasks.

Import PDF Tables into Excel tutorial showing Power Query table extraction data cleaning and PDF records analysis

Power Query: Transform Data from PDF (Import PDF Tables)

Bring PDF table data into Excel with this practical tutorial. Learn how to import tables from PDF files, select the correct table, load data into Excel, clean headers, fix formatting issues, and prepare extracted records for reporting or analysis. Ideal for Excel users, analysts, finance teams, admin staff, and professionals who regularly work with PDF reports and need a faster way to convert them into usable Excel data.

Power Pivot Date Table tutorial showing calendar fields relationships and Excel data model setup

Power Pivot: Create Date Table for Time Intelligence

Build a proper Date Table in Excel Power Pivot with this practical tutorial. Learn how to create calendar dates, add year, month, quarter, and weekday fields, mark the table as a date table, and connect it to your data model for better reporting. Ideal for Excel users, analysts, finance teams, and Power Pivot learners who want cleaner data models and more accurate time-based analysis.

Custom Views in Excel tutorial showing saved worksheet layouts filters print settings and reporting views

Custom Views: Save & Switch Between Different Display Settings

Save and switch between different worksheet views with Excel Custom Views. This tutorial explains how to create custom views, preserve print settings, filters, hidden rows, hidden columns, page setup, and reporting layouts for faster workbook navigation. Ideal for Excel users, analysts, finance teams, admin staff, and professionals who need an easy way to manage multiple views of the same workbook.

Data Validation in Excel tutorial showing dropdown lists input rules error alerts and spreadsheet controls

Data Validation: Create Dropdowns from Tables & Dynamic Named Ranges

Control data entry and reduce spreadsheet errors with Excel Data Validation. This tutorial explains how to create dropdown lists, set input rules, restrict values, show input messages, display error alerts, and manage validation settings across worksheets. Ideal for Excel users, analysts, finance teams, admin staff, and professionals who want cleaner data, better controls, and more reliable spreadsheets.

Advanced Filter in Excel tutorial showing criteria ranges unique records filtered data and copied results

Advanced Filter: Extract Unique Records & Complex Criteria

Filter Excel data more powerfully with the Advanced Filter tool. This tutorial explains how to set up criteria ranges, filter records in place, copy results to another location, extract unique values, and apply multiple conditions to large datasets. Ideal for Excel users, analysts, finance teams, admin staff, and professionals who need a faster way to clean, filter, and analyze structured data.

Excel Subtotal Data tab tutorial showing auto outline grouped summaries category totals and report structure

Subtotal (Data Tab): Auto Outline & Group Summaries

Create quick grouped summaries in Excel using the Subtotal tool from the Data tab. This tutorial explains how to sort data, apply subtotals, group rows automatically, use outline levels, summarize by category, and remove subtotals when needed. Ideal for Excel users, analysts, finance teams, admin staff, and professionals who need an easy way to summarize large datasets without building formulas manually.

Excel tutorial for consolidating data from multiple sheets using formulas Power Query and consolidation tools

Consolidate Data from Multiple Sheets (No Formulas Needed)

Combine data from multiple Excel sheets into one organized report with this practical tutorial. Learn how to consolidate worksheets using Excel formulas, Power Query, built-in consolidation tools, consistent headers, and clean data structures. Ideal for Excel users, analysts, finance teams, admin staff, and professionals who need an easy way to merge data across sheets and create reliable summary reports.