10/13/2019 Interactive Excel 2016 Tutorial For Mac
This post contains a video and interactive guide on how to use slicers. You can use this if you are distributing a file that contains slicers, and want to provide instructions to your users. Video Watch full screen or on What's a Slicer? Slicers were introduced in Excel 2010, and allow you to quickly filter a PivotTable or PivotChart with the click of a button. They also serve as a visual reference that allow you to see what filters are currently set in the PivotTable.
Basically, slicers do the same thing as filtering a PivotTable using the Filter drop-down menus. However, users and consumers of your reports will really enjoy using slicers. They are very easy to use and make your report more interactive. Slicers can also greatly condense your file size. For example, let's say you have a workbook with a lot of tabs, and each tab contains a similar report for each region, product, category, etc. You could consolidate all of these reports into one sheet and use a slicer to allow the user to see different views (slices) of the same report. Interactive Guide The interactive guide is an Excel file that contains instructions on how to use slicers.
This tutorial will guide you on how to make a checkbox in Excel and use the check box results in formulas to create an interactive checklist, to-do list, report or graph. I believe everybody knows what a checkbox is, you must have seen plenty of them on various forms online. Still, for the sake of.
The entire guide is self contained in one worksheet, and you can copy this worksheet into your workbook before distributing to your audience. The guide covers the basics of clicking on the slicers and also shows some tips for working with multiple slicers connected to one Pivot. The guide actually uses slicers to step through the instructions, so there is NO VBA or macros in the file.
This uses a technique by Bill Jelen (aka Mr. Excel) and you can checkout his to learn more about it. It's an awesome technique!
Download (39.1 KB).Compatible with Excel 2010 and 2013. Please leave a comment with any questions or suggestions on how to improve this guide. Additional Resources The guide above will show you how to use slicers. Here are some articles and videos on how to create slicers and add them to your PivotTables and PivotCharts.
(Articles by Excel MVP Debra Dalgleish at Contextures Blog). (video by Excel MVP Zack Barresse). (article and links by Zack Barresse). (Excel Campus Library).
Kamal - October 10, 2016 Hi Jon, I have an issue, here is the facts: I’ve create slicer on my XLS file, the slicer is a list of dates, and when I click on a date I have all the tasks that are done during this day on a pivot table called “Tasks Overview”. The thing is we might have some “Canceled tasks” some days, so to show the exact number of the tasks that are done on my dashboard am using a simple equation that calculate the following: “Tasks done = total tasks – canceled tasks” So when I choose on my slicer a day with no canceled tasks I get “#REF!” as a result of my equation. On the other side if I choose a day with “Canceled tasks” I got the number of done tasks. Thanks in advance for your help ?. February 13, 2014 Hi Greg, Great question. Unfortunately slicers are not available in Excel 2011 for Mac, but there is a workaround.
The following link is a list of known issues for 2011, and slicers is the first one on the list. Since the issue is listed first, maybe this means the next Excel for Mac version will contain slicers. Slicers are available in the Excel Web App. So you could upload your file to SkyDrive (soon to be called OneDrive) or SharePoint, and share the link with your Mac users. This would allow them to open the file in a web browser and use the slicers. Here is a link to open the Excel Slicers – Interactive Guide.xlsx file in the Web App. Unfortunately, shapes are not supported in the web app yet, so you won’t be able to see the guide.
But you will be able to see the slicers on the PivotTable and interact with them. I hope this helps. Let me know if you have any questions. February 13, 2014 Hi Rolo, The effect of changing the images when the slicer button is pressed is based on a technique that Bill Jelen created. You can see more about it on his YouTube video here.
Basically, the image is a Linked Image and the source of the image is a named range. The named range contains an OFFSET formula to display the image. This OFFSET formula references a starting cell, which is cell AC58 in the file, and then offsets based on the value that is displayed in the PivotTable in cell AG50. The value displayed in the PivotTable is based on the Slicer for the Steps (Intro, Step 1, Step 2, etc.). Sounds confusing? ? It is a complex process and I recommend watching Bill’s video to help get an understanding.
The nice part is that you can change any of the instructions in this guide by modifying the text boxes starting in cell AC59. You can also add/delete steps if needed.
Please let me know if you have any other questions.
Excel 2016 for Mac brings lots of welcome improvements to the workhorse spreadsheet but also leaves out useful tools. Pros Moving to the cloud: Like the other apps in the latest Mac Office suite, Excel 2016 lets you store, sync, and edit files online, via Microsoft's OneDrive cloud storage service. You can also save Excel files to your Dropbox account.
Collaborate: Excel 2016, through OneDrive, lets you share documents with colleagues and collaborate in real time. You can chat online as well, either through a document chat window or through Skype.
Modern design: The redesigned ribbon for the Mac version of Excel borrows the look and feel of its Windows counterpart, presenting tools and formatting options in much the same way as Excel for Windows does. Excel also recommends appropriate charts based on spreadsheet data. Moving across platforms: Excel 2016 supports many of the Windows keyboard shortcuts and most of the Excel 2013 for Windows functions, which should make it easier to use the tool across platforms. The update also includes an Analysis ToolPak add-in, PivotTable slicers for analyzing data, and an improved formula builder. Cons Macro limits: Prior to Office 2016, you could build macros in Excel for Mac.
The 2016 edition offers what Microsoft calls a 'simplified' Visual Basic Editor (VBE), which allows you to debug existing macros. But if you want to build new macros, you need to do that on the Windows side or use an earlier Mac version. For those who built macros in preceding Mac editions, this is a big loss.
Bottom Line Excel 2016 is probably worth the upgrade just based on its ability to take advantage of OneDrive. The new analysis tools and formula builder help keep Microsoft's spreadsheet an essential tool. Hobbling its macros tool, however, diminishes a bit of the attraction for serious users.
More Resources. With the new features in Excel, you'll be telling your data's story in no time. Charts, graphs, keyboard shortcuts and data entry enhancements (like formula builder and autocomplete) immediately make you more productive. And support for Excel 2013 (for Windows) functions ensures that you can easily share files across platforms. The new Analysis ToolPak offers a wide range of statistical functions, including moving averages and exponential smoothing, and PivotTable Slicers help you cut through large volumes of data to find patterns that answer questions.
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |