{"id":177,"date":"2013-04-04T10:30:31","date_gmt":"2013-04-04T15:30:31","guid":{"rendered":"http:\/\/flevy.com\/blog\/?p=177"},"modified":"2015-04-01T01:44:40","modified_gmt":"2015-04-01T06:44:40","slug":"tips-from-a-consultant-some-not-so-obvious-excel-shortcuts","status":"publish","type":"post","link":"https:\/\/flevy.com\/blog\/tips-from-a-consultant-some-not-so-obvious-excel-shortcuts\/","title":{"rendered":"Tips from a Consultant: Some Not-so-Obvious Excel Shortcuts"},"content":{"rendered":"<p>There are 3 things that separate the struggling Excel monkey from the seasoned one.<\/p>\n<ul>\n<li><span style=\"line-height: 13px;\"><strong>Keyboard Shortcuts. \u00a0<\/strong>Knowing your keyboard shortcuts will dramatically accelerate the speed and efficiency of your work. \u00a0It also makes you look like a real pro when your colleagues see you jump from cell to cell, sheet to sheet without ever touching the mouse.<\/span><\/li>\n<\/ul>\n<ul>\n<li><strong>Pivot Tables and Formulas. \u00a0<\/strong><\/li>\n<\/ul>\n<ul>\n<li><strong>Visual Basic Programming. \u00a0<\/strong>A little bit of VB programming knowledge goes a long way. VB can be used to automate calculations, which, in many cases, becomes necessary, as the model becomes too bulky to &#8220;calculate&#8221; within a bearable amount of time.<\/li>\n<\/ul>\n<p>This article will discuss the first group&#8211;shortcuts. \u00a0I\u2019ve compiled a short list of \u201cnot-so-obvious\u201d Excel shortcuts. \u00a0These are also the shortcuts I find myself using most often.<\/p>\n<p><span style=\"color: #888888;\"><strong>Navigation Shortcuts<\/strong><\/span><\/p>\n<table style=\"font-family: arial;\" border=\"0\">\n<tbody>\n<tr>\n<td width=\"180\"><strong>Ctrl + arrow<\/strong><\/td>\n<td>This is the shortcut I use most frequent. Hold down &#8220;CTRL&#8221; and hit the arrow key to jump to the edge of the next data region (in the direction of the arrow key). If you&#8217;re unfamiliar with this shortcut already, test it out. It&#8217;s a bit difficult to articulate in words.<\/td>\n<\/tr>\n<tr>\n<td><strong>Ctrl + Shift + arrow<\/strong><\/td>\n<td>Achieves the above, while highlighting your selection. This is most often used in conjunction with other shortcuts (e.g. preceded with Copy and proceeded with a Paste).<\/td>\n<\/tr>\n<tr>\n<td><strong>Ctrl + Page Up<br \/>\nCtrl + Page Down<\/strong><\/td>\n<td>Jump to the previous sheet (to the left).<br \/>\nJump to the next sheet (to the right).<\/td>\n<\/tr>\n<tr>\n<td><strong>Ctrl + Home<\/strong><\/td>\n<td>Jump to the top-left cell of the sheet. In most cases, this is &#8220;A1.&#8221; Exceptions include if rows\/columns are hidden or the sheet has frozen panes.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><span style=\"color: #888888;\"><strong>Custom Quick Access Shortcuts<\/strong><\/span><\/p>\n<p>One of the greatest improvements in Excel 2007+ is the ability to customize the Quick Access Toolbar. The Quick Access Toolbar refers to the section at the top-left corner of the Excel interface (see screenshot below). You can customize this toolbar by right clicking on it and selecting &#8220;Customize Quick Access Toolbar&#8230;&#8221; From there, you can add, remove, and re-order shortcuts. I&#8217;ve only added 2 shortcuts here.<\/p>\n<p><a href=\"https:\/\/flevy.com\/blog\/wp-content\/uploads\/2013\/04\/excel_quickaccess.gif\"><img decoding=\"async\" class=\"aligncenter size-full wp-image-575\" src=\"https:\/\/flevy.com\/blog\/wp-content\/uploads\/2013\/04\/excel_quickaccess.gif\" alt=\"excel_quickaccess\" width=\"300\" height=\"191\" \/><\/a><\/p>\n<table style=\"font-family: arial;\" border=\"0\">\n<tbody>\n<tr>\n<td width=\"180\"><strong><a href=\"https:\/\/flevy.com\/blog\/wp-content\/uploads\/2013\/03\/formatpainter1.gif\"><img decoding=\"async\" class=\"alignnone size-full wp-image-233\" src=\"https:\/\/flevy.com\/blog\/wp-content\/uploads\/2013\/03\/formatpainter1.gif\" alt=\"formatpainter\" width=\"16\" height=\"14\" \/><\/a>\u00a0Alt + 1<\/strong><\/td>\n<td>This is the &#8220;Format Painter&#8221; shortcut.<br \/>\nI often use this shortcut in conjunction with &#8220;Ctrl + Shift + down arrow&#8221; to extend the formatting of a cell down the column.<\/td>\n<\/tr>\n<tr>\n<td><strong><a href=\"https:\/\/flevy.com\/blog\/wp-content\/uploads\/2013\/03\/pastevalues.gif\"><img decoding=\"async\" class=\"alignnone size-full wp-image-234\" src=\"https:\/\/flevy.com\/blog\/wp-content\/uploads\/2013\/03\/pastevalues.gif\" alt=\"pastevalues\" width=\"16\" height=\"16\" \/><\/a>\u00a0Alt + 2<\/strong><\/td>\n<td>This is the &#8220;Paste Values&#8221; shortcut.<br \/>\nPrior to this trick, accessing the &#8220;Paste Values&#8221; function was a fairly cumbersome process.<\/td>\n<\/tr>\n<tr>\n<td><img decoding=\"async\" src=\"https:\/\/flevy.com\/blog\/wp-content\/uploads\/2013\/04\/pasteformulas.gif\" alt=\"pasteformulas\" width=\"17\" height=\"17\" \/>\u00a0<strong>Alt + 3<\/strong><\/td>\n<td>This is the &#8220;Paste Formulas and Number Formatting&#8221; shortcut.<br \/>\nThis functions allows you to copy and paste everything, except the cell formatting (e.g. borders, background color, font color). \u00a0This is useful if you already have your spreadsheet nicely formatted and just wanted to overwrite or extend some existing formulas.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><span style=\"color: #888888;\"><strong>Random Shortcuts<\/strong><\/span><\/p>\n<table style=\"font-family: arial;\" border=\"0\">\n<tbody>\n<tr>\n<td width=\"180\"><strong>Ctrl + click sheet name + drag tab<\/strong><\/td>\n<td>Many times, you will need to duplicate a full sheet. The easiest way to do this is to hold down &#8220;Ctrl,&#8221; click the sheet name (i.e. click the tab), then drag it.<\/td>\n<\/tr>\n<tr>\n<td><strong>Ctrl + click sheet names + make edits<\/strong><\/td>\n<td>Sometimes, you may want to make the <span style=\"text-decoration: underline;\">same<\/span> changes across multiple spreadsheets. Prior to knowing this method, I would replicate the change across the multiple sheets manually. \u00a0With this shortcut, you can make changes across the sheets simultaneously. \u00a0Once you have the multiple sheets selected (note the color of the tab will lighten), pick any one of these sheets and make your edits.<\/td>\n<\/tr>\n<tr>\n<td><strong>Alt + H, M, C<\/strong><br \/>\n(Letters pressed consecutively)<\/td>\n<td>Merge and center selected cells.<\/td>\n<\/tr>\n<tr>\n<td><strong>Alt + H, O, R<\/strong><\/td>\n<td>Rename sheet.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><strong>Use a shortcut that\u2019s missing from our list? \u00a0<\/strong>Please share in the comments section below.<\/p>\n<p>A great way to learn Excel&#8211;including pivot table usages, advanced formula nesting, and VB programming&#8211;is by studying robust models that have been built by other business professionals. \u00a0You can find\u00a0<a href=\"https:\/\/flevy.com\/browse\/filetype\/Excel\">Excel models<\/a> of all shapes and sizes on <a href=\"https:\/\/flevy.com\/browse\/filetype\/Excel\">Flevy here<\/a>. \u00a0Here are a few examples:<\/p>\n<ul>\n<li><a href=\"https:\/\/flevy.com\/browse\/business-document\/Sample-LBO-Model-Template---2-27\"><span style=\"line-height: 13px;\">LBO Valuation Model<\/span><\/a><\/li>\n<li><a href=\"https:\/\/flevy.com\/browse\/business-document\/Supply-Chain-Program-Comparison-Model-Working-Capital-34\">Supply Chain Comparison Model<\/a><\/li>\n<li><a href=\"https:\/\/flevy.com\/browse\/business-document\/Project-Management-Tool-in-Excel-212\">Project Management Tool<\/a> (i.e. Excel version of MS Project)<\/li>\n<\/ul>\n<p>For a comprehensive list of Excel shortcuts, check out this page:\u00a0<a href=\"http:\/\/shortcutworld.com\/en\/win\/Excel_2010.html#link_9\">http:\/\/shortcutworld.com\/en\/win\/Excel_2010.html<\/a>. \u00a0Also,\u00a0if you are an avid user of PowerPoint, read my list of\u00a0<a href=\"https:\/\/flevy.com\/blog\/tips-from-a-consultant-some-not-so-obvious-powerpoint-shortcuts\/\">Not-so-Obvious PowerPoint Shortcuts<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>There are 3 things that separate the struggling Excel monkey from the seasoned one. Keyboard Shortcuts. \u00a0Knowing your keyboard shortcuts will dramatically accelerate the speed and efficiency of your work. \u00a0It also makes you look like a real pro when your colleagues see you jump from cell to cell, sheet to sheet without ever touching&hellip;&nbsp;<a href=\"https:\/\/flevy.com\/blog\/tips-from-a-consultant-some-not-so-obvious-excel-shortcuts\/\" rel=\"bookmark\"><span class=\"screen-reader-text\">Tips from a Consultant: Some Not-so-Obvious Excel Shortcuts<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":575,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","footnotes":""},"categories":[1],"tags":[103,105,104,101],"class_list":["post-177","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-general","tag-excel","tag-excel-modeling","tag-excel-shortcuts","tag-shortcuts"],"_links":{"self":[{"href":"https:\/\/flevy.com\/blog\/wp-json\/wp\/v2\/posts\/177","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/flevy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/flevy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/flevy.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/flevy.com\/blog\/wp-json\/wp\/v2\/comments?post=177"}],"version-history":[{"count":20,"href":"https:\/\/flevy.com\/blog\/wp-json\/wp\/v2\/posts\/177\/revisions"}],"predecessor-version":[{"id":1421,"href":"https:\/\/flevy.com\/blog\/wp-json\/wp\/v2\/posts\/177\/revisions\/1421"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/flevy.com\/blog\/wp-json\/wp\/v2\/media\/575"}],"wp:attachment":[{"href":"https:\/\/flevy.com\/blog\/wp-json\/wp\/v2\/media?parent=177"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/flevy.com\/blog\/wp-json\/wp\/v2\/categories?post=177"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/flevy.com\/blog\/wp-json\/wp\/v2\/tags?post=177"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}