How to Fix Missing Borders When Converting Excel XLS to XLSX
Question details
Users experience missing or incorrect borders resulting from conditional formatting when converting older Excel 97 (XLS) files to the modern XLSX format.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Converting or opening older legacy .xls workbooks in newer versions of Excel.
- Observed behavior
- Conditional-formatting borders may disappear, render incorrectly, or overlap after the file format conversion is completed.
Always create a backup copy of your original .xls file before making changes. This ensures your legacy data and original formatting rules remain safe during the conversion and troubleshooting process.
Review and Rebuild Conditional Formatting Rules
Clear outdated or overlapping conditional formatting rules and recreate them in the new format to ensure compatibility.
Older Excel 97-2003 formats process conditional formatting differently than modern versions. Converting these files can cause rules to duplicate or conflict, causing borders to fail to render.
Open the original XLS file in Excel and navigate to the worksheet displaying the missing borders.
Navigate to the 'Home' tab, click on 'Conditional Formatting' in the Styles group, and select 'Manage Rules'.
Review the list of existing formatting setups. Delete any duplicated or conflicting legacy rules that might cause rendering issues.
Create new, clean border rules specifically optimized for the modern format. Once applied, click 'File' > 'Save As' and select XLSX or XLSM.
Check the Print Preview to ensure your newly configured borders display correctly on both the screen and the printed page.
Replace Conditional Borders with Direct Formatting
If conditional formatting is not strictly necessary, apply static borders directly to the cells to guarantee they render correctly.
Fix Missing Excel Borders with WPS Spreadsheet
WPS Office offers high compatibility with both older XLS and modern XLSX formats, often bypassing rendering issues found when transitioning legacy files. You can easily manage conditional formatting and apply reliable borders using WPS Spreadsheet.
- 1. Open the legacy file: Launch WPS Spreadsheet and open your original XLS workbook.
- 2. Manage formatting: Navigate to 'Home' > 'Conditional Formatting' > 'Manage Rules' to review and easily clean up legacy border rules.
- 3. Apply standard borders: If rules overlap heavily, clear them and use the standard 'Borders' tool under the Home tab for stable cell formatting.
- 4. Save as modern format: Click 'Menu' > 'Save As' and choose the '.xlsx' format to secure your updated borders safely.
Frequently Asked Questions
Why do borders disappear when saving an XLS file as XLSX?
The file format transition changes how the spreadsheet software stores and renders conditional formatting rules. Older Excel 97-2003 (XLS) rules can conflict with the modern formatting engine in XLSX, leading to dropped or overlapping borders.
Can I recover borders without recreating the formatting rules?
Sometimes opening the file in Compatibility Mode retains the borders temporarily on screen. However, for a permanent fix in the new format, converting to static borders or rebuilding the conditional rules is highly recommended.
Are printed borders affected by this XLS conversion issue?
Yes, conditional formatting glitches can affect both the screen display and the physical print output. Always check the Print Preview after converting an XLS to XLSX to confirm your gridlines and borders render correctly before printing.
