You are working on your website and you want to manage your categories.
Imagine you have all your categories in your Excel spreadsheet in one column in (A).
However, you now want to make them all sub-categories and want to add a Main Category to groups of these products. So you need to add a Parent category to selected groups of products.
Step-by-Step Guide
Open Your Excel File: Open the Excel file where you have all your categories in column A.
Insert a New Column: Insert a new column next to your category column to input the parent categories.
Add Parent Categories:
- In the new column, enter the parent categories corresponding to each group of sub-categories.
- For example, if you want to group certain products under “Main Category 1” and others under “Main Category 2”, fill the parent category column accordingly.
Combine Category and Parent Category:
- In another new column (let’s call it column C), use a formula to combine the category and parent category into the same cell.
Example
| A | B | C |
| Restoration & Care | Main Category | |
| Cleaning Supplies | Main Category 1 | |
| Tools & Equipment | Main Category 2 | |
| Hardware | Main Category 2 |
After applying the formula in column C:
| A | B | C |
| Restoration & Care | Main Category | Restoration & Care, Main Category 1 |
| Cleaning Supplies | Main Category 1 | Cleaning Supplies, Main Category 1 |
| Tools & Equipment | Main Category 2 | Tools & Equipment, Main Category 2 |
| Hardware | Main Category 2 | Hardware, Main Category 2 |
Applying the Formula
Enter the Formula in Column C:
- In cell C1, enter the following formula to combine the values from column A and column B:
=A1 & “, ” & B1
- In cell C1, enter the following formula to combine the values from column A and column B:
Drag the Formula Down:
- Click on the bottom right corner of cell C1 (where there is a small square) and drag it down to apply the formula to all rows.
Steps:
Insert a New Column: Insert a new column B next to your categories in column A.
Enter Parent Categories: Enter the parent categories in column B next to the corresponding sub-categories.
Use a Formula to Combine: In column C, use the formula to combine the category and parent category.
Example Spreadsheet
Before:
| A | B |
|---|---|
| Restoration & Care | Main Category 1 |
| Cleaning Supplies | Main Category 1 |
| Tools & Equipment | Main Category 2 |
| Hardware | Main Category 2 |
Formula:
In cell C1, enter:
=A1 & “, ” & B1- Drag the formula down to apply it to all rows.
After:
| A | B | C |
|---|---|---|
| Restoration & Care | Main Category 1 | Restoration & Care, Main Category 1 |
| Cleaning Supplies | Main Category 1 | Cleaning Supplies, Main Category 1 |
| Tools & Equipment | Main Category 2 | Tools & Equipment, Main Category 2 |
| Hardware | Main Category 2 | Hardware, Main Category 2 |
This will concatenate the category and parent category into a single cell in column C.
Final Steps
Copy and Paste as Values (Optional):
- If you want to remove the formulas and keep only the text, copy the new column C.
- Right-click and select
Paste Special > Values.
Remove Original Columns (Optional):
- You can now delete the original columns A and B if needed.