Learn Excel - MrExcel Review of Excel for iPad - Podcast 1874

MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1874.
MrExcel Review of Excel for the iPad.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen from MrExcel.com.
It was 4 years ago in 2010, that the first iPad debuted.
My friend Jerry handed me one, in the summer 2010.
I said, wouldn't it be amazing to have Excel on this.
Yesterday, March 27, 2014.
They...
Microsoft released Excel, Powerpoint and Word for the iPad.
I have to tell you, I Love it.
I've been using it for the better part of a day,.
It has amazing features.
They did a beautiful job on this.
Hey! It turns out, this is a really long video.
So, let's cut to the chase right up front here.
Not supported at all, didn't ever expect this to happen, VBA Macros, but no external data connections which means no Power Pivot.
So, my surprise hyperlinks is not working, table slicers and timeline slicers aren't working.
In fact, I can't seem to get pivot table slicers to work but I think that's a bug with me, not with the app.
All of this here in the center column, all of these you can do right on the iPad.
I'm going to show you that in the video, an impressive set of editing tools on the iPad.
Now, that assumes that you have office 365.
If you don't have office 365, you can just view and then these are things that you can create on the PC.
That you will then render correctly on the iPad.
And we're going to take a look at all of that in the video.
My advice to you is down, in the description will be a table of contents.
If there's one particular thing you want to jump to, feel free to jump right to that.
Let's get started.
Now, primarily you are going to be using Excel to consume reports, that you created on the PC, but it is still very possible to create your own reports right here.
I'm just going to show you that we can do that.
They've added enough features report sample.
I'll press [ return ].
I'll go back and select that cell and you'll see that I can change that the bold, if I want italics, underline, all the usual stuff.
I can change the font size but even better than that, the Excel 2010 Cell Styles are there, so I can just choose a title, style in very quickly.
Fill that data in, here we'll type JAN for January, select the data and then single tap and say that I want to fill and they let me fill either down or across.
So, the fill handle works alright, and then we need to, what we need to do?
We need to align that, right.
And that works just fine.
Let's type some numbers here, so I'll put in a thousand.
Of course, Excel is all about formulas, right.
So, we're going to start a formula here type the equal sign, recently used function, shows up here.
So what the round of, let's say 1.1 times that previous number, I can just click on it like it was a mouse and click on number of digits and we'll round it to zero digits except that formula.
Now, when I go back it puts it in edit mode.
So, I have to hit the green check mark to the right, but then we will...
Actually, let's do it this way.
So, I'll single tap, copy and then paste, and it copies those formulas across.
In fact, we can select the whole range, copy that and paste and here it would want to fill going down.
So, let's type in 2000 there, select those two cells and then say fill.
It's very easy to go through and create a report from scratch.
They've added enough features in here that you can make something.
It looks good in fact on the insert tab, we can even say insert table and they quickly apply the formatting.
I have a new contextual ribbon tab there called table.
Where I can turn on and off the banded rows, if I want choose a different table style.
All right! So, you can quickly, quickly recreate your own spreadsheet here with formulas copying and pasting, it feels natural.
It feels fluid.
All right! Let's look at some other features that they gave us.
I'm gonna press the new sheet icon, one of the things, that I really, really hated about Excel for the iPhone, was the horrible for color palette.
All right! I mean literally, it was 2-bit color and so one of the first things I did, when I opened this was, you know I said, I was kind of snarky about the whole thing.
I wonder like, Oh! But hey, look they have all the theme colors here, more colors you get those colors, all right!
So, what that's about 50 colors, I hope check it out.
They have every single color, you want.
They're all, they're all 16 million colors.
So, kudos for that some other things they added that are kind of interesting.
We'll get to charts, later.
We can insert shapes.
All right! So, just all the auto shaped features, choose the little cloud there, resize it and now we have the shape styles.
So, we can choose from the very shape styles, built in.
Of course, those colors are based on your theme.
You can choose any fill color, that you want.
I can rotate the shape.
I can change the color of the line and I can even click the shape and edit text and say this is cool and once I have that text, I'll select all go back to the home tab and choose a larger font size.
Change the alignment, right, it all just works very, very cool.
Now, are shapes the most important thing?
No, they're probably not.
But it's nice they put that in pictures, it will access pictures from your photo stream.
Here is the keyboard that I went out and bought, yesterday.
Rotate that, there you go.
That was at Books-A-Million, $29 Bluetooth Keyboard, pass that way I could actually use the keyboard.
With Excel do you need to use the keyboard, no not unless you have a really really large spreadsheet.
Hey! Look all the picture styles that they added in Excel 2010, are there.
So, we can add a little...
So, we're gonna add a little oval there.
I can add a reflection, is this stuff important?
No, it's not, but is it cool?
Yeah, it's nice.
They really went beyond you know, just the the basics and there is a lot in there.
Now, on the view tab, they have these great little toggle bars here, to trough the formula bar.
Turn off the sheet tabs if you want, turn off the headings and turn off the grid lines.
Even freeze panes, so you can have something that doesn't necessarily look like a spreadsheet.
Hey! This next one.
I'm not going to call is a feature, one of the things they added was merge cells.
Now, since I think that merge cells are the worst thing in the world.
I never would have put that one in but okay!
Just put a little tap over that part of the screen and you'll never be tempted to use that.
All right! Selecting when you first select a cell, you get that outline around it.
But then to select more cells, you use one of the green dots very easy to do very fluid.
Let's take a look at their charts, so insert.
I'm gonna use a recommended chart, they recommend the column chart for that and then what can we edit, so we have this chart.
Context menu up here and we can change the chart styles.
These were the Excel 2013 chart styles, very nice.
This is a quick way to apply a lot of formatting to the chart, very quickly.
I like these in the Excel client ,in Excel 2013 and they're very nice here.
In Excel 2010, we can choose different colors and even various layouts.
Can we go micromanage the chart?
No, we cannot do that.
There aren't tools here to do that.
But you can do that back on the PC and create a perfect chart on the PC, and then come down here and view it on the iPad.
They will switch the axis, rows and columns versus columns and rows.
So, enough tools to get you some really nice charts.
If you're gonna need to fine-tune those, you're going to have to go back to the PC, to do that.
Just finishing up on the home tab.
What else do they have?
There, they have number formats with a lot of different choices.
But no custom, you can't create a custom number format here, but still you can go through and a lot of the different choices are there, so you can do your number formatting.
All right! So, that's nice.
Insert rows and delete rows, so this icon here.
We insert a sheet row or delete sheet row or columns.
So, they give you those, also sort and filter.
Let's see, let's sort this data descending and or ascending.
All right! Nice and easy.
The filter drop-down, so we will take a look at those, a little bit later.
Okay! So, hey! It's time to save this thing and we can choose to save to either your one-drive account or right here to the iPad, right isn't that cool.
Again, I finally have like a file, save as a little file system on the iPad, which is the one thing, right and I was really looking for, so this is just test to turn in there.
It's saved on the iPad.
If we choose new here, they give us all sorts of templates.
These are the Zach easy templates, very quick way to quickly create a brand new spreadsheet from scratch, that you can just fill in these were created on the PC.
So, they have some features on the P (iPad), that were available on the PC.
That will just render here on the iPad but still quick way to get your spreadsheets.
Well, you know, of course as you would expect, I'm going to test the limits on this thing.
So, here is an obnoxious file with 600,000 cells, I wanted to see how that would react on the iPad.
All right! So, here it is.
I'm just gonna start swiping, check this out.
It swipes from very, very fast.
Left, right, up, down.
My only complaint here is I don't have [ Control Down Arrow ].
There's no way naively on the iPad to press [ control down arrow ].
Which is what, I need to do to get to the very last cell.
Now, if I'm lucky and I know something in the last cell, I can search for like for example, total.
You'll notice the search takes a little bit just after I start to type.
It's like they're searching as I'm typing and in this case with 600,000 cells.
It has a long way to go.
There we go, we found it.
All right! Now, let's see how it works doing an auto-sum.
So, where the formulas to have click auto-sum.
Want to use the sum function and press return and just that quick, all right.
So, it's very speedy now, this is the new iPad Air.
So, the fastest processor that we have today on, an iPad.
But it is very, very responsive.
You know, and hey, by the way, the other way that you can navigate here is if you have a bluetooth keyboard for your iPad.
Now, I don't have a bluetooth keyboard, but I went out and bought one, last night.
It was 29 bucks.
So, I just turned on the bluetooth keyboard, and I'm pressing not control, but [ command down arrow ] and I can quickly navigate down to the last cell [ control or command right arrow ], [ command up arrow ].
Alright! But let's face it, you're not really going to be creating spreadsheets on the iPad.
You're going to be creating them on the PC.
Sending them out to the one-drive, so others can consume them on the iPad.
So, let's see what the iPad can actually render, all right!
So, I'm throwing a lot at it here.
Here's just a small data set with a chart.
I've done formatting in the chart.
I also by the way, went to page layout and change to a different theme.
Let's see, if they can render the colors in that theme.
Here's some slicers and a chart.
Here's a pivot table with a timeline slicer and then just a whole bunch of things, I wanted to see if it will render the new data visualizations, data bars, the color scales, the icon sets will do spark lines, spark bars, strike through will let us do data validation, custom number formatting, hyperlinks back to menus.
Let's see what this will do, So, this is saved out on the sky-drive.
I'll save it here, I have to close it on the PC.
That way we don't have a sharing issue, and then on the iPad.
Now, we'll open that, dowlnoad it from the one-drive.
All right! So, let's come back here to our data tab.
All right! There we go, it rendered the chart even with my title, the headings, I had made the width of each, the height of each bar taller and all of that worked fine.
All right! Let's do a little formula out here.
So, we're create a figure out the sales tax and here we're going to put a tax rate.
Currently in Florida today, so let's go with our 6% Brevard County, tax rate.
All right! So, think about this formula.
We start with an equal sign, we type the tax rate.
I'm going to switch over to the formula keypad here, to get my time sign and then click on revenue.
But think about this, this formula has to use the [ F4 ] key to lock that.
[ L1 down ] All right!
Well, right here in the formula bar, I'm going to click on L1 and the context menu offers reference types, and I can say this is absolute row, absolute column.
It's one of the very important things that you have to be able to do in Excel, is use the absolute references and that certainly works.
All right! So, we will copy that cell.
Choose the whole range and then paste and our formulas, now always accurately refer back to L1, nice feature.
Slicers, let's see how those look.
Well, fail there.
The shape represents a table slicer that does not work.
Pivot tables, timeline slicer, does not work.
Will it render?
All right!
So, all of our conditional formatting does appear, the spark-line up here, is a spark bar appears, strike through appears.
Let's see how the data validation looks, so we select that cell.
We get a little drop-down and we can choose from the drop-down, custom number formats.
Let me change that to number 1.
I have a program that 1, will put a checkbox in a program, but just using a custom number format and hyperlinks.
Can we hyperlink back to a different sheet?
That's really disappointing, we needed hyperlinks, and it doesn't look like that's working.
Hey! For test, I just went back to the PC and change this pivot table and added a regent slicer, and it is not rendering at all.
So, a little disappointing that the table slicers and the timeline slicers and the pivot table slicers are not rendering here.
I mean, they after all, they do it in the office web app.
I thought that that would have been one.
That we had.
All right! Let's take a look at the filter feature.
I promised, I'd do that.
So, here we have our data set, we turn on the filter and then here in the drop-downs.
We can unselect all, just choose product ABC, of course the chart automatically updates.
Just like in regular Excel.
So, filters work.
There is no search, that they added in Excel 2010 to the filters, but you know that's generally, okay, that all right!
An interesting...
Do they have a format painter?
Let's try the format painter, so we choose some sort of a color here, and then I will copy that cell and select a range of cells and paste formats that's essentially the format painter.
So, that works.
Here's one that's not going to be surprising to office pros, but might be surprising to people who just generally use the iPad.
So, we're here in an application called Excel for the iPad, and I copy this chart and then let's go back to the main menu and I'll open Power Point.
I'll create a new presentation.
and then paste and remarkably the chart from Excel gets pasted right here in Power Point.
So, we actually have copy and paste between two different apps, which is very, very rare on the iPad.
and one of the beautiful things is, you're working on your PC, and the iPad.
Everything stays in sync with the one-drive.
So, I just created a brand new file on the...
So, I just created a brand new file on the PC called a rate formula and see it, it just pops right in and my recent items there, okay!
So, here is a fairly ugly array formula that I created on the PC.
That is analyzing 49 years of dates, let's see if we can change the formula.
Let's see if we can change the input cells and have that array formula recalculate.
Yes, that works.
I wonder if we can edit the formula here.
So, testing for instead of Friday the 13th, testing for Friday the 23rd, press [ enter ] and wow!
That actually, works.
That allows us to edit the array formula.
Hey! Back on the PC, okay!
Now, I don't expect any of these to render on the iPad if any of them do, I'll be amazed.
Word Art, Smart Art, a object with 3D, insert screen clipping and then the camera tool.
So, I'm really honestly just don't expect anything this to work, but we'll save it.
We'll close it.
Wow! That's pretty crazy, every single one of those is working including the camera tool.
Can that, that can't possibly be working.
Hang on, let's change that number to something else.
Scroll down, no, okay! Yeah, so the camera tool is static.
That would have been way cool, but Word Art, Smart Art, Art 3D bevel, is working very nice.
All right! This is so, there you have it.
Overall, I'm really impressed.
I never expected macros to work.
I don't think they'll ever work.
But the things here that you're able to create on the iPad are great.
The things that render on the PC are great.
These two, I'm still not sure on hyperlinks.
It just, it seems bugging me.
I can't get it that link to another spot in the worksheet, but they have UI for hyperlinks.
So, I'm just not doing it right.
Pivot table slicers, they're not rendering.
Not sure if that's a bug, we'll figure that one out.
Overall very, very impressive great job.
Kudos to the team at Microsoft, that design Excel for the iPad.
It came out great.
Okay! There you have it.
Thank you for stopping by, we'll see you next time for another netcast from MrExcel.