Percentages after PDF to Excel go wrong in two ways: a rate arrives as the number 5, so applying the Percentage format shows 500%, or it arrives as the text "5%", which will not calculate. Divide whole-number rates by 100, convert percent text with VALUE, then apply the Percentage format to the result.
Why does 5% become 500% after PDF to Excel?
A rate becomes 500% because the Percentage format multiplies the stored value by 100 for display. If the converter wrote the rate as 5 rather than 0.05, the cell holds five whole units, and formatting it as a percentage shows 500%. The value was wrong before the format touched it.
Microsoft's page on formatting numbers as percentages in Excel describes this directly. When you apply the format to cells that already hold numbers, Excel multiplies those numbers by 100, so 10 becomes 1000.00%. Numbers typed into a cell after it was formatted as a percentage follow a different rule: 10 and 0.1 both show as 10.00%.
That difference explains why a converted column can look half right. Rates you retype by hand come out as expected. Whole-number rates the converter wrote before you formatted the column are a hundred times too large.
Percentages stored as text with a trailing percent sign
A rate that arrives as text, such as "24.99%", sits at the left of the cell and is skipped by SUM, AVERAGE and other range functions. Converters often keep the percent sign because a PDF stores it as just another character on the line. The result looks like a percentage but behaves like a label.
Signs that a rate column is text:
- Values sit against the left edge of the cell.
=ISNUMBER(C2)returns FALSE.=SUM(C2:C20)returns 0 even though the cells show rates.
Text rates often carry other characters too: a trailing space, a non-breaking space before the sign, or a label such as "APR". Strip those first. Statements that mark amounts with a CR or DR suffix have the same text problem in the amount column, and turning CR and DR amounts into signed numbers handles that case.
Converting percentage text to real rate values
VALUE turns percent text into the decimal it stands for. =VALUE("5.25%") returns 0.0525, because Excel reads a trailing percent sign as a division by 100. Put the formula in a helper column, check a few results, then paste the helper as values over the original column.
The table below shows examples of what can arrive and the formula that fixes each. The values are made up, and C2 is the converted cell.
| Arrives as | Means | Formula | Result before formatting |
|---|---|---|---|
| Text "5.25%" | 5.25 percent | =VALUE(TRIM(C2)) |
0.0525 |
| Number 5.25 | 5.25 percent | =C2/100 |
0.0525 |
| Text "5.25 %" with a non-breaking space | 5.25 percent | =VALUE(SUBSTITUTE(C2,CHAR(160),"")) |
0.0525 |
| Text "APR 24.99%" | 24.99 percent | =VALUE(TRIM(SUBSTITUTE(C2,"APR",""))) |
0.2499 |
| Number 0.0525 | 5.25 percent | No change | 0.0525 |
After conversion, select the column and apply the Percentage format from the Home tab, with two decimal places if the PDF printed two. Each cell now shows the rate as printed and holds the value formulas need.
Rates printed once for several rows
Rate tables in loan or savings statements often print a rate once and leave the next rows blank until it changes. Fill those gaps before converting, so each row carries its rate. The method in filling down blank dates in Excel works the same way for a rate column.
Should a rate column hold 5 or 0.05?
A rate column should hold 0.05 for five percent. Formulas expect that form: =B2*C2 with a balance in B and 0.05 in C returns five percent of the balance. With 5 in C, the same formula returns five times the balance, and every interest calculation built on it is wrong by a factor of 100.
Keeping the value as a decimal and letting the format add the sign also keeps the sheet honest. A reader sees 5.00%, a formula sees 0.05, and nobody has to remember to divide.
If a column must stay as whole numbers, for example to match a system that expects 5 for five percent, label the header clearly, such as "Rate (percent, not decimal)", and do not apply the Percentage format to it.
Rates spread across page sheets
When a converter puts each page on its own sheet, a rate column fixed on one sheet stays broken on the others. Combining PDF pages onto one Excel sheet lets you fix the whole column once.
Percent formatting that hides rounding in converted rates
A converted rate holds only the digits the PDF printed, so a rate shown as 5.3% may stand for 5.25% or 5.349%. The Percentage format can also hide digits that are there: a cell holding 0.05249 formatted with one decimal shows 5.2%. Check what a cell holds in the formula bar, not what it displays.
Two habits avoid surprises:
- Do not recompute interest from a rounded rate. If the statement prints both a rate and an interest charge, use the printed charge. A rounded rate times the balance rarely matches the bank's figure to the cent.
- Match the decimal places to the PDF. Format the column with the number of decimals the statement printed, so the sheet does not suggest more precision than the source has.
Rates buried inside sentences, such as "Your rate changed to 4.75% on March 1", need extracting before any of this applies; extracting text from a PDF to Excel covers pulling a value out of a line of prose.
FastBankConvert is built for the transaction lines of a statement, with dates, descriptions, amounts and balances in their own columns, and it is getting ready to open. You can ask to hear when trial conversions start at Get started.