. 0:00 For business applications, Windows is far and. 0:03 away the most dominant operating system. 0:06 If you're on Windows, you can skip this video and.
Learn the basics of using Microsoft Excel for Mac, including the anatomy of a spreadsheet, how to enter data, how to make your data look good so it's easier to read and use, and more. If you are authoring Macros for Office for Mac, you can use most of the same objects that are available in VBA for Office. For information about VBA for Excel, PowerPoint, and Word, see the following: Unlike other versions of Office apps that support VBA, Office 2016 for Mac apps are sandboxed.
0:08 watch the next one where I take you through the Windows version of Excel. 0:13 Okay, if you're still with me, you're probably a Mac user.
0:17 I am too. 0:18 Let me take you through a tour of the Excel interface. 0:22 So here we are looking at a blank spreadsheet in Excel. 0:26 You can see the basic tabular format of columns and rows. 0:29 Also, there is a sheet down below. 0:32 We can add more sheets by clicking the plus button, or.
0:37 we can go up to the Menu bar, scroll to the Insert,. 0:41 sheets, by sheet.
0:45 This is a good time to call out one of the biggest differences between. 0:48 Excel on a Mac versus a PC. 0:50 You can see on a Mac that not every item has a keyboard shortcut. 0:57 On a PC they will.
1:00 This means that if you're a power user of Excel,. 1:02 chances are you could get things done much more quickly on a PC than a Mac. 1:07 Just something to be aware of. 1:10 So, we have the menu bar at the top of the screen with the application menu. 1:15 Then below that, on the actual Excel interface, at the top,. 1:18 there is what is called the Quick Access Toolbar. 1:22 By default, it should have the File, Save, Undo and repeat commands available.
1:29 If you click this arrow on the right, you can customize your Quick Access tool bar. 1:35 I'll talk more about that in a few minutes. 1:38 Then below the Quick Access tool bar we have what is called the reading in Excel. 1:42 It is made up of different tabs that each have their own toolbar. 1:47 So these are the tabs in the ribbon, and then below them they have their toolbars. 1:52 So, for example, if I click on the Data tab in the ribbon,. 1:56 you can see this is where there's the Sort and Filter options and other features or.
2:00 commands related to data analysis. 2:04 Under View, you can Freeze panes, or Set page layouts. 2:10 You can also adjust Zoom level of your spreadsheet from here. 2:14 Which is helpful when you're chairing a spreadsheet on a video call. 2:18 Sometimes you need to make it bigger for others. 2:24 Below the toolbars, we have the formula bar.
2:28 Excel has a ton of features and commands and. 2:30 also gives you several different options to customize the software for your needs.
2:34 In just a few seconds,. 2:35 I'll briefly cover some of the customization options available. 2:39 In future courses we'll cover more of the features and. 2:41 commands available to you in Excel. 2:44 Some of you may have been wondering why my ribbon has the developer tab in it and. 2:48 yours does not. 2:50 Well it's because the view isn't set by default.
2:54 If you go up to the menu bar and select Excel Preferences. 2:59 note the keyboard shortcut command comma there on the right. 3:03 We get to the Excel Preferences window.
3:06 Here you can change your default settings to suit your needs. 3:10 For example, if I click on ribbon and. 3:13 toolbar, I can change which tabs are visible in the ribbon. 3:18 This is also where I can change what commands appear in the quick.
3:20 access toolbar. 3:25 So for example, I would deselect Developer in View. 3:31 Save down here at the bottom.
3:33 This is a little bit off the screen here. 3:39 Close this down, and look.
3:42 Those tabs are gone. 3:45 But I want them there, so I'm gonna go back and add them. 3:49 View, Developer, Save.
3:55 Another cool thing is I can also create my own tab. 3:58 Plus, new tab.
4:05 I'm gonna rename it. 4:08 TreehouseTab.
4:13 And I can choose which commands I wanna put in there so. 4:17 I'll put in my filter command in. 4:22 Commands can only be added to custom groups. 4:25 To add a custom group, click the tab where you want the group to appear and.
4:29 then click new group. 4:30 Okay there you go so I wanna go here, new group, boom. 4:38 Format why not, let's put that in. 4:41 And Paste Special is one of my favorites. 4:43 Let's put that in. 4:45 So you get the idea. 4:46 This is where you can customize your ribbon tabs and commands.
4:53 So let's close this window. 4:55 Go over here to Treehouse tab, and there they are. 5:00 There are lots of other customization options.
5:03 For example, if you go to general,. 5:09 you can change whether or not you want to have centimeters, millimeters,. 5:12 basically get off the Imperial system and go onto the metric system. 5:16 As you continue to use Excel,. 5:19 you may find yourself having to repeatedly change certain formats or settings. 5:23 That is a good sign for you to consider changing your preferences. 5:27 I want to leave you with two of my favorite keyboard shortcuts that.
5:31 are unique to Excel on Mac. 5:33 First, is control + u. 5:36 This allows you to get into the formula bar from the cell you are in and.
5:40 start editing. 5:42 So unlike Sheets, if you press Enter, it's gonna take you to the next cell below it.
5:45 But if you press control + U, you can see there I am in the formula bar. 5:51 The second keyboard shortcut I wanna share with you is Command + T. 5:55 This allows you to make an absolute reference with the cell. 5:59 So just, for example's sake, I'm gonna put a couple numbers in here. 6:04 And then I'm gonna make a relative reference and now.
6:09 I'm going to make an absolute reference to sell E5 so I'm holding command and then T. 6:13 And you can see it makes it absolute. 6:16 But I can cycle through it so it's not just both column and row. 6:19 It can also be either or so if I press T again you can see it goes to the row. 6:25 If I press T again, it goes to the column. 6:29 So watch, I'm still holding on Command, and just pressing T. 6:31 And it's cycling through the different absolute references I can do.
And give it a thumbs up. Big Update to the VB Editor for Mac I know many Mac users have been waiting along time for this, and I'm excited to share that an update to the VB Editor is finally here! Microsoft just released a new build of Excel that contains a new VB Editor, which we use to write VBA macros in Excel. The new VB Editor contains a lot of the features we are used to seeing in the Windows versions of Excel, or Excel 2011 for Mac. In the video above I highlight some of the many new improvements to the editor. Here is a list of the major new features.
Intellisense drop-down menus for auto-filling code. Toolbars with buttons to for commands like: indenting, comment blocks, run, reset (stop), step into, etc.
VBA Project, Properties, Immediate, Locals, and Watch windows. Debugging features and options. Keyboard shortcuts listed next to each command on the top menus. This new VB Editor feels a lot more like the VB Editor we are used to on the Windows version of Excel.
The one notable feature that is missing right now is userforms. The latest version (7.1) does not have any features that allow you to create, modify, or even view an existing userform. I'm hoping that will be updated in the near future as well. You can on the Excel UserVoice site. Microsoft uses this site to help prioritize feature updates, so please vote there if you want userforms back.
Even with the absence of userforms, this new VB Editor is a huge leap forward from the editor that was originally shipped with Excel 2016 for Mac. If you are a Mac user, you will now be able to write macros to help you automate Excel and save time with your job. If you are new to macros or want to learn more, checkout my upcoming. Get the Office Insiders Fast Pre-Release Build Important Note: You no longer need the Insiders Fast build to get the new VB Editor. You might just need to update Office which can be done from the Help Check for Updates menu. Here is the page with the that lists all the recent updates. To get the updated version you will need to be on an Office 365 subscription and on the Office Insiders Fast Program.
That program is free to join and open to everyone. It allows you to download and install pre-release builds of Excel. The latest build is 16.6 at the time of this writing, and it includes PivotCharts and a new VB Editor for writing VBA macros. Two awesome updates for Mac users.
It's really easy to do! Help Microsoft with Feedback As part of the Pre-release Program, Microsoft would love to get your feedback on how to make pivot charts better. If you find any bugs or have any suggestions you can provide feedback by:.
Clicking the smiley face icon in the top-right corner of Excel. Click the “Tell Us What Can Be Better” option. Complete the form and hit Send. The Excel development team will receive the feedback and use it to make Excel even better. If you are excited about this update, please leave a comment below and let us know.
And thanks Ian! ? Just to clarify, I was referring to the VB Editor that was originally shipped with Excel 2016 for Mac. It wasn’t much of an editor and looked like the following. No toolbars or debugging capabilities. This new editor is definitely a step forward, even with the absence of userforms. I realize that Excel 2011 for Mac did have userforms, so compared to that it is still lacking features. You can run userforms on the Excel 2016 for Mac.
They will just have to be developed in either a Windows version or Excel 2011. I realize this is frustrating if you are only using Macs.
In the past I have used Parallels on a Mac to run Windows, and it worked great. This is another option if you want to get the best of both worlds. Yes, it will come at a price, and no, I don’t get paid by Microsoft. I hope that helps.
You can also vote for the userforms feature on the Excel UserVoice site. Here is a link to that page. I added a link in the article above as well. Microsoft uses that site to help prioritize updates based on votes.
I think it’s also important to note that Macs still only make up about 8%-10% of total market share for personal computers. And MS and Apple are still competitors. So those two factors will probably always give priority to Windows versions of Office. Don’t get me wrong. I love Apple products too.
Our house is full of them. We just can’t expect our sports car to be good at off roading ?. Hi Jon, Thanks for your video above. I’m contemplating moving from PC to Mac, and trying (without much success) to understand how complete the VBA implementation is in Mac Office 2016. I recently tested an xlsm file I wrote in PC Excel 2007 on a friend’s Mac Office 2016, and it choked on trying to create a Word file from within Excel.
This uses the lines: Set objWord = CreateObject(“Word.Application”) Set objDoc = objWord.Documents.Add Is there a newer way to do this in the most recent Mac implementation? I read something about sandboxing on the Mac Office, which makes me think “no”. Wondering in general how complete the port of Excel VBA was to the Mac version.