How to Hide Excel Columns and Autofill Prices for Duplicate Items
Question details
The user needs a way to hide specific columns and automatically fill in prices for repeating items in an Excel worksheet.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Managing bids, product lists, or worksheets with duplicate line items where screen space needs optimization and pricing needs automated lookup.
- Observed behavior
- The user wants a streamlined view by hiding unnecessary columns and an automated method to populate identical items' prices without manual data entry.
Ensure your product list has a clear, standardized reference table containing unique item names and their corresponding prices before applying lookup formulas.
Hide and Unhide Columns in Excel
Easily manage your worksheet view by hiding unnecessary columns without deleting your underlying data.
Click on the column letter (e.g., column C) at the top of your worksheet that you want to hide.
Right-click the selected column letter and choose "Hide" from the context menu.
To unhide, highlight the columns immediately to the left and right of the hidden column (e.g., columns B and D).
Right-click the selection and click "Unhide" to make the hidden column visible again.
Autofill Prices for Duplicate Items Using Lookup Formulas
Use Excel's powerful lookup functions to automatically populate prices based on item names, saving time and preventing manual errors.
Efficiently Manage Worksheets and Formulas in WPS Spreadsheet
WPS Spreadsheet offers a powerful and familiar interface to effortlessly hide columns, manage large datasets, and use advanced formulas like VLOOKUP and XLOOKUP to autofill duplicate item data.
- 1. Open your workbook: Launch WPS Spreadsheet and open your existing Excel workbook containing your product lists.
- 2. Hide unused columns: Right-click any column header you wish to hide and select "Hide" from the dropdown menu to streamline your view.
- 3. Insert lookup formula: Type `=VLOOKUP(` or `=XLOOKUP(` in your target cell to activate the intuitive formula hints and syntax guides.
- 4. Autofill your prices: Select your lookup value and reference table, press Enter, and drag the fill handle down to autofill prices seamlessly.
Frequently Asked Questions
Can I hide multiple columns at once in Excel?
Yes, you can click and drag across multiple column headers to select them, then right-click any of the selected headers and choose "Hide" to hide them all simultaneously.
Why is my VLOOKUP returning an #N/A error?
The #N/A error usually means the item you are searching for does not exist in the first column of your lookup range, or there is a typo, formatting issue, or trailing space in your data.
How do I unhide column A if it is hidden?
To unhide column A, type "A1" into the Name Box (next to the formula bar) and press Enter. Then, go to the Home tab, click Format > Hide & Unhide > Unhide Columns.
Can VLOOKUP look to the left of the reference column?
No, VLOOKUP only searches the first column of the specified range and returns a value to the right. To look to the left, you should use XLOOKUP or an INDEX and MATCH combination.
