Summary of Functions

Example Table

Basic Counting

COUNT → counts numbers only

=COUNT(C2:C6)  → 4

COUNTA → counts all non-empty cells

=COUNTA(C2:C6) → 4 (counts numeric and text if present)

COUNTBLANK → counts empty cells

=COUNTBLANK(C2:C6) → 1

Conditional Counting

COUNTIF → counts cells that meet one condition

=COUNTIF(B2:B6,"Marketing") → 3
=COUNTIF(D2:D6,"Active") → 4

COUNTIFS → counts cells that meet multiple conditions

=COUNTIFS(B2:B6,"Marketing",D2:D6,"Active") → 2

Conditional Summing

SUMIF → sum values based on one condition

=SUMIF(B2:B6,"Sales",C2:C6) → 450

SUMIFS → sum values based on multiple conditions

=SUMIFS(C2:C6,B2:B6,"Marketing",D2:D6,"Active") → 100

Conditional Average

AVERAGEIF → average values based on a condition

=AVERAGEIF(B2:B6,"Marketing",C2:C6) → 125

Key Takeaways

• COUNT / COUNTA / COUNTBLANK → basic counting of numbers, non-empty, or blank cells
• COUNTIF / COUNTIFS → count based on one or more conditions
• SUMIF / SUMIFS → sum values conditionally
• AVERAGEIF → average values based on a condition
• Use these functions for quick data aggregation, reporting, and analytics

If you’re using Excel for data analytics, reporting, or general data work, understanding formulas, functions, and cell references is essential. These are the building blocks behind almost every spreadsheet.

What Is a Formula?

A formula is a calculation you write yourself using:

• Numbers
• Cell references
• Math operators: + - * /

All formulas start with =.

Examples:

=B2+B3
=B2-B3
=B2*B3
=B2/B3

Cell References vs Hardcoding Numbers

Avoid hardcoding:

=10+20

Use cell references:

=B2+B3

This makes your spreadsheet dynamic. When values change, the result updates automatically.

What Is a Function?

A function is a built-in formula Excel provides to simplify common calculations.

Example:

=SUM(B2:B5)

Key idea:
Every function is a formula. Not every formula is a function.

When to Use Functions

Instead of:

=B2+B3+B4+B5

Use:

=SUM(B2:B5)

Functions are cleaner, faster, and reduce errors.

Essential Excel Functions

SUM – Add values

=SUM(B2:B5)

AVERAGE – Calculate the mean

=AVERAGE(B2:B5)

MAX – Largest value

=MAX(B2:B5)

MIN – Smallest value

=MIN(B2:B5)

COUNT – Count numeric cells

=COUNT(B2:B5)

IF – Apply logic

=IF(B2>50,"Pass","Fail")

Relative vs Absolute Cell References

• B2 → Relative reference (changes when dragged)
• $B$2 → Absolute reference (stays fixed when dragged)

This matters when copying formulas across rows or columns.

Explore Excel’s Function Library

Go to Formulas → Insert Function to browse Excel’s full function library and get help building formulas.

Key Takeaways

Understand absolute vs relative references when copying formulas

Formulas start with = and use cell references

Functions are built-in formulas like SUM, IF, AVERAGE

Always reference cells instead of hardcoding numbers

Use functions to simplify calculations

Intro / The Problem

You have Excel dates like:

25 12 2024
27 01 2025

Excel treats them as text, not real dates, because the format is DD MM YYYY instead of MM DD YYYY.You can’t sort, filter, use date formulas, or load them correctly into Power BI / Tableau.

You need to convert text to a real Excel date, then format it.

Solution Summarized

1. Select the column
2. Data → Text to Columns
3. Choose Delimited → Next → Next
4. Set Column Data Format = Date (DMY) → Finish
5. Ctrl + 1 → Custom format → mm dd yyyy

Solution Steps

Step 1 — Select the Date Column

Highlight the entire column with the DD MM YYYY dates.

Step 2 — Open Text to Columns

Go to Data → Text to Columns and choose Delimited.

Step 3 — Skip Delimiters

Uncheck all delimiters. Click Next.

Step 4 — Set Date Format to DMY

Under Column Data Format, choose Date → DMY. Click Finish.
This converts Excel text to a real date.

Step 5 — Apply Custom Date Format

Press Ctrl + 1 → Custom → type:

mm dd yyyy

How to Verify It Worked

• =YEAR(A2) returns a year
• Sorting works correctly
• Changing to Long Date changes the display
• Works in Pivot Tables / Power BI

Summary

• Excel can’t recognize DD MM YYYY dates (when the region of the software is set to U.S.)
• Formatting alone does not fix text dates
• Text to Columns (DMY) converts text to a real date
• Custom format displays as MM DD YYYY

Before we begin, ensure that you have at least Python 3 installed. I’m using Python 3.9

First, create a new folder. This will be your Python project folder.

Next, create a Python file inside your folder. It doesn’t matter what you call it. I’ll call mine main.py

In order to import libraries inside our folder, we need to first create a Python virtual environment. To do this run the following command in Terminal inside your project directory:

python3.9 -m venv projenv 

I typed python3.9 because I’m using Python 3.9, but you would type whichever version you’re using.

I called my virtual environment projenv, but you can call yours whatever you want.

You should see a new folder for your virtual environment was created within your project folder.

Note: Check to make sure that the VS Code Python interpreter is the correct version.

Next, we will activate the virtual environment so that Terminal knows we only want to work within the virtual environment.

source projenv/bin/activate

Now, you should see the virtual environment name in parentheses at the left of your new Terminal line.

It’s good practice to make sure you have installed the latest version of pip. Run this command to update to latest:

pip3 install --upgrade pip

Now, we can start installing pip dependencies to our project.

To install a dependency, run the following command:

pip3 install matplotlib

I’m using matplotlib in this example.

Now, we will check to to see if the install was successful. In the Python file you created earlier, import the matplotlib package:

import matplotlib.pyplot

Run the main.py file by typing python main.py in Terminal.

If you don’t get an error, then you successfully installed a package!

Visual Studio Code Extensions make your workflow so much more efficient and enjoyable in VS Code. This article provides a list of some of my favorite VS Code extensions. There is no ordering from best to worst.

1. Auto Rename Tag

If you change an HTML/XML tag, this extension will automatically update the paired tag.

2. Bracket Pair Colorizer

This extensions’ intuitive colorizing of matching brackets (and parentheses) makes it easier to see where a block starts and ends

3. Live Server

This extension launches a local server with live reload. This extension is great for updating static HTML/CSS/JS files quickly without creating your own server.

4. Prettier

Have you been formatting your code manually? No more. This extension will format your code for you on save.

5. Live Share

Do you want to work on a project with a friend or teammate? This extension allows you to collaborate on a project in VS Code in real-time. Think Google Docs but for code!

6. Quokka.js

This extension allows you to test out JavaScript and TypeScript code on the fly in an in-editor playground.

7. Import Cost

When you import a package into a file in VS Code, this extension will show you how much memory that package takes up.

8. GitLens

This extension allows you to visualize code authorship via Git blame annotations and code lens. You can also navigate in the history of a file back and forth to see the changes that were made on it.

9. Path Intellisense

This extension will auto-complete filenames and file paths for you as you type them.

10. Snippets

This is not a single extension but a collection of extensions that provide code snippets for specific languages and frameworks. Using snippets allows you to not have to type as much. The example I’m showing here is the ES7 React snippets for React developers.

11. Better Comments

This extension augments your ability to create more human-friendly comments in your code. You can categorize your annotations with different colors.

12. VS Code Icons

This extension adds icons to your files in the sidebar so it is easier to tell which is which when there is a lot of files.

Go check out all of these great extensions!

The Error

If you are using a string URI when dealing with the http package in Flutter, you may be seeing this error:

The argument type 'String' can't be assigned to the parameter type 'Uri' at .... (argument_type_not_assignable)

This error is due to an update in the package.

The Solution

Parse the String to be an explicit URI by using the Uri.parse() method:

http.get(yourString) becomes http.get(Uri.parse(yourString))

http.post(yourString) becomes http.post(Uri.parse(yourString))

Here is it in an example:

String dataURL = "https://api.com/users";
http.Response response = await http.get(Uri.parse(dataURL));

To improve compile-time type safety, the http package (version 0.13.0) introduced changes that made all functions that previously accepted Uris or Strings now accept only Uris instead.

You will need to explicitly use Uri.parse to convert Strings to Uris. In the previous version, the http packaged called that for you behind the scenes.