Functions in Excel for Date Formulas

Introduction

Microsoft Excel provides a variety of functions that can be used to work with dates. Each function performs a simple operation; if you combine multiple functions within one formula, you can tackle more complex tasks. In this article, we'll talk about how to use functions in Excel for date formulas and provide some examples to illustrate the process.

Default date format in Excel

Different countries and regions use different date formats. Much of the world uses a DD/MM/YY format; however, the United States uses a MM/DD/YY format. If you'd like to change the default date format for your Excel spreadsheets, click on the dropdown for the More Number Formats.. option, which can be found in the Home tab.

Change the default date format in Excel and how to change the date format in Excel

A modal window should pop up. In this window, you can configure the default format for the date values in your spreadsheets.

Excel date formula

Date formulas allow you to customize the exact date values for a cell. Many date formulas make use of the DATE() function, which can be entered as part of a formula in a cell of a worksheet. This function actually returns the date’s serial number in a General numeric format, but is displayed as a date based on your chosen format.

Excel date format formula

Here's an example of the DATE() function being used as a part of a cell formula:

=DATE(2020, 2, 11)

This formula returns a serial number that corresponds to 11-February-2020 or 2/11/20, depending on your selected date format.

Formula in Excel to get the date for today

The TODAY() function is the simplest way to get the current date in an Excel spreadsheet. Just enter the following in a cell:

=TODAY()

If you use the TODAY() function call without passing any arguments to it, it will display the current date in the (MM/DD/YYYY) format.

The TODAY() function can also be used as a part of more complex formulas based on today’s date. For example, to add five days to the current date, just use the following formula:

=TODAY()+5

NOW function in Excel

The Excel NOW() function returns the current date and time. To display today’s date and current time in your worksheet, simply enter the following formula in a cell:

=NOW()

The formula example shown below will return the first day of the current year and month:

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

You can use the =DATE(2020, 2, 11)-5 formula to subtract five days from February 11th, 2020.

Screenshot of example functions in Excel for date formulas in a spreadsheet

Formulas in Excel to get a date value

The DATEVALUE() function converts a date in the text format to a serial number that Microsoft Excel recognizes as a date.

Let's look at a few simple DATEVALUE() function examples used in the formulas below:

=DATEVALUE("11-Feb-2020")

=DATEVALUE("2/11/2020")

=DATEVALUE("2 11, 2020")

Regardless of the date format, these values were able to be converted to a serial number.

Excel TEXT function

The TEXT() function will change date values to text strings in a variety of formats. Let's look at some examples:

=DAY(A2) - returns the day of the date in A2
=DAY(DATE(2020,2,11)) - returns the day of 11-Feb-2020
=DAY(TODAY()) - returns the day of today’s date

NOTE: If you want to extract just a specific value from a date, like the DAY(), MONTH(), or YEAR(), you'll have to change the cell's format from Date to General. This will prevent Excel from attempting to convert the numeric value to a date-like format.

Excel MONTH function

The MONTH function in Excel returns the month of a specified date. The value is returned as an integer ranging from "1" (January) to "12" (December).

Here are some examples pf the MONTH function:

=MONTH(A2) - returns the month of a date in cell A2
=MONTH(TODAY()) - returns the current month

Excel YEAR function

The YEAR Function returns a year corresponding to a given date. The value is returned as a number from 1900 to 9999. Some examples of the function are shown below:

=YEAR(A2) - returns the year of a date in cell A2
=YEAR("11-Feb-2020") - returns the year of the specified date

The following DATE() example shows a simple way to get the year of a given date:

=YEAR(DATE(2020,2,11))

=YEAR(TODAY()) - returns the current year

Screenshot of functions in Excel for date formulas to get date values

Conclusion

If you're working with data in Microsoft Excel, it's important to know how to retrieve and manipulate date values. In this article, we reviewed some common Excel date functions and showed how they can be implemented in formulas. With our explanations and examples, you'll be ready to utilize Excel date functions in your own spreadsheets.

Mac Excel Shortcuts

Introduction

If you use Excel frequently, you know how much time keyboard shortcuts can save. While these shortcuts make many tasks easier, it can take time to learn and master them all. That's where a handy "cheat sheet" can be a game changer. In this article, we'll review the most useful Mac Excel shortcuts, organized into a simple table for quick reference.

Excel keyboard shortcuts

Some of the basic keyboard shortcuts for Excel are the same on a Mac or PC, but most shortcuts, especially the more complex ones, are mapped differently for Excel on macOS.

NOTE: Many basic Windows shortcuts, like CTRL+V for paste or CTRL+O to open a worksheet, can be executed on a Mac by pressing ⌘ ("command") instead of CTRL.

Mac excel shortcuts

In this section, we'll show a few of the most commonly-used macOS keyboard shortcuts. These shortcuts can help you boost your productivity while working in Excel.

macOS Excel shortcuts cheat sheet

The following table contains some useful Excel keyboard shortcuts for macOS:

Functionality Shortcut
Edit current cell control+U
Cut ⌘+X
Paste ⌘+V
Fill down ⌘+D
Fill right ⌘+R
Duplicate from cell above ⌘+D
Duplicate from cell to the left ⌘+D
Select the entire row shift+space
Select the entire column control+space
Duplicate from cell to the left ⌘+D
Format cells window ⌘+1
Save workbook ⌘+S
Open workbook ⌘+O
Close workbook ⌘+W
Redo ⌘+Y
Undo ⌘+Z
Hide selected column ⌘+0
Hide selected row ⌘+9
Show hidden column ⌘+shift+0
Show hidden row ⌘+shift+9
Find & replace ⌘+shift+H
Display emoji window ⌘+control+space
Display insert cell window ⌘+shift+=
Display formula builder window shift+fn+F3
Decrease font size of selected ⌘+shift+<
Increase font size of selected ⌘+shift+<
Change format to numerical control+shift+!

Useful Excel shortcuts

This section will cover some of the most useful Excel keyboard shortcuts for a Mac. Keep in mind that these shortcuts were tested on the 2016 version of Microsoft Excel.

Excel new sheet shortcut

Press the fn+shift+F11 keys simultaneously to create a new sheet for your workbook on a Mac.

HINT: You can also hold down the option () key and navigate to different sheets in your workbook by pressing the arrow keys.

Screenshot of the Excel new sheet shortcut and switching sheets on macOS

Excel delete row shortcut

To delete, or shift, a row up, you can press control+- or ⌘+U on a Mac.

Excel insert row shortcut

You can also use the shortcut shown above to insert an entire row. To do this, you'll need to make sure to select the entire row first, or else it will simply shift the current cell or column down.

Screenshot of Excel shortcuts for shifting or deleting rows

Absolute cell reference shortcut

In Windows, you can press F4 to change a cell's default reference from relative to absolute. Excel inserts the $ symbol before the columns and rows for a referenced cell or range. On a Mac, however, you'll need to press ⌘ and T together to accomplish this while editing a cell.

Excel strikethrough shortcut

On a Mac, you can press ⌘+shift+X (or CTRL+5 in Windows) to strikethrough a cell or range of cells in an Excel spreadsheet.

Excel paste values shortcut

In most macOS applications, the paste shortcut is ⌘+V, and Excel is no different. If you'd like to strip all formatting from your clipboard before pasting its contents into a cell, you'll have to press control+U to edit the cell contents. After you make your changes, press ⌘+V.

Excel paste special shortcut

To paste the contents of your clipboard with a special format such as Unicode or formula values, simply press control+option+V.

Formula shortcuts in Excel

If you'd like to bring up the window for inserting formulas, just hold down the control and option keys while also pressing the down arrow key.

Show formulas in Excel

If you press control and the ` grave accent mark key, you can toggle the contents of your cells between values and the formulas that render the values. This shortcut can be useful to understand how a sheet functions and calculates relative values for its respective cells.

Autosum Excel shortcut

To insert a =SUM() formula into a cell so that the current cell adds up all the numeric values in a column, just press the ⌘+shift+T key combination.

Mac Excel shortcuts screenshot of the autosum Excel shortcut in macOS

NOTE: This shortcut will return an error if the selected column doesn't contain numeric values.

Conclusion

Using keyboard shortcuts can help you work more quickly and efficiently in Microsoft Excel, but you might face a bit of a learning curve if you're just getting started. In this article, we've provided a list of commonly-used Excel shortcuts that can be used on a Mac. With our handy cheat sheet as a reference, you'll be ready to start using keyboard shortcuts and speed up your Excel tasks.

Excel Remove Duplicates Tutorial

Introduction

When you work with Excel spreadsheets, you're likely to encounter duplicates in your data at some point. There are many potential causes of duplicates-- for example, you might have exported data from different sources or consolidated multiple spreadsheets. Regardless of the cause, it's time-consuming to find and remove duplicates manually. In this article, we'll show you how to minimize the work involved in cleaning up your data by using Excel to remove duplicates.

Prerequisites

If you're planning to follow along with the examples we present in this tutorial, you'll need to have Microsoft Excel installed on your computer, Basic knowledge of Excel is recommended to get the most out of this article.

Microsoft Excel versions

Microsoft Excel can be downloaded on Windows, macOS, Android or IOS. It's best to use a supported version of Excel, preferably 2013 or newer. To determine what version you're running, navigate to the "About" section of your Excel application.

Find duplicates in Excel

In some cases, you may want to verify which cells in your spreadsheet contain duplicates before you attempt to modify any data. Excel offers custom highlighting rules that allow you to highlight duplicates by formatting the affected cells.

Excel: highlight duplicates

If you'd like to highlight the duplicate cells, you'll need to select all the cells by pressing CTRL+A, or command+A on a Mac, on your keyboard. You can also click the small "Select All" square button in the upper left-hand corner of the current sheet.

Once all of the cells are selected, you can navigate to the Home tab, click the Conditional Formatting dropdown button and select "Duplicate Values".

Screenshot of highlighting cells in Excel to delete duplicates

A modal window allowing you to enter a new formatting rule should pop up at this point. Here, you can create a rule that will format the duplicate values in your sheet.

Screenshot of a selected cells formatting rule for the Excel delete duplicates example

Delete duplicates in Excel

Screenshot of how to remove duplicates in an Excel spreadsheet

Screen of an Excel delete duplicates example selecting header or columns targeted for the remove duplicates action

Excel remove duplicates example of an alert window showing number of values removed

Use an Excel formula to remove duplicates

You can also use an Excel formula to find and remove duplicates. Choose an empty column and row and input a formula using the COUNTIF() function to filter for a specific condition:

=COUNTIF(range, criteria)

Using an absolute reference for a cell range in the formula will help us identify and remove duplicate cells.

Absolute reference in Excel

In Excel, an absolute reference uses the dollar sign as an alias for the relative position of cells. For example, if you have 16 cells in one column, the range would be expressed as =COUNTIF(A1:A16,A1); however, you can lock the range to (A1:A16) using an absolute reference. Simply press F4, or use the Command+T shortcut key in macOS, while you're still editing the formula in the cell.

Count unique values with an Excel formula

After locking the range, the formula should look like this: =COUNTIF($A$1:$A$16,D4). A number will now appear in the cell location for the formula that counts the duplicates. Just drag the formula down to have it duplicated for each row or column.

Any formula cell that contains a number of two or greater indicates that the row has duplicate values. The screenshot below shows a typical result of the COUNTIF function:

Screenshot of an Excel COUNTIF function to find duplicate cells in a spreadsheet

Advanced filters in Excel

The last method that can be used to remove duplicate values in Excel involves an advanced filter search. Here's how to use it:

At this point, your duplicates should be filtered out and removed.

Conclusion

If you find yourself dealing with duplicate data in your Excel spreadsheet, there's no need to take a manual approach to the task. You can get the job done quickly with some useful Excel features and functions. In this article, we looked at a few different ways to remove duplicates in Excel. With these handy tools at your fingertips, you'll be ready to clean up the data in your own Excel worksheets.