Shahriar Abrar Rafid
When you prepare, access, or publish a Microsoft Excel workbook, it can have content that you do not wish to expose to others while sending the workbook online. Metadata is the technical term for this type of data. We can use this metadata for multiple objectives. In this article, we’ll discuss what is metadata in Excel. Also, we’ll demonstrate various ways to view, edit, remove, and protect the metadata of our Excel workbook. So, let’s go through the article thoroughly to clear up the concept of metadata in Excel.
What Is Metadata in Excel?
If you aren’t a native user of Excel, then one question must come to your attention: “What is metadata in Excel?”. Don’t worry! We’ve got you.
Metadata, commonly referred to as “document properties“, are informational elements that identify or characterize a file. They contain information that identifies the subject or contents of the document, such as the title, author, subject, and key phrases. You can quickly arrange and categorize your files later on if you include the document properties. You can also insert documents into your papers or search for them based on their properties.
Four different types of document properties exist. See them below.
- Standard properties
- Automatically updated properties
- Custom properties
- Document library properties
How to Add Metadata in Excel
When importing data from the CSV files to Excel, you might discover that certain sales or accounting systems’ CSV files occasionally have metadata at the beginning.
Perhaps you should add this metadata to a new column. You would eliminate the top rows that include the metadata before promoting the first row of the report data to column headers while importing the data with the Power Query feature.
Here, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience.
However, let’s follow the steps below carefully.
📌 Steps:
- At the very beginning, open a new workbook.
- Then, go to the Data tab.
- After that, click on From Text/CSV on the Get & transform Data group of commands.
Immediately, the Import Data window appears before us.
- Afterward, select the preferred CSV file which you want to import to Excel.
- Following this, click on Open.
Now, we can see a preview of our dataset.
- Then, click on the Transform Data button.
At this time, we have to add a new column in the data range.
- First, navigate to the Add Column tab.
- Then, click on Custom Column on the General group.
Instantly, the Custom Column dialog box pops up.
- In the New column name box, write down Account Name.
- Then, insert the following formula in the Custom column formula box.
=if [Column1]= "Account" then [Column2] else null
- As usual, click OK.
As a result, we can see the new column beside Column4.
Thenceforth, we need another column.
- So, repeat the above steps to bring the Custom Column dialog box again.
- Again, write down Account in the New column name box.
- In the Custom column formula box, insert the following formula.
= if [Column1]= "Sales Rep" then "Account" else [Account Name]
- As always, click OK.
Another new column is visible in the editor. This is the main column for which we open the file in the editor.
Now, we have to delete the previously created helper column.
- Firstly, right-click on the column header Account Name.
- Secondly, select Remove from the context menu.
At this point, the data looks like the following.
Then, we need to delete the rows above Row 5. To do this,
- First, move to the Home tab.
- Then, click on Remove Rows drop-down.
- After that, select Remove Top Rows from the drop-down list.
Suddenly, the Remove Top Rows input box opens up.
- Here, write down 4 in the Number of rows box.
- Following this, click OK.
Presently, we have to make Row 1 the header row.
- So, click on the drop-down icon beside Use First Row as Headers.
- From the list, select Use First Rows as Headers.
The final look in the Power Query editor is just like the following image.
- Currently, click on Close & Load to export this to our worksheet.
After some sort of formatting, the final result is before our eyes.
We can see the Account in Column F for every Sales Rep.
Read More: How to Create Excel File from Metadata with CSV File
3 Methods to Explore Metadata (Metadata Viewer) in Excel
You may not know where we can view the metadata of your Excel file. We can do this work in multiple ways. Here are three different but simple methods for viewing metadata in Excel. So, let’s explore them one by one.
1. Using File Tab
In our first method of exploration, we’ll get the help of the File tab. Using this we’ll see the document properties AKA metadata in the Info options. It’s simple and easy. Just follow along.
- First of all, proceed to the File tab.
- Then, click on Info on the left side pane.
On the Info option screen, you can see the metadata of this document on the right side of the display.
Read More: How to Create Metadata in Excel
2. Utilizing Properties Wizard
In this approach, we’ll use the Properties wizard. Follow the below steps to understand the matter better.
- Firstly, get to the Properties panel like the previous method.
- Then, click on the Properties drop-down.
- After that, select Advanced Properties from the one-item list.
Immediately, we can see the Properties dialog box for our Excel file. Here we can find the details of the metadata of our workbook.
Read More: How to Show Excel Metadata Viewer
3. Employing Windows Explorer
There is one simpler and more useful trick for viewing Excel metadata that involves using Windows Explorer. So, let’s see it in action.
📌 Steps:
- Firstly, navigate to your file location.
- Then, right-click on the file name.
- Later, select Properties on the context menu.
Instantly, the Properties wizard appears before us.
- Thus, click on the Details tab. And, you will be able to see the details metadata of the Excel file.
How to Apply Metadata Editor in Excel
In the intro, we committed to disclosing how we can edit metadata in Excel. Now, we’ll show how to alter three different metadata fields in our Excel file. So, without further delay, let’s dive in!
🔄 Add an Author
Here, we’ll demonstrate how we can add multiple authors to one document. Let’s see the process in detail.
📌 Steps:
- Like before, navigate to the Properties panel.
- Then, click on the box Add an Author under the Related People section and write down the preferred author name that you want to add.
- After that, click anywhere on the display except this box.
Here, you can see another author’s name under the default name.
Read More: How to View Properties Dialog Box in Excel
🔄 Modify Default Name of Author
In this section, we will learn how to modify the default author name in Excel. So, let’s see how we do it.
📌 Steps:
The default author name is always the Windows username. However, it is not always possible to serve in this manner. So, we need to change it.
- First, jump to the File tab.
- Secondly, click on Options at the bottom of the display.
Suddenly, the Excel Options window emerges before us.
- Firstly, go to the General tab.
- Here, you can change the User name under the Personalize your copy of Microsoft Office section.
- As always, click OK.
Then you’ll realize how simple the situation is.
Read More: How to Edit Document Properties in Excel
🔄 Create Custom Properties
For your Excel document, you can define more properties. Actually, we call it custom properties. To make it happen, adhere to the guidelines below.
📌 Steps:
- Like before, bring the Properties wizard of the Excel document.
- Then, go to the Custom tab.
- After that, choose a Name for the custom property. In this case, we chose Language.
- Later, select the Type of property. Here, we selected Text.
- In the Value box, we wrote down English. It’s the language of the document and the value is in Text format. These Type and Value fields must match each other.
- Lastly, tap the Add button.
Instantly, we can see the recently added properties in the preview section.
- To close, click OK.
Read More: How to Remove Last Modified By in Excel
How to Remove Metadata from Excel File
Here, we’ll discuss how to remove metadata from an Excel file. Allow me to demonstrate the process below.
📌 Steps:
- Initially, go to the Info option like before.
- Then, click on the Check for Issues drop-down.
- From the list, select Inspect Document.
As a result, the Document Inspector opens.
- Currently, click on the Inspect button at the bottom.
After inspection, we find information in the Document Properties and Personal Information.
- So, click on Remove All.
Look, there is no information on metadata in this file.
You can check these by going to the Properties panel.
How to Protect Metadata in Excel?
If you don’t want other individuals to edit any document properties or anything else in your Excel file, you can employ the protection of metadata and confidential information. Let us go through the steps below.
📌 Steps:
- Again, navigate to the Info options.
- Now, click on the Protect Workbook drop-down icon.
- From the drop-down list, choose Mark as Final option.
It will show a warning box saying, “This workbook will be marked as final and then saved”.
- Here, click on the OK option.
Now, nobody can change or edit the metadata info of this workbook.
Download Practice Files
You may download the following Excel workbooks for better understanding and practice yourself.
Conclusion
This article explains what metadata is and how to add, edit, remove, and protect it in Excel in a simple and concise manner. Don’t forget to download the practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.
