The prompt
Score 10 of 10, Excellent
Use it when Your data has months, sites or products as columns, and you need one row per item per period so pivot tables, filters and charts work.
- {{app_and_version}}
- {{table_description}}
- {{column_roles}}
- {{next_step}}
- {{sample_rows}}
Full prompt and why it works
Prompt
App and version: {{app_and_version}} What the table holds: {{table_description}} Which columns identify each row, and which are repeated periods or categories: {{column_roles}} What I want to do with it next: {{next_step}} Headers and three sample rows, copied exactly: """ {{sample_rows}} """ Task: Show me how to turn this wide table into a long one in {{app_and_version}}, with one row per item per period or category, so that I can do the next step above. The original table should stay untouched. Format: 1. Target layout: the new column headers and the first six rows the reshaped table would have from my sample, as a small table, so I can see what I will get. 2. Recommended method for my app, with one sentence on why: for example Unpivot Columns in Power Query, a formula using TOCOL and HSTACK in Excel for Microsoft 365, or a FLATTEN or SPLIT formula in Google Sheets. 3. Numbered steps, up to 10, with menu names as they appear in the app, or the formula in a code block with each part explained in a plain sentence. 4. Clean-up: making the period column a real date or a sortable label, keeping empty cells empty rather than zero, and leaving out total rows or columns so nothing is counted twice. 5. Check it: the sum of the value column in the new table should equal the sum of all value cells in the original; give both formulas. 6. Updating: what to do when a new month or column is added. Requirements: - Only features of {{app_and_version}}; no macros. Do not change or overwrite the original table. - Under 400 words, plus formulas. - Leave total and subtotal rows and columns out of the reshaped data, and list any you find in the sample. Example of the target layout's form only: "Site | Month | Sales", with rows such as "North | Jan 2025 | 1,200". If it is not clear which columns identify a row and which hold values, say what you assumed and how the result changes if I meant it the other way.
Variables
- {{app_and_version}}
- For example "Excel for Microsoft 365" or "Google Sheets".
- {{table_description}}
- For example "monthly sales by store, one row per store, one column per month".
- {{column_roles}}
- For example "Store and Region identify the row; Jan to Dec are values".
- {{next_step}}
- For example "build a pivot table by region and quarter" or "chart each store over time".
- {{sample_rows}}
- The header row and three data rows, including any total row or column.
Why it works
- An example. It shows the first six rows of the reshaped table from your own sample before any steps, so you can confirm the layout is what you need.
- What good looks like. The value column's total in the new table is checked against the total of the original, which proves nothing was lost or counted twice.
- What to avoid. Total and subtotal rows are kept out of the reshaped data, the usual reason a pivot table built on it shows double the real figures.