Excel Tips Tricks
This year, Spreadsheet Day falls on Saturday, Oct. 17, a day in which number crunchers worldwide gather around the formula bar, fill their cells with all sorts of values, and reminisce about the 1979 birth of VisiCalc, the Apple II computer program that first brought this wonder to the workplace. Of course, everyone has their own holiday traditions. Google Sheets users, for instance, like to get together with friends and collaborate on spreadsheets together. Apple Numbers users decorate their documents with multiple, multi-color charts and graphs.
And OpenOffice users cobble together meager tables while talking about the good ol’ days of data sets. Personally, I like to think Spreadsheet Day is all about family.
But Excel users are the biggest bunch of spreadsheet revelers out there, and for them we have rounded up a list of five quick tips for getting the most out of their electronic ledgers this year. Navigate Like A Pro Sure, mice and trackpads are tools of the trade. But if you want to rock a Excel like an electric guitar, you’ve got to do your finger picking with the keyboard. For instance, holding down control while tapping an arrow button will select every cell all the way to the end of the sheet. Flip through worksheets by holding control while pressing either page down or page up. Turn a range of numbers into currency, complete with a dollar sign and two decimal places, by pressing control, shift, and $ at the same time.
Similarly, you can turn a number into a date (day, month, and year) by pressing control, shift, and # simultaneously. These are just five of the hundreds of keyboard shortcuts available in Excel. But don’t trash your mouse yet—it will come in handy for scrolling through all those navigational tips. Flash Fill Your Data I know saying this makes me sound like the Grinch Who Stole Spreadsheet Day, but: Data entry is the worst. Cutting and pasting is fine; importing is great; but manually keying in values can crush the soul.
In honor of Spreadsheet Day, we rounded up 5 tips to make you an Excel pro. Microsoft Excel is an amazing piece of software, and even regular users might not be getting as much out of it as they can. Improve your Excel efficiency. In this article, we have some really cool, latest Excel tips and tricks that can help users to improve the way in which they have used Microsoft Excel.
Thankfully, starting with Excel 2013, the Flash Fill feature has helped ease this pain by examining the information you’re inputting and suggesting values in subsequent boxes. That might sound like magic, but it’s really just logic. For instance, if you’re entering a column full of dates, Flash Fill will detect that and help Excel cells make that cognitive leap. Or, say you’re creating a contact sheet and have just filled a column full of email addresses, and you start a new column for last names. Once Flash Fill catches on, it can examine the email addresses and suggest the names for you. (This trick is an HR department holiday favorite!). Think Fast With Quick Analysis For the experienced and uninitiated alike, Excel is a deep program with a variety of capabilities.
But finding those tools can be one of the bigger challenges of using the program. Quick Analysis is a small menu located at the bottom righthand corner of a table or list in Excel 2013 and onward that pulls together the basic things that most users do with similar data sets. So instead of having to go back to the ribbon and find the feature, you just click on to the corner of the table. And because it’s in such close proximity to your data, your mind won’t start wandering through all the other features of Excel, or to your web browser where you’re searching for help, or to Facebook because you just opened your web browser. In addition, the contextual tools that Quick Analysis brings up can add great visuals and depth to your spreadsheet, making you look like much more of an Excel pro. Show Off With New Charts Pie charts on Spreadsheet Day are like pumpkin pie on Thanksgiving — of course they’re going to be there, but wouldn’t you rather have something else?
In fact, Excel 2016 is serving up several refreshing new data visualizations to make your spreadsheets burst with flavor. The first two are hierarchical charts: the treemap and the sunburst. The treemap is a giant square (that represents 100% of the values) full of rectangles (that represent smaller portions of the whole). The sunburst functions similarly, though its round and the inside portions are like chunks of onion, rather than pie slices. These two charts are great for showing how categories can add up to a whole, and even how smaller bits within those categories can contribute as well. And a third new chart, the waterfall, is something that master spreadsheet artists have been laboriously building on their own for some time, but now can do with with just a click from the InsertCharts menu.
“This is a really interesting chart because it’s showing different type of information with your data,” says Pamphlet. “It’s showing you flow and contributions to a total, and this is something that’s useful for financial data.” 5. Look Ahead With One-Click Forecasting “Forecasting” is one of those terms that’s thrown around a lot in the age of Big Data, but even small spreadsheets have the ability to look ahead and make solid projections. “We’ve made it super easy to create forecasted data based on historical data that you’ve created inside Excel,” says Pamphlet.
A new feature in Excel 2016, users can select columns on a table and have the spreadsheet make a few different projections as to how the results will play out in the future. Displaying the information as either a line or bar chart, Excel will even calculate lower and upper confidence bounds (based on a programmable confidence interval) to show the possible margin of error. You can also fine tune the tool to better meet your needs, calculating for seasonality, for instance. And the timing couldn’t be better, because now you have the tools to determine how many people will come to your Spreadsheet Day party next year. Osmo Tangram Tired of seeing your kids staring at screens like zombies? This iPad game brings virtual play to life.
A reflector equipped with artificial intelligence snaps over your iPad's camera and can sense when objects are moving (or being drawn) on a pad in front of it. Comes with three games, all of which promote creativity and—because it’s best played in groups—social intelligence.
The best way to understand how Osmo works is by watching the of kids who have never played with the device before testing it out for the first time. Jawbone's UP3 Wristband Depending on your view, Jawbone is on the road to making us all super fit athletes or brutally efficient cyborgs who operate by data alone. How many minutes of REM sleep did I get last night? What’s the difference between my heart rate while resting and during a workout?
How hydrated am I? How many calories did I burn on my last run? Jawbone UP3 answers all those questions better than any other Jawbone did before. The $179 wearable fits on your wrist and comes in black and silver.
This year, Spreadsheet Day falls on Saturday, Oct. 17, a day in which number crunchers worldwide gather around the formula bar, fill their cells with all sorts of values, and reminisce about the 1979 birth of VisiCalc, the Apple II computer program that first brought this wonder to the workplace. Of course, everyone has their own holiday traditions. Google Sheets users, for instance, like to get together with friends and collaborate on spreadsheets together. Apple Numbers users decorate their documents with multiple, multi-color charts and graphs.
And OpenOffice users cobble together meager tables while talking about the good ol’ days of data sets. Personally, I like to think Spreadsheet Day is all about family. But Excel users are the biggest bunch of spreadsheet revelers out there, and for them we have rounded up a list of five quick tips for getting the most out of their electronic ledgers this year. Navigate Like A Pro Sure, mice and trackpads are tools of the trade. But if you want to rock a Excel like an electric guitar, you’ve got to do your finger picking with the keyboard. For instance, holding down control while tapping an arrow button will select every cell all the way to the end of the sheet. Flip through worksheets by holding control while pressing either page down or page up.
Turn a range of numbers into currency, complete with a dollar sign and two decimal places, by pressing control, shift, and $ at the same time. Similarly, you can turn a number into a date (day, month, and year) by pressing control, shift, and # simultaneously. These are just five of the hundreds of keyboard shortcuts available in Excel. But don’t trash your mouse yet—it will come in handy for scrolling through all those navigational tips. Flash Fill Your Data I know saying this makes me sound like the Grinch Who Stole Spreadsheet Day, but: Data entry is the worst. Cutting and pasting is fine; importing is great; but manually keying in values can crush the soul.
Thankfully, starting with Excel 2013, the Flash Fill feature has helped ease this pain by examining the information you’re inputting and suggesting values in subsequent boxes. That might sound like magic, but it’s really just logic. For instance, if you’re entering a column full of dates, Flash Fill will detect that and help Excel cells make that cognitive leap. Or, say you’re creating a contact sheet and have just filled a column full of email addresses, and you start a new column for last names. Once Flash Fill catches on, it can examine the email addresses and suggest the names for you.
(This trick is an HR department holiday favorite!). Think Fast With Quick Analysis For the experienced and uninitiated alike, Excel is a deep program with a variety of capabilities. But finding those tools can be one of the bigger challenges of using the program. Quick Analysis is a small menu located at the bottom righthand corner of a table or list in Excel 2013 and onward that pulls together the basic things that most users do with similar data sets. So instead of having to go back to the ribbon and find the feature, you just click on to the corner of the table.
And because it’s in such close proximity to your data, your mind won’t start wandering through all the other features of Excel, or to your web browser where you’re searching for help, or to Facebook because you just opened your web browser. In addition, the contextual tools that Quick Analysis brings up can add great visuals and depth to your spreadsheet, making you look like much more of an Excel pro. Show Off With New Charts Pie charts on Spreadsheet Day are like pumpkin pie on Thanksgiving — of course they’re going to be there, but wouldn’t you rather have something else? In fact, Excel 2016 is serving up several refreshing new data visualizations to make your spreadsheets burst with flavor.
Excel Tips Tricks Shortlist
The first two are hierarchical charts: the treemap and the sunburst. The treemap is a giant square (that represents 100% of the values) full of rectangles (that represent smaller portions of the whole).
Favorite Excel Tips & Tricks
The sunburst functions similarly, though its round and the inside portions are like chunks of onion, rather than pie slices. These two charts are great for showing how categories can add up to a whole, and even how smaller bits within those categories can contribute as well. And a third new chart, the waterfall, is something that master spreadsheet artists have been laboriously building on their own for some time, but now can do with with just a click from the InsertCharts menu. “This is a really interesting chart because it’s showing different type of information with your data,” says Pamphlet. “It’s showing you flow and contributions to a total, and this is something that’s useful for financial data.” 5.
Look Ahead With One-Click Forecasting “Forecasting” is one of those terms that’s thrown around a lot in the age of Big Data, but even small spreadsheets have the ability to look ahead and make solid projections. “We’ve made it super easy to create forecasted data based on historical data that you’ve created inside Excel,” says Pamphlet. A new feature in Excel 2016, users can select columns on a table and have the spreadsheet make a few different projections as to how the results will play out in the future. Displaying the information as either a line or bar chart, Excel will even calculate lower and upper confidence bounds (based on a programmable confidence interval) to show the possible margin of error.
You can also fine tune the tool to better meet your needs, calculating for seasonality, for instance. And the timing couldn’t be better, because now you have the tools to determine how many people will come to your Spreadsheet Day party next year. Osmo Tangram Tired of seeing your kids staring at screens like zombies? This iPad game brings virtual play to life.
A reflector equipped with artificial intelligence snaps over your iPad's camera and can sense when objects are moving (or being drawn) on a pad in front of it. Comes with three games, all of which promote creativity and—because it’s best played in groups—social intelligence. The best way to understand how Osmo works is by watching the of kids who have never played with the device before testing it out for the first time. Jawbone's UP3 Wristband Depending on your view, Jawbone is on the road to making us all super fit athletes or brutally efficient cyborgs who operate by data alone. How many minutes of REM sleep did I get last night? What’s the difference between my heart rate while resting and during a workout?
How hydrated am I? How many calories did I burn on my last run? Jawbone UP3 answers all those questions better than any other Jawbone did before. The $179 wearable fits on your wrist and comes in black and silver.