I know what you’re thinking… “Excel? why would I use that? I’m a serious data analyst with\u00a0SQL superpowers<\/a>“.<\/p>\n\n\n\n
Here are 25 tips, tricks, tweaks to make it work for you.<\/p>\n\n\n\n
Getting set up<\/a>
Shortcuts<\/a>
Formatting<\/a>
Tools<\/a>
Formulas and Functions<\/a>
Security<\/a><\/strong><\/p><\/blockquote>\n\n\n\n
\n\n\n\nGetting set up<\/h2>\n\n\n\n
If you’re like most people you probably don’t spend a lot of time customising your Excel experience using File > Options.<\/strong><\/p>\n\n\n\n
By exploring the menus here and making a few tweaks you can make your experience more ‘you’ and avoid having to make the same change manually each time you open a workbook.<\/p>\n\n\n\n
\n\n\n\n1 – Hide the things you don’t need<\/h4>\n\n\n\n
If you’d like to free up real estate by hiding away the search bar you can do so in File > Options > General.<\/strong><\/p>\n\n\n\n
\n\n\n\n<\/a><\/figure>\n\n\n\n
\n\n\n\n2 – Change what happens when you press enter<\/h4>\n\n\n\n
You may not want the selection to move down when you press enter. To change this to right, left, or even up use the option in File > Options > Advanced.<\/strong><\/p>\n\n\n\n
\n\n\n\n3 – Customise the ribbon<\/h4>\n\n\n\n
There’s no reason to keep the Ribbon as it is ‘out of the box’. Add what you need and hide what you don’t in File > Options > Customize Ribbon.<\/strong><\/p>\n\n\n\n
\n\n\n\n<\/a><\/figure>\n\n\n\n
\n\n\n\n4 – Make a map<\/h4>\n\n\n\n
Microsoft PowerMap, and other useful add-ins, can be set up from File > Options > Customize Ribbon.<\/strong><\/p>\n\n\n\n
DISCLAIMER: This may not be available on all operating systems<\/em><\/p>\n\n\n\n
\n\n\n\n<\/a><\/figure>\n\n\n\n
\n\n\n\n
\n\n\n\nShortcuts<\/h2>\n\n\n\n
Shortcuts will save you time clicking through menus and remembering where to find what you need.<\/p>\n\n\n\n
5 – One click to select all<\/h4>\n\n\n\n
Selecting all cells is a useful way to make a change to everything in your workbook, whether that’s the cell colour, font, or adding borders to all.<\/p>\n\n\n\n
This can be done by either clicking the Select All button at the top left corner or using CTL+A.<\/strong><\/p>\n\n\n\n
\n\n\n\n<\/a><\/figure>\n\n\n\n
\n\n\n\n6 – Highlighting large numbers of rows<\/h4>\n\n\n\n
When your sheets start getting large you can spend more time than you’d like navigating around. Save time by highlighting large areas of data by using CTL+SHIFT+up\/down\/left\/right<\/strong>. That way you won’t spend your day scrolling forever.<\/p>\n\n\n\n
\n\n\n\n7 – Paste special<\/h4>\n\n\n\n
You probably know you can use CTL+C<\/strong> to copy, and CTL+V<\/strong> to paste. You may even know about the Paste Special menu. But did you know you can use keyboard shortcuts to do it all?<\/p>\n\n\n\n
- Values only \u2013\u00a0Alt+E+S+V + Enter<\/strong><\/li>
- Formatting only \u2013\u00a0Alt+E+S+T + Enter<\/strong><\/li>
- Comments only \u2013\u00a0Alt+E+S+C + Enter<\/strong><\/li>
- Set column width same as copied cells \u2013\u00a0Alt+E+S+W + Enter<\/strong><\/li><\/ul>\n\n\n\n
\n\n\n\n<\/a><\/figure>\n\n\n\n
\n\n\n\n8 – Paste formatting with the paintbrush<\/h4>\n\n\n\n
If you like the look of a formatted area, you can quickly use the format paintbrush to apply it anywhere else you like.<\/p>\n\n\n\n
Highlight the area you have in mind, click the paintbrush, then click the destination area to copy it over.<\/p>\n\n\n\n
If you have multiple areas to format just double-click the paintbrush.<\/p>\n\n\n\n
\n\n\n\n<\/a><\/figure>\n\n\n\n
\n\n\n\n9 – Formulas without the formulas<\/h4>\n\n\n\n
To see SUM, COUNT, AVG super quickly, there’s no need to use formulas. Just highlight the data you need and check out the bottom right corner for the result.<\/p>\n\n\n\n
\n\n\n\n<\/a><\/figure>\n\n\n\n
\n\n\n\n10 – Fill down fast<\/h4>\n\n\n\n
If you have data that you need to copy down a column you can do it with a double-click. Hover in the bottom right corner of the cell until a small black cross appears, then pull down to copy down.<\/p>\n\n\n\n
\n\n\n\n11 – Format the column to fit the text<\/h4>\n\n\n\n
To quickly format one or more columns to be the right width for your data, position your mouse over the right border of the column heading until the double-headed arrow appears, and then double-click the border.<\/p>\n\n\n\n
\n\n\n\n<\/a><\/figure>\n\n\n\n
\n\n\n\n12 – Add a new line to a cell<\/h4>\n\n\n\n
In Excel, if you hit Enter you will move to the next cell. If you’d rather have a new line in the same cell use ALT+Enter<\/strong> instead.<\/p>\n\n\n\n
\n\n\n\n
\n\n\n\nFormatting<\/h2>\n\n\n\n
Now it’s time for some fun with formatting. These tips make it easy to make your numbers look great.<\/p>\n\n\n\n
\n\n\n\n13 – Remove gridlines for cleaner dashboards<\/h4>\n\n\n\n
Gridlines are great when wrangling data, not so great when presenting dashboards. These can be turned off by finding the option in the View<\/strong> menu on the ribbon.<\/p>\n\n\n\n
\n\n\n\n<\/a><\/figure>\n\n\n\n
\n\n\n\n14 – Rotate headings<\/h4>\n\n\n\n
Headings don’t need to stay horizontal. If you have narrow columns they may look better rotated on an angle or even vertical. Find this option on the ribbon under Home > Alignment.<\/strong><\/p>\n\n\n\n
\n\n\n\n<\/a><\/figure>\n\n\n\n
\n\n\n\n15 – Wrap text<\/h4>\n\n\n\n
While we are on the alignment menu this is also a good time to bring up the other options here. Use Wrap Text<\/strong> to wrap extra-long text across multiple lines.<\/p>\n\n\n\n
\n\n\n\n<\/a><\/figure>\n\n\n\n
\n\n\n\n16 – Merge cells<\/h4>\n\n\n\n
If you have text that needs to be merged across multiple cells you can find Merge & Center<\/strong> here to do just that.<\/p>\n\n\n\n
\n\n\n\n17 – Conditional formatting<\/h4>\n\n\n\n
The quickest way to get a feel for trends in your data is to add some conditional formatting. You can find this menu either on the Ribbon under Home > Styles<\/strong> or when highlighting a data set.<\/p>\n\n\n\n
There’s more to it than just highlighting the top and bottom results. You can add icons, data bars, sparklines, or add some logic to make sure the right data points stand out.<\/p>\n\n\n\n
\n\n\n\n<\/a><\/figure>\n\n\n\n
\n\n\n\nTools<\/h2>\n\n\n\n
18 – Split a cell in two<\/h4>\n\n\n\n
This is probably one of my favourite tools in Excel to use when cleansing data. To split a cell by a space, a symbol, a letter, or any other text qualifier use the Text to Columns<\/strong> tool under Data Tools.<\/strong><\/p>\n\n\n\n
\n\n\n\n<\/a><\/figure>\n\n\n\n
\n\n\n\n19 – Remove duplicates<\/h4>\n\n\n\n
Alongside the Text to Columns<\/strong> tool you’ll find the Remove Duplicates<\/strong> tool. Highlight a column where you suspect there are duplicates and click the button to delete the duplicated data.<\/p>\n\n\n\n
If you’d prefer to use a formula to do this use:<\/p>\n\n\n\n
=UNIQUE(A:A) -- returns a list of distinct values from column A\n<\/code><\/pre>\n\n\n\n
\n\n\n\nFormulas and Functions<\/h2>\n\n\n\n
There are hundreds of functions in Excel that can be used from anything from cleansing text to forecasting and adding complex logic.<\/p>\n\n\n\n
20 – Clean up text<\/h4>\n\n\n\n
Cells with text can be quickly converted to the same case with the help of functions. This is especially useful when a csv or sheet of names or email addresses needs to be cleansed.<\/p>\n\n\n\n
=UPPER(A:A) -- returns a list of UPPER case text from column A\n=LOWER(A:A) -- returns a list of lower case text from column A\n=PROPER(A:A) -- returns a list of Proper case text from column A<\/code><\/pre>\n\n\n\n
\n\n\n\n21 – Trim<\/h4>\n\n\n\n
To further clean up text, use TRIM to remove trailing and leading spaces.<\/p>\n\n\n\n
=TRIM(A:A) -- removes leading and trailing spaces <\/code><\/pre>\n\n\n\n
\n\n\n\n22 – Concatenate quickly<\/h4>\n\n\n\n
To join cells together, maybe if you’ve been supplied a First Name and Last Name column and want a Full Name column, use CONCATENATE.<\/strong><\/p>\n\n\n\n
=CONCATENATE(H15, ,H16) -- concatenates H15 and H16 into one cell with a space between<\/code><\/pre>\n\n\n\n
\n\n\n\n23 – Count text<\/h4>\n\n\n\n
You may have heard of the COUNT<\/strong> function which counts the number of cells in a range that contains numbers. But there are more options in this function family.<\/p>\n\n\n\n
=COUNTA(H5:H29) -- counts the number of cells in a range which are not empty \n=COUNTIF(E65:E76,2015) -- counts the number of cells in a range which meet a condition, in this case =2015\n<\/code><\/pre>\n\n\n\n
\n\n\n\n24 – Create a countdown<\/h4>\n\n\n\n
Returning the system time and date is a useful function to create a countdown. Use it on a sales dashboard to countdown till the end of the month, or count down till your trip on a sheet where you track savings goals.<\/p>\n\n\n\n
=TODAY() -- returns the current date\n=NOW() -- returns the current date and time<\/code><\/pre>\n\n\n\n
\n\n\n\nSecurity<\/h2>\n\n\n\n
25 – Protect your sheet<\/h4>\n\n\n\n
There’s nothing worse than sending a sheet and finding someone has edited a cell in error. Prevent this by using the options in the ribbon under Review<\/strong> to protect cells or the whole workbook.<\/p>\n\n\n\n
\n\n\n\nExcel is easy to learn and allows analysts and stakeholders to speak the same language. While it doesn’t replace databases and BI tools for regular reporting, Excel may be just what you need for quick analysis and cleansing of small data sets.<\/p>\n\n\n\n
What are your most-used Excel functions and tools?<\/p>\n\n\n\n
\n\n\n\nPhoto by\u00a0bongkarn thanyakij<\/a><\/strong>\u00a0from\u00a0Pexels<\/a><\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"
![Set column width same as copied cells \u2013\u00a0Alt+E+S+W + Enter<\/strong><\/li><\/ul>\n\n\n\n\n\n\n\n](\"https:\/\/res.cloudinary.com\/practicaldev\/image\/fetch\/s--nz5Cp2La--\/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880\/https:\/\/dev-to-uploads.s3.amazonaws.com\/i\/91jvvjcko5c6bygjud33.png\"){kind=link}
![<\/a><\/figure>\n\n\n\n\n\n\n\n](\"https:\/\/res.cloudinary.com\/practicaldev\/image\/fetch\/s--CBI-Vn3u--\/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880\/https:\/\/dev-to-uploads.s3.amazonaws.com\/i\/k2o48qw56psdklu1sph8.png\"){kind=link}
![<\/a><\/figure>\n\n\n\n\n\n\n\n](\"https:\/\/res.cloudinary.com\/practicaldev\/image\/fetch\/s--dpGyo_0C--\/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880\/https:\/\/dev-to-uploads.s3.amazonaws.com\/i\/rlgbt4l43slqppqolwn2.png\"){kind=link}
![<\/a><\/figure>\n\n\n\n\n\n\n\n10 – Fill down fast<\/h4>\n\n\n\nIf you have data that you need to copy down a column you can do it with a double-click. Hover in the bottom right corner of the cell until a small black cross appears, then pull down to copy down.<\/p>\n\n\n\n\n\n\n\n](\"https:\/\/res.cloudinary.com\/practicaldev\/image\/fetch\/s--0RTfQKff--\/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880\/https:\/\/dev-to-uploads.s3.amazonaws.com\/i\/x9luqpfho1akhki6uj89.png\"){kind=link}
![<\/a><\/figure>\n\n\n\n\n\n\n\n](\"https:\/\/res.cloudinary.com\/practicaldev\/image\/fetch\/s--EDe7pAgj--\/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880\/https:\/\/dev-to-uploads.s3.amazonaws.com\/i\/8u6v1hkpf6u78w0kpes4.png\"){kind=link}
![<\/a><\/figure>\n\n\n\n\n\n\n\n](\"https:\/\/res.cloudinary.com\/practicaldev\/image\/fetch\/s--YVn2BXqu--\/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880\/https:\/\/dev-to-uploads.s3.amazonaws.com\/i\/hsh0xogsbnf7tndlu1be.png\"){kind=link}
![<\/a><\/figure>\n\n\n\n\n\n\n\n](\"https:\/\/res.cloudinary.com\/practicaldev\/image\/fetch\/s--34Pb6fp_--\/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880\/https:\/\/dev-to-uploads.s3.amazonaws.com\/i\/5ayh2d0rxgz4tmbqwu4x.png\"){kind=link}
![<\/a><\/figure>\n\n\n\n\n\n\n\n](\"https:\/\/res.cloudinary.com\/practicaldev\/image\/fetch\/s--v1GHExtk--\/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880\/https:\/\/dev-to-uploads.s3.amazonaws.com\/i\/v9sx8vt9zf7d3zxvei0x.png\"){kind=link}
![<\/a><\/figure>\n\n\n\n\n\n\n\nTools<\/h2>\n\n\n\n](\"https:\/\/res.cloudinary.com\/practicaldev\/image\/fetch\/s--3Vy5MtL7--\/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880\/https:\/\/dev-to-uploads.s3.amazonaws.com\/i\/e8524m9woxwwcjjrkcte.png\"){kind=link}
![<\/a><\/figure>\n\n\n\n\n\n\n\n](\"https:\/\/res.cloudinary.com\/practicaldev\/image\/fetch\/s--BZXjk_qw--\/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880\/https:\/\/dev-to-uploads.s3.amazonaws.com\/i\/4kp72i6h4ekqmrddniwq.png\"){kind=link}