Named Ranges in Excel — and New Excel Formulas Training 🎉

If you have been a long-time subscriber, you might remember me mentioning Named Ranges in Excel a long time ago. Using your Name Box to generate Named Ranges can make it so much easier to navigate through a workbook that contains many sheets and will help you more easily build 3D functions and formulas more easily across sheets.

With Excel dashboards being all the rage lately, I thought it would be the perfect time to revisit the incredibly useful, and incredibly understated, functionality of named ranges. Also, I wanted to give you a special preview of a new Excel training session you have access to in myTraining. Let’s take a look!

About Named Ranges in Excel

Most people primarily use Excel’s Name Box (this box that lives to the left of your formula bar) to identify cell location, but there is much more utility here than you may realize.

For instance, you can name cells, or a series of cells, yourself, making them easier to access, like in this example of a workbook with two named ranges I can easily jump to by using the name box. So, the first advantage is named ranges make your workbooks easier to navigate.

Even more useful is the ability to reference named ranges within a formula, preventing the need to click around between sheets, potentially selecting incorrect data in the process, as you are completing your function arguments.

This formula, for instance, references an entire column on another sheet:

Here is a formula referencing two named ranges from other sheets in the workbook:

Named Ranges Video

I wanted you to see these in action, so I am sharing a new video with you that shows you named ranges in more detail. This video is actually a portion of a lesson within the new Excel Advanced Formulas: Part 2 training.

If this video piques your interest, the full course is now (just today!) available to faculty and staff in myTraining. I would love to hear what you think!

Note: if the contents of the video appear too small in this embedded view, click on the video title to expand.

Excel Training Available

✨Visit myTraining to sign up for live Excel sessions, including:

Excel Essentials
Excel PivotTables and Charts
Excel Advanced Formulas

✨And asynchronous Excel sessions, including:

Excel Advanced Formulas, Part 2 (where named ranges appear)
Excel 365: New Functions

Keep Your SharePoint Files Organized with Content Types 📂

Now that nearly 200 of you have come through SharePoint training and have gone on to manage your own SharePoint sites, your document libraries are probably bursting at the seams with documents and folders. One tool we look at in SharePoint 2: Site Creation Basics training is the ability to create columns to help organize site files. If you liked learning about columns, you will love content types!

Content types will give you the ability, among other things, to apply columns to specific files, and effectively filter by files with similar column structures.

Check out the video below, with examples and instructions for building your own content types.

Video: Content Types

If the screen is too small to view embedded, click on the video title to open in a new screen.

Written Instructions

You just saw content types in action, so here are the steps I took in the video in written form.

Create a Content Type

The first step is to create your content type(s).

1. Go to Site Contents > Site Settings

2. Select Content Types under Web Designer Galleries. Note that this may appear on the left side of the screen in the case of Communications sites.

3. Select Create Content Type.

4. Name your content type. If the name is already in use in columns on your site, you will be prompted to pick a different name.

- For Parent Category, select Document Content Types.
- For Content Type, select Document.

5. Select Add Site column and add as many columns as you would like. The column types and options are the same that you see when we added columns to a site library in SharePoint 2 training.

Add Content Type to a Site Library

Your content type is created, so now you can add it to any of your site document libraries.

Go to your document library and select Add Column. Scroll down and select Add a content type

2. Select your custom content type.

Assign a Content Type to Documents in the Library

At this point, the content type has been created and applied to a library. Now it is available to add to specific files.

Select any files to which you would like to apply the new content type. Go to Details (“i” in the upper right).

2. Under Content Type, select your custom content type.

3. Review the pane for your columns, and press Save.

Add Desired Data to New Columns

Just like any other columns, you will need to fill in the values for the custom content fields. Having labeled your documents as their new content types, you can go to the details of each file and fill in the custom columns, even though these columns are not readily in view.

Select each file that has the custom content type applied and go to details (“i” in the upper right). Complete any desired fields. In my case, there were two new fields: a person field and a yes/no field.

Filter by Content Type to See Columns

Now that your content types have been created, applied to a library, and applied to specific documents, you can filter by a content type to view the custom columns.

In the upper right view settings, go to All Documents and find your custom content type fields in the list.

Your custom columns with any completed data will appear. To go back to the previous view, click on the same dropdown menu and select All Documents.

Wrapping Up and More Resources

Well, what do you think? I hope this was a helpful trick as you are organizing your SharePoint document libraries! This is a somewhat advanced look at SharePoint organization, so if you are unfamiliar with SharePoint, this may sound a little confusing. If so, here are some more resources for you.

Resources from ITS

ITS has two SharePoint training offerings available:

- SharePoint 1: Foundations
- SharePoint 2: Site Creation Basics

Visit myTraining to view the training schedule, sign up for SharePoint sessions and look for other Microsoft Cloud sessions, like OneDrive and Teams.

Resources from Microsoft

Here is some information about content types from Microsoft:

Wait! You may not need a new Team… Let’s talk about ✨Channels✨

Now that the new year is well underway and you and your colleagues are finding yourselves embarking upon new projects, you may be tempted to create a series of new Teams to help you organize your content. But before you click “Create” on those new Teams, consider if you may be better served creating a channel within an existing Team.

About Your Team Groups

Open Teams on your desktop and look for an app in the app bar similarly called Teams. These “Teams in Teams” are a workspace tool for you and your colleagues, connecting anyone who is a member or owner together in a Microsoft 365 group. These Teams are a great space for you and your coworkers to access shared content and collaborate using a variety of tools.

By the way, to avoid confusion, I am going to refer to these as Team groups here to distinguish them from the broader Teams application.

Using Team Groups for Work Projects

Team groups are a natural go-to tool for work projects, and for good reason. They reduce the never-ending back and forth of emails and the inevitable duplicate files this creates, they make sharing simpler, and they can even incorporate some additional apps, like Microsoft Lists and Microsoft Forms, to help you stay organized.

“So,” you think to yourself, “my colleagues and I are about to embark upon a spring project, I think I will create a new team to keep us organized.” Great thinking! But before you do, I have one consideration for you…

Team Channels

Channels are created within a Team Group to help you further organize content intended for the same Team Group membership. Within your Team group, look on the lefthand menu for the existing channels in your current Team.

Each Team group comes with at least one channel, called General.

Channels allow you an additional level of organization within your Team. You will notice, as you navigate between channels, they each have unique posts, files, and tab structures. So, they can be customized to be highly unique from one another.

New Channel vs. New Team

Okay, back to your situation, you are embarking on a spring project, do you make a new Team or a new Channel within an existing Team?

Who is involved in the project?

Here is my advice: think about who is involved in this project. Are the collaborators all people who are already members of an existing Team with you? If so, the organization strategy for this project might be better served with a channel within an existing Team.

Think about your Team group as your virtual office space. You would probably not look for new physical office space for each new project with the same colleagues… You would likely use the same storage spaces and meet in the same conference rooms to handle a variety of projects. The same concept applies for your virtual office spaces, like Teams groups.

At their heart, Team groups are intended to identify a specific group of people who regularly work together, by nature of their position or across divisions in collaborative projects. So, these groups are all about the people who should have access to the content, not the content itself. The channels, on the other hand, are all about the content.

Side note

Are there exceptions? Of course! Is this a rule? Do you have to create channels instead of a new Team? No way! But I did want to remind you about channels, because there is a tendency to create a new Team group when using an existing Team might simplify things and keep your ever-expanding Team list a little more manageable.

Creating a Channel

Creating a channel is easy, and it is something we do together in both Teams Essentials and Teams Advanced training.

1. Open your Team group and look for the more (…) menu next to your Team group name.

2. Select Add Channel.

3. You have a variety of customization options, and I will leave most of those to the full trainings, but one that may interest you is the ability to create a Private channel. A private channel is only viewable by certain specified members of your Team.

Upcoming Training Opportunities

If this has inspired you to learn more about Team Channels, you might be interested in attending Teams Essentials and Teams Advanced training.

Visit myTraining for available times and to sign up for a session. Also, I mentioned Microsoft Lists as a fantastic Teams tool: look for Microsoft Lists Essentials if you are interested in learning more (this one is super cool, and a lot of people don’t know about it).

Microsoft Cloud Power User Digital Credential

Coincidentally, all three of those trainings will get you halfway to WSU’s Microsoft Cloud Power User Digital Credential . Just saying…