Forum Discussion
The visualization of the numbers in excel is not "correct"
To resolve this issue, you can may be try changing your regional settings in Windows.
Here's how:
- Click on the Start menu and type "Region" in the search box.
- Click on "Region settings" to open the Region settings window.
- Under the "Formats" tab, make sure the "Format" drop-down menu is set to "Spanish (Colombia)".
- Under the "Additional settings" section, click on the "Numbers" tab.
- In the "Decimal symbol" field, enter a comma (",").
- In the "Digit grouping symbol" field, enter a period (".").
- Click "Apply" and then "OK" to save the changes.
After changing these settings, try refreshing your PivotTable to see if the numbers display correctly. If the issue persists, try changing the format of the cells in the PivotTable to "Number" or "Currency" and adjust the decimal places as needed.
I hope this helps!
Hello there! Thank you for your answer, I really appreciate it, but unfortunately it didn't work 😞 The advanced settings in my computer are already like you said. I changed the format of my cells and refreshed the pivot table, but the problem still the same there and in my cells of the column from the database:
In that case, there are a few other things you can try:
- Check if the numbers are being imported into Excel as text. You can check this by selecting the cells with the numbers, right-clicking, and selecting "Format Cells". In the Format Cells dialog box, select the "General" category and click "OK". If the numbers change to the correct format, then they were being imported as text and you can try importing them again as numbers.
- Use the VALUE function to convert the text-formatted numbers to actual numbers. To do this, insert a new column next to the column with the text-formatted numbers, enter the formula "=VALUE(cell)" in the first cell of the new column (replace "cell" with the reference to the corresponding cell in the original column), and then drag the formula down to apply it to all the cells in the new column. Then, use the new column for your pivot table.
- Use Power Query to import the data and transform it before creating the pivot table. Power Query is a tool in Excel that allows you to import, transform, and clean data from various sources. You can use Power Query to convert the text-formatted numbers to actual numbers before creating the pivot table.
Here's how to do it:
- Click the "Data" tab and select "From Other Sources" -> "From Microsoft Query".
- In the "Choose Data Source" dialog box, select "Excel Files" and click "OK".
- In the "Connect" dialog box, select the Excel file containing the data and click "OK".
- In the "Query Wizard - Choose Columns" dialog box, select the columns you want to import and click "Next".
- In the "Query Wizard - Filter Data" dialog box, you can apply any filters you need and click "Next".
- In the "Query Wizard - Sort Order" dialog box, you can specify the sort order of the data and click "Next".
- In the "Query Wizard - Finish" dialog box, select "View data or edit query in Microsoft Query" and click "Finish".
- In Microsoft Query, select the column with the text-formatted numbers and click "Format" -> "Number".
- In the "Number" dialog box, select the desired format for the numbers and click "OK".
- Click "File" -> "Return Data to Microsoft Excel".
- In the "Import Data" dialog box, select where you want to place the data and click "OK".
- The imported data will now be in a table in Excel, which you can use to create your pivot table.
I'm at my and AI’s wit's end with this, hope it helps :).