When using the PIVOTBY function, 3 common issue types come up most often: error handling, unexpected results, and filling the formula across cells; this page includes 3 common Q&As.
FAQ
What should I do if PIVOTBY shows a #VALUE! error?
- Check whether the data types in the parameters are correct.
- Make sure numeric parameters do not include text or extra spaces.
- If needed, clean the source data first and then enter the formula again.
Why does the PIVOTBY result not match what I expected?
- Check whether the referenced range is correct.
- Confirm that the related cells are formatted as numbers instead of text.
- Use formula evaluation on the Formulas tab to inspect the calculation step by step.
How do I use the PIVOTBY function in multiple cells?
- Enter and confirm the formula in the first cell.
- Drag the fill handle at the lower-right corner of the cell across the target range, or copy and paste the formula into other cells.
- Check whether relative and absolute references are set as expected.
Glossary
| Term | Definition |
|---|---|
| Insert Function | The entry used to search for and insert a function from a dialog box. It is useful when you are not familiar with the function name or parameters. |
| Formula bar | The formula input area above the worksheet where you can view and edit the current cell formula directly. |
| Function arguments | The input items required for a function to run. Different arguments affect the final calculation result. |