Power Query, otherwise known as the \u201cGet and Transform Data\u201d tool is incredible at automating the many repetitive steps that users often perform when getting data into Excel and manipulating it to make it useful for further analysis and calculations. It is extremely flexible in the options of how you can automatically connect to data, as well as how you can transform that data to suit your needs.<\/div>\n

Pro tip<\/strong>: Learn some of the most common Power Query actions first, such as filtering, grouping, calculating fields, transposing, and unpivoting.<\/div>[\/et_pb_toggle][et_pb_toggle title=\u201dLINK EVERYTHING YOU CAN\u201d _builder_version=\u201d4.23.1\u2033 _module_preset=\u201ddefault\u201d hover_enabled=\u201d0\u2033 global_colors_info=\u201d{}\u201d sticky_enabled=\u201d0\u2033]\n
Most people have hard-coded, manually-entered data all throughout their Excel files. This isn\u2019t just inefficient, it\u2019s irresponsible! It\u2019s virtually impossible to review every piece of data in most workbooks, so it\u2019s crucial to minimize the inputs and have one source of the truth. Link all your analysis and reports to those few inputs and the source data itself in a way that\u2019s easy to understand for others (and your future self!).<\/div>\n
Pro tip<\/strong>: Another place you might store data values is in lookup sheets\/tables. If you have data that will be used over and over again throughout your workbook, consider putting it in a lookup table so there is only one place to update as the values change.<\/div>[\/et_pb_toggle][et_pb_toggle title=\u201dUSE TEMPLATES\u201d _builder_version=\u201d4.23.1\u2033 _module_preset=\u201ddefault\u201d hover_enabled=\u201d0\u2033 global_colors_info=\u201d{}\u201d sticky_enabled=\u201d0\u2033]\n
Highly related to linking everything you can is leveraging templates. I\u2019ve seen too many people run a raw report and manually make all kinds of formatting, structure, and calculation updates. Those changes aren\u2019t necessary! Ideally that source data is in a clean, tabular format you can use for your Source Data sheet. Even if it\u2019s not, you can often leverage the other items on this list (Power Query, Macros, and Dynamic Array Functions) to clean it up. The key is to leverage previous effort as much as you can. That might take a change in mindset!<\/div>\n
Pro tip<\/strong>: I considered making the Instructions sheet it\u2019s own time hack \u2013 that\u2019s how important it is. We all spend more time than we\u2019d like troubleshooting and rethinking the process. Include instructions and notes that are clear, simple, and comprehensive to make sure that you and others leverage your template as intended!<\/div>[\/et_pb_toggle][et_pb_toggle title=\u201dUSE DATA VALIDATION\u201d _builder_version=\u201d4.23.1\u2033 _module_preset=\u201ddefault\u201d hover_enabled=\u201d0\u2033 global_colors_info=\u201d{}\u201d sticky_enabled=\u201d0\u2033]
<\/p>\n
You\u2019ve heard the mantra \u201cgarbage in, garbage out\u201d and defines Excel possibly more than any other piece of software out there. Data validation gives you several options to guide users on how to enter data with text, number, date, drop-down list, and custom formula options. Keep in mind they aren\u2019t bullet-proof \u2013 without protecting cells, it\u2019s easy to clear or change data validation settings. If input data is crucial, it\u2019s best to further verify accuracy with macros, a thorough review, or tools outside of Excel.<\/div>\n
Pro tip<\/strong>: Dynamic drop-down lists \u2013 those that automatically update as the source list changes \u2013 are a great way to guide users. Custom formulas offer a ton of flexibility as well, such as requiring a valid phone number or email address format.<\/div>[\/et_pb_toggle][et_pb_toggle title=\u201dUSE DYNAMIC ARRAY FUNCTIONS\u201d _builder_version=\u201d4.23.1\u2033 _module_preset=\u201ddefault\u201d hover_enabled=\u201d0\u2033 global_colors_info=\u201d{}\u201d sticky_enabled=\u201d0\u2033]\n
Dynamic Array Functions (DAFs) are one of the biggest enhancements in Excel\u2019s history. By updating and spilling into other cells automatically, you\u2019re able to simplify formulas, use fewer helper columns, sheets, and manual manipulation steps.<\/div>\n
Pro tip<\/strong>: DAFs are even more powerful if you combine them together! Consider a data set you need to transpose, then filter, then reduce to unique values, then sort. These steps can all be performed with nested DAFs!<\/div>[\/et_pb_toggle][et_pb_toggle title=\u201dUSE CONDITIONAL FORMATTING\u201d _builder_version=\u201d4.23.1\u2033 _module_preset=\u201ddefault\u201d hover_enabled=\u201d0\u2033 global_colors_info=\u201d{}\u201d sticky_enabled=\u201d0\u2033]\n
Of all the valuable features in Excel, conditional formatting might be the most underused. It helps you find errors, exceptions, variances, blanks, duplicates, and just about any kind of numeric or text search you can throw at it using custom formulas. It also helps you add a polished look to your data automatically \u2013 changing with your data.<\/div>\n
Pro tip<\/strong>: Keep in mind that conditional formatting rules are based on priority and override fixed formatting. Before tearing your hair out wondering why your formats don\u2019t work, review the conditional formatting rules on the sheet and verify their priority.<\/div>[\/et_pb_toggle][et_pb_toggle title=\u201dUSE CHECKS AND BALANCES\u201d _builder_version=\u201d4.23.1\u2033 _module_preset=\u201ddefault\u201d hover_enabled=\u201d0\u2033 global_colors_info=\u201d{}\u201d sticky_enabled=\u201d0\u2033]\n
We often look at data in several different ways, even calculating differently (i.e. top-down vs. bottom-up). It is vital to ensure that the sum of the parts equals the whole. Comprehensive tie-outs, checks, and balances are very helpful for ensuring completeness and accuracy. Anywhere you compare numbers between files and sheets, consider comparing them and call them out when they don\u2019t match.<\/div>\n
Pro tip<\/strong>: Use conditional formatting so checks easily stick out. Collapse or hide them when they would be otherwise distracting.\nKeep a central, linked list of all the checks throughout your file so you can see them all in one place.<\/div>[\/et_pb_toggle][et_pb_toggle title=\u201dSAVE FILES THOUGHTFULLY\u201d _builder_version=\u201d4.23.1\u2033 _module_preset=\u201ddefault\u201d hover_enabled=\u201d0\u2033 global_colors_info=\u201d{}\u201d sticky_enabled=\u201d0\u2033]\n
It\u2019s amazing how much time we spend \u2013 hundreds of hours a year \u2013 looking for information, including digital files of all kinds . It is crucial to be consistent and specific when naming files, including where you save them. Many years ago, I accidentally stumbled upon the approach above \u2013 ending the file with a \u201c_YYYY-MM-DD\u201d date suffix. The mere act of typing the date has often helped me find files quickly, at least by narrowing down within a few months of when I created them.<\/div>\n
Pro tip<\/strong>: If you can, have Excel name the file for you! I use several macros that help ensure that I save files with consistent names and locations.<\/div>[\/et_pb_toggle][et_pb_text _builder_version=\u201d4.23.1\u2033 _module_preset=\u201ddefault\u201d global_colors_info=\u201d{}\u201d]
Summary<\/strong><\/h3>\n
Excel is more powerful than 99% of people give it credit for \u2013 so much more than just spreadsheets.\u00a0 Learning the time hacks above \u2013 and how they work together \u2013 will make you to go-to expert in your company and your sphere of influence.\u00a0 Like me, it can have a profound impact on your career, and prevent a lot of late nights performing work the slow way.\u00a0 I can\u2019t make you choose to be more efficient in Excel (or any aspect of life really), but I can tell you it sure is worth it!<\/p>\n
What\u2019s your favorite Excel time hack?\u00a0 How would you change the list above?\u00a0 Let us know in the comments below!<\/p>\n
\u00a0<\/p>[\/et_pb_text][et_pb_code _builder_version=\u201d4.23.1\u2033 _module_preset=\u201ddefault\u201d global_colors_info=\u201d{}\u201d]