While most in the USA are enjoying a long holiday weekend, another podcast looking at how data created in desktop Excel 2013 will render on the iPhone Excel. Among other things, they get an A+ for sorting, but an F for summing filtered data.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1745.
More Excel on the iPhone.
Hey welcome back to the MrExcel netcast.
I'm Bill Jelen.
It's July 5th.
I don't know if anyone's actually working today yes here's a big holiday in the United States picnics and fireworks and the whole bit I bet everyone took the day off.
So, hey we're gonna talk about the Excel on the iPhone again today, yesterday I guess I was pretty negative and I'm not I was hoping I was gonna be able to come in today and say something more positive I I wanted to test some real-life situations where we started out with an Excel file created in Excel 2013 on the computer and then took a look at how it looked on the iPhone.
So, here is the original data set we have a little slicer going on here you know which is new in Excel 2013, this is a table of control+T table name that table data save this out to the SkyDrive and open it all the data shows up the slicer is missing and the table functionality is missing but I still can see the data.
So, all right there we go, formatting yesterday complaining about four colors of fonts and four colors of fill.
If we used formatting on the computer and sent it down to the iPhone, while the formatting still appear and so, I tried all kinds of different things you're not just theme colors, but you know more colors and RGB colors and then some cell styles here, some various underlining here, like a double underline and a dotted border and I was really kind of evil and center cross selection and merge.
You know, my real hope when I started to do this was that when I open it on the iPhone over here the merge wouldn't work and then I'd have something positive say hey, at least the iPhone doesn't let you merge cells, but unfortunately they do let you merge cells and yes sir I complained that there weren't enough numeric formats on the iPhone.
Oh! Hey, if you create them on the computer they do all show up correctly on the iPhone.
So, this is 1234.56 with different formatting and it looks like they all came through even the comma K to show things in thousands date and time I couldn't figure out how to set up a time format on the iPhone, but if I created on the computer it does show up correctly on the iPhone.
That's a good thing.
All right, then I tried some, some evil things one of my favorite formulas in the whole world from February 17 1965 until today how many Friday the 13th are there we need to see the formula bar here.
So, we can see this view formula bar, using this is Mike Girvin's version of my formula.
So, it's not actually in a real formula, but the completely evil thing about it is that it's using the row of A2 and the row of B2 which are way beyond 16384 which is how many rows we have on the iPhone.
So, this thing came in the columns were not the right width and again I don't know how to fix it on the iPhone yet, but the right answer was there.
So, I was intrigued how are they getting the right answer and I wanted to come and change this date.
So, I clicked in here and you'll notice I now knows that now, that is grayed out in the form of the bar click, click, click, click, click, or tap, tap, tap, tap, they're not gonna let you in there and when I try to format another cell I get this message can't edit the workbook.
All right! So, I've used some rows that are beyond the 16384 and they're going to display it for me they're gonna give me the right answers give them credit for that, but then we're in can't edit mode.
I also tried a true array formula.
So, equal and MMULT to the multiplication table I suppose for my podcast a couple of weeks ago and again it's grayed out they don't even show me the formula there and they show me the value greyed out not allowed to edit.
Now, let's take a look at some of the features that they planned on working sorting, filtering, and outline.
All right, let's see how those work because I didn't get to those yesterday.
So, here's the data stuff that I started with on the computer.
I had groups A and B, I was hoping that's gonna be able to outline this like you could do with subtotals just one like that and then some Reps, and some Sales.
So, I could sort and filter, apply an icon set here, apply conditional formatting here, apply the sparkline, open it in Excel in the iPhone and what do we get.
Well, the data is there that's great, the icon set is gone, the conditional formatting is gone, the sparkline does not appear.
All right! Well, let's try their features.
So, it's underneath the eye, the eyeball the view features, right?
All right! So, the first thing I did was I tried outline, outline it's not like grouped outline had all Excel people know it's some weird thing where it shows me a list of all of the sheets and it would let me jump to a sheet I guess I only had one sheet.
So, the outline was really really boring.
So, click the down arrow to close that sort is fairly cool.
All right! So, I selected the data and they were able to figure out that the top row where the headings starts out with sort by none as soon as I open this flyout menu and choose sales and then descending look it sorts instantly I don't have to click OK or anything like that it just does the sort and then I went and chose rep and it's sort of backed by rep.
I kept thinking well I have to click OK, but no it just does the sort so that's cool, sorting works.
Now, give you the give him an A-plus for this sorting.
Filter okay I should have went back and reselected the data, my bad later screenshots I did go back and reselect the data.
So, when I applied the filter they applied it to the top row of my selection they weren't smart enough to figure out that there were headings above there, but nonetheless I get the filter drop-down.
So, I open this filter drop-down they give me a list of All, A or B, turned off B and then I get a list of A.
So, that's cool let's go on here and try the other one this little bar here I didn't mention this yesterday I said there were only a few commands we have View Cell, we have Freeze, Wrap and Comment.
So, you can add a comment to a cell, you can wrap text, you can freeze panes and view cell.
What does view cell, oh, that opens up a screen a box at the bottom that says this is the cell address and this is the value.
So, if you had something really, really long there and it was spilling beyond the edge of the cell you couldn't see all the values you could view cell with that and then finally the last thing on the eyeball menu, the autosum, it's not really an autosum it's not adding an equal sum formula at the bottom, I was being evil here I wanted to test and see if they would do the equal subtotal instead of equal sum.
It's not any that at all it's really the quick sum it's the, the totals that appear at the bottom right of your spreadsheet when you select some cells.
So, you know when I do this and select it AutoSum they show me the sum the average the max, the min, the count.
Yeah, that's nice do they know the rule that if you apply a filter, they should only show you the visible cells, no they don't it's the exact same answer.
So, here I've applied the filter to group A and it's still showing me the total of everything.
Well, I'm really kind of left speechless on that one.
I don't know is that a bug is that by design I should at least be consistent with how Excel and the desktop works or they shouldn't support it you know don't give us filters if you're not gonna follow the same rules.
Well, there you go.
All right! So, a few different tests on the iPhone Excel and on the iPhone the formatting does come through very nicely the sorting does work very nicely as I mentioned yesterday my big complaint here is they don't care if you've already given them $399 to buy Excel 2013 Pro Plus, you are gonna have to go out and subscribe to office 365 before you can use Excel on the iPhone.
Frankly, I'm just not sure that it's worth it to have to pay that month after month, after month, when you already own this product on your desktop PC at work or at home or everywhere and want you to go out and rent this I just don't get it.
Well, hey I wanna to thank you for stopping by.
We'll see you next time Excel on the iPhone for another netcast from MrExcel.
Learn Excel from MrExcel podcast episode 1745.
More Excel on the iPhone.
Hey welcome back to the MrExcel netcast.
I'm Bill Jelen.
It's July 5th.
I don't know if anyone's actually working today yes here's a big holiday in the United States picnics and fireworks and the whole bit I bet everyone took the day off.
So, hey we're gonna talk about the Excel on the iPhone again today, yesterday I guess I was pretty negative and I'm not I was hoping I was gonna be able to come in today and say something more positive I I wanted to test some real-life situations where we started out with an Excel file created in Excel 2013 on the computer and then took a look at how it looked on the iPhone.
So, here is the original data set we have a little slicer going on here you know which is new in Excel 2013, this is a table of control+T table name that table data save this out to the SkyDrive and open it all the data shows up the slicer is missing and the table functionality is missing but I still can see the data.
So, all right there we go, formatting yesterday complaining about four colors of fonts and four colors of fill.
If we used formatting on the computer and sent it down to the iPhone, while the formatting still appear and so, I tried all kinds of different things you're not just theme colors, but you know more colors and RGB colors and then some cell styles here, some various underlining here, like a double underline and a dotted border and I was really kind of evil and center cross selection and merge.
You know, my real hope when I started to do this was that when I open it on the iPhone over here the merge wouldn't work and then I'd have something positive say hey, at least the iPhone doesn't let you merge cells, but unfortunately they do let you merge cells and yes sir I complained that there weren't enough numeric formats on the iPhone.
Oh! Hey, if you create them on the computer they do all show up correctly on the iPhone.
So, this is 1234.56 with different formatting and it looks like they all came through even the comma K to show things in thousands date and time I couldn't figure out how to set up a time format on the iPhone, but if I created on the computer it does show up correctly on the iPhone.
That's a good thing.
All right, then I tried some, some evil things one of my favorite formulas in the whole world from February 17 1965 until today how many Friday the 13th are there we need to see the formula bar here.
So, we can see this view formula bar, using this is Mike Girvin's version of my formula.
So, it's not actually in a real formula, but the completely evil thing about it is that it's using the row of A2 and the row of B2 which are way beyond 16384 which is how many rows we have on the iPhone.
So, this thing came in the columns were not the right width and again I don't know how to fix it on the iPhone yet, but the right answer was there.
So, I was intrigued how are they getting the right answer and I wanted to come and change this date.
So, I clicked in here and you'll notice I now knows that now, that is grayed out in the form of the bar click, click, click, click, click, or tap, tap, tap, tap, they're not gonna let you in there and when I try to format another cell I get this message can't edit the workbook.
All right! So, I've used some rows that are beyond the 16384 and they're going to display it for me they're gonna give me the right answers give them credit for that, but then we're in can't edit mode.
I also tried a true array formula.
So, equal and MMULT to the multiplication table I suppose for my podcast a couple of weeks ago and again it's grayed out they don't even show me the formula there and they show me the value greyed out not allowed to edit.
Now, let's take a look at some of the features that they planned on working sorting, filtering, and outline.
All right, let's see how those work because I didn't get to those yesterday.
So, here's the data stuff that I started with on the computer.
I had groups A and B, I was hoping that's gonna be able to outline this like you could do with subtotals just one like that and then some Reps, and some Sales.
So, I could sort and filter, apply an icon set here, apply conditional formatting here, apply the sparkline, open it in Excel in the iPhone and what do we get.
Well, the data is there that's great, the icon set is gone, the conditional formatting is gone, the sparkline does not appear.
All right! Well, let's try their features.
So, it's underneath the eye, the eyeball the view features, right?
All right! So, the first thing I did was I tried outline, outline it's not like grouped outline had all Excel people know it's some weird thing where it shows me a list of all of the sheets and it would let me jump to a sheet I guess I only had one sheet.
So, the outline was really really boring.
So, click the down arrow to close that sort is fairly cool.
All right! So, I selected the data and they were able to figure out that the top row where the headings starts out with sort by none as soon as I open this flyout menu and choose sales and then descending look it sorts instantly I don't have to click OK or anything like that it just does the sort and then I went and chose rep and it's sort of backed by rep.
I kept thinking well I have to click OK, but no it just does the sort so that's cool, sorting works.
Now, give you the give him an A-plus for this sorting.
Filter okay I should have went back and reselected the data, my bad later screenshots I did go back and reselect the data.
So, when I applied the filter they applied it to the top row of my selection they weren't smart enough to figure out that there were headings above there, but nonetheless I get the filter drop-down.
So, I open this filter drop-down they give me a list of All, A or B, turned off B and then I get a list of A.
So, that's cool let's go on here and try the other one this little bar here I didn't mention this yesterday I said there were only a few commands we have View Cell, we have Freeze, Wrap and Comment.
So, you can add a comment to a cell, you can wrap text, you can freeze panes and view cell.
What does view cell, oh, that opens up a screen a box at the bottom that says this is the cell address and this is the value.
So, if you had something really, really long there and it was spilling beyond the edge of the cell you couldn't see all the values you could view cell with that and then finally the last thing on the eyeball menu, the autosum, it's not really an autosum it's not adding an equal sum formula at the bottom, I was being evil here I wanted to test and see if they would do the equal subtotal instead of equal sum.
It's not any that at all it's really the quick sum it's the, the totals that appear at the bottom right of your spreadsheet when you select some cells.
So, you know when I do this and select it AutoSum they show me the sum the average the max, the min, the count.
Yeah, that's nice do they know the rule that if you apply a filter, they should only show you the visible cells, no they don't it's the exact same answer.
So, here I've applied the filter to group A and it's still showing me the total of everything.
Well, I'm really kind of left speechless on that one.
I don't know is that a bug is that by design I should at least be consistent with how Excel and the desktop works or they shouldn't support it you know don't give us filters if you're not gonna follow the same rules.
Well, there you go.
All right! So, a few different tests on the iPhone Excel and on the iPhone the formatting does come through very nicely the sorting does work very nicely as I mentioned yesterday my big complaint here is they don't care if you've already given them $399 to buy Excel 2013 Pro Plus, you are gonna have to go out and subscribe to office 365 before you can use Excel on the iPhone.
Frankly, I'm just not sure that it's worth it to have to pay that month after month, after month, when you already own this product on your desktop PC at work or at home or everywhere and want you to go out and rent this I just don't get it.
Well, hey I wanna to thank you for stopping by.
We'll see you next time Excel on the iPhone for another netcast from MrExcel.
