Excel Community: Simplifying Spaces and Labels
Hi all, As you may have noticed already, we as the Excel Team have done a bit of "spring/summer cleaning" for the community. We have received feedback that the number of "spaces" was simply too many at nine, so we have pared things down. Now, there are three community spaces: Excel: this will be the primary place for posting content, as many of the old spaces have been migrated as labels will find a good home here BI and Data Analysis: this will be a good place for posting about tasks and questions that cut across Excel, Power BI, and other topics in this realm Resources and Community: this will increasingly become a place for folks to share sample files and templates with each other Q: What happened to the other spaces that used to exist? A: They have since been rolled up as "Labels", which you can find in the "More Resources" sidebar under "Labels" of any of the three spaces mentioned above. Look for these on the right side: Please let us know if you have any other questions. Thanks for reading!Have an Excel question?
Ask the Excel team anything. Get your questions ready for the Excel team! Join our Reddit AMA this Thurs. November 17th at 12pm EST. https://www.reddit.com/r/excel/comments/5aonww/microsoft_excel_product_team_ama_november_17th/ Related Excel Resources: Besides this community we also have a large set of related resources for Excel. From training videos to Hands on labs, from support to feature request. Here are the top links for Excel: Excel Basics Guided Learning: Excel LinkedIn Learning Videos Excel Documentation More Excel Learning YouTube Videos Excel Intermediate-Advanced Guided Learning: Data Analysis using Excel at edx.org Analyzing and Visualizing Data with Excel at edx.org Excel Hands On Labs Analyzing Data in Excel Hands On Labs Get and Transform Data Hands On Labs Excel Data Model Hands On Labs Support Documentation: Excel Documentation What is new in Excel Product and bug support: Excel Support Community: Excel Community Request a Feature: Excel Feature Requests Blog: Excel Blogs Demo Files: Excel Demos Download trial version of Excel Try Power BI for pre-built dashboards On Demand Excel session from Microsoft Ignite: Excel sessions at Microsoft Ignite 2016Complex numbers in Excel
Did you know that Excel can handle complex mathematical operations with complex numbers? Complex numbers, expressed as "a + bi," where 'a' and 'b' are real numbers and 'i' is the imaginary unit, can be powerful tools in various scenarios. Think engineering, physics, signal processing, or even financial modelling. Excel offers native support for complex numbers, and you can perform basic operations like addition, subtraction, multiplication, and division just as easily as with real numbers. If you haven't explored this yet, give it a shot! It's as simple as entering a formula like =IMSUM(A1:B1) or =IMPRODUCT(A1:B1) One nifty feature is the ability to convert complex numbers from rectangular to polar form and vice versa. This can be a game-changer in certain calculations, especially when dealing with phase angles or magnitude-based analyses. For this you can make use of the =IMABS(A1) and =IMARGUMENT() functions. As complex numbers are considered to be text within Excel you will need special functions to perform arithmetic with them, such as IMSUM, IMSUB, IMPRODUCT, and IMDIV. Excel can help you plot complex numbers on the complex plane using the XY Scatter chart! you will need the IMREAL and IMAGINARY functions to extract the real and imaginary coefficients from the complex number to use them to plot the numbers. This feature can be invaluable when dealing with complex data sets or analysing the behaviour of complex functions. If you're interested in learning more about complex numbers, I made a video covering all functions that work with complex numbers in Excel: https://www.youtube.com/watch?v=_A2DIUibkmk Have you worked with complex numbers before in Excel? What specific use case did you have? Were there any obstacles you had to overcome?Generally available on mobile: Cards View in Excel
Cards View enables you to view and edit a row of data from an Excel spreadsheet in a format that fits within your phone’s screen. When viewing an Excel spreadsheet in the new Office mobile app or the Excel mobile app, tap on the Card View icon in the bottom left corner of the screen and you will see the a collection of cards, with each card containing the data from each row of the spreadsheet. Click here to learn more!
Excel iOS app requires enabling Connected services for CSV files
I recently encounted this issue with CSV files on Excel iOS app (app version 2.74). When trying to open a CSV file located on iCloud Drive, OneDrive or device itself, the Excel requires to enable 'Connected services' setting 'Analyze content'. If you don't allow that (or reject making any setting), the CSV file opens as blank in Excel app. If you enable 'Analyze content' setting, then CSV file opens into Excel normally (in read-only mode, which ok and documented feature). Issue is reproducable with any kind of CSV file. I personally made the most simple CSV file with my Excel Windows version having just 1 row and two columns with data 'Data1' and 'Data2'. Moved this CSV file to OneDrive and accessed it from ipad and iphone with Excel app with given results: a 'Connected services' popup comes and if reject that or going to setting and do not enable 'Analyze content', the CSV file kind of opens but is totally blank (though not looking as normal blank sheet) and not editable. According to Microsoft documentation of about connected services https://learn.microsoft.com/en-us/DeployOffice/privacy/connected-experiences#connected-experiences-that-analyze-your-content , CSV file types should not be part of 'Connected services' content. Thus this is either a documentation fault, or error in the Excel iOS app behaviour. On Windows version of Excel, CSV files can be used normally as ever. All other normal Excel formats can be opened normally on Excel iOS app version without any 'Connected services' popup. I have a privately owned Office 358 subscription and am a normal private consumer. I did contact Microsoft office Chat support today (caseId 1055025104) and agent also considered this is a bug in Excel iOS version but recommeded to write to this forum. All my devices are up-to-date. So the workaround is to enable 'Analyze content' in the 'Connected services' settings, but that is not mentioned in the documentation and in general I don't want Microsoft to analyze my content especially with such a primitive file content type. A typical scenario for consumers could be similar to my case where I encountered this issue: I can monitor my home electricity consumption on electricity operator company's website from where data can be downloaded to my device as CSV files (no option to choose format) for my further processing.
Formula Description Excel iPad/ios
I noticed that the iPad app on excel does not have formula description text boxes once you type a formula. If I start typing any formula on the desktop Excel, let's say the PMT formula. It requires several cells to be referenced but if I use excel on iPad, I don't know what order it needs to be. If I type "=PMT(" on the desktop Excel, a small rectangular text box will appear just below where I am typing the formula and it would read "PMT(rate,nper,PV,FV,type)", which lets me know the order of which cells to reference. On the iPad version, no such box appears.SOLVED - Split data in dynamic/spill array by delimiter
Greetings everyone, I have a calculation tool for Minecraft items that lists data from minecraft.fandom.com/wiki/Crafting and does various things with that data. I cannot get the queried data to spill properly because I don't know how. I have a query to minecraft.fandom.com/wiki/Crafting/Bulding_blocks (to name one) that excludes the "Crafting recipe" column and the "Description column" (in Power Query Editor), on a sheet called Crafting & Ingredients Query. The data's listings sometimes show a choice of materials in the game by using an " or ". The cells display this with character 10 from the website. I need a spill formula to separate and then continue the spill down for each occurrence of the " or ". I also need to show the same "Ingredient" on the right of those separations. On a test sheet in range $B$4, I have a spill formula that simply references and filters the blanks of the Name column from the query on the Crafting & Ingredients Query sheet ('Crafting & Ingredients Query'!$B$5:$B$2000). In in range $D$4 of the test sheet, I am attempting to make a spill formula that will 1), remove all instances of " or " (CHAR(160)&"or"&CHAR(10)) then 2), display each item after the cell's delimiter ("or") sequentially underneath in the spill, and then 3) display the same Ingredient next to each item of the items that are transposed down. Spill Formula so far (separates each item by the delimiter of Character 160, up to 10 delimiters for now): Current Formula (spill from range $D$4 - incomplete) =MID(SUBSTITUTE($B4:$B2000,CHAR(160),REPT(" ",LEN($B4:$B2000))),(TRANSPOSE(SEQUENCE(10,1)-1))*LEN($B4:$B2000)+1,LEN($B4:$B2000)) (Original formula credit: https://exceljet.net/formula/split-text-with-delimiter) Notes: • The =SEQUENCE() function is not dynamic (is just 10) and is temporary. Once each cell that has a delimiter is displayed underneath, the spill should be only 1 column wide. • I want the number of delimiters a cell can have to be dynamic (no limit - infinite, 1 or 7 or 100 or whatever). • Attached is the file with the relevant data. I would be very grateful for any solution to make this spill formula happen. Thank you!
