Modern SharePoint - I Export List to Excel But Can’t Calculate Numbers

%3CLINGO-SUB%20id%3D%22lingo-sub-1428120%22%20slang%3D%22en-US%22%3EModern%20SharePoint%20-%20I%20Export%20List%20to%20Excel%20But%20Can%E2%80%99t%20Calculate%20Numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1428120%22%20slang%3D%22en-US%22%3E%3CP%3EWhenever%20I%20export%20to%20excel%20using%20Modern%20SharePoint%2C%20it%20exports%20as%20a%20iqy%20(which%20I%E2%80%99m%20not%20sure%20what%20that%20means).%20I%20save%20as%20an%20Excel%20doc%20but%20for%20some%20reason%20none%20of%20the%20numbers%20can%20be%20used%20to%20calculate%20anything.%20Is%20there%20some%20setting%20I%20need%20to%20change%3F%20Am%20I%20doing%20something%20wrong%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1428120%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1428531%22%20slang%3D%22en-US%22%3ERe%3A%20Modern%20SharePoint%20-%20I%20Export%20List%20to%20Excel%20But%20Can%E2%80%99t%20Calculate%20Numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1428531%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F684035%22%20target%3D%22_blank%22%3E%40PowerApp400%3C%2FA%3E%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBefore%20exporting%20the%20Excel%20list%20from%20a%20Modern%20SharePoint%20site%2C%20verify%20that%20each%20list%20column%20has%20been%20defined%20in%20the%20correct%20column%20type%20format%20('Number'%20column%20type%2C%20instead%20of%20'Single%20line%20of%20text').%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrior%20to%20the%20actual%20export%20action%20from%20the%20modern%20SharePoint%20list%2C%20you%20should%20be%20able%20to%20verify%20your%20list%20settings%20on%20your%20site%20(via%20Settings%20-%20List%20settings)%20assuming%20you%20have%20the%20proper%20administrative%20permissions%2C%20otherwise%20List%20Settings%20may%20not%20appear%20for%20you).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20List%20Settings%20in%20the%20'Columns'%20section%2C%20your%20column%20types%20for%20calculation%2C%20should%20appear%20in%20a%20'Number'%20column%20format.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20column%20type%20got%20lost%20after%20the%20export%20action%2C%20most%20likely%20either%20the%20file%20extension%20has%20been%20changed%20to%20an%20unsupported%20file%20extension%20type%20or%20the%20column%20types%20have%20not%20been%20exported%20as%20a%20%22number%22%20type.%20Make%20sure%20you%20don't%20change%20the%20proposed%20.iqy%20extension%20when%20working%20with%20Microsoft%20apps%20like%20Excel.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20save%20your%20Excel%20file%20with%20the%20correct%20column%20type%20formats%20used%2C%20in%20the%20default%20file%20format%20(*.iqy)%20and%20use%20a%20supported%20tool%20(such%20as%20Excel)%20to%20open%20it%20(just%20double-click%20the%20file%20with%20the%20*.iqy%20file%20extension)%2C%20you%20should%20be%20able%20to%20use%20the%20file%20as%20expected.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Whenever I export to excel using Modern SharePoint, it exports as a iqy (which I’m not sure what that means). I save as an Excel doc but for some reason none of the numbers can be used to calculate anything. Is there some setting I need to change? Am I doing something wrong?

 

Thanks for your help!

1 Reply
Highlighted

Hi @PowerApp400

 

Before exporting the Excel list from a Modern SharePoint site, verify that each list column has been defined in the correct column type format ('Number' column type, instead of 'Single line of text'). 

 

Prior to the actual export action from the modern SharePoint list, you should be able to verify your list settings on your site (via Settings - List settings) assuming you have the proper administrative permissions, otherwise List Settings may not appear for you).

 

In the List Settings in the 'Columns' section, your column types for calculation, should appear in a 'Number' column format. 

 

If the column type got lost after the export action, most likely either the file extension has been changed to an unsupported file extension type or the column types have not been exported as a "number" type. Make sure you don't change the proposed .iqy extension when working with Microsoft apps like Excel. 

 

If you save your Excel file with the correct column type formats used, in the default file format (*.iqy) and use a supported tool (such as Excel) to open it (just double-click the file with the *.iqy file extension), you should be able to use the file as expected.