Apps - The Productive Engineer

Microsoft Excel is renown for its data management and spreadsheet functionality. It can handle a lot of crucial data with ease and precision. Microsoft Excel is extremely flexible and there are a lot of ways we can use it. Excel is unsurpassed in terms of feature-functionality. It follows that Excel is the go-to tool for anyone looking to analyze data. This blog post will give you 25 cool tips for using Excel. These tips and tricks will help you in your day-to-day use of Excel. My hope is that a couple of these tips help you become more productive and efficient using Excel. Listed below are 25 Tips for Using Microsoft Excel: 1. Interchange Columns into Rows 2. Hiding a Cell 3. Freeze Row or Column Headings 4. Drag Cell to Copy Value to Other Cells 5. Deleting Blank Cells from a Column 6. Using Flash Fill in Excel 7. Adding Comments to Custom Formulas 8. Using the SUM Function 9. Rotate Heading Text 10. Add Decimal Points Quickly 11. Saving Charts as Template 12. Adding Calculator in Toolbar 13. Applying Conditions 14. Sketching Equations 15. Fast Navigation 16. Adding Values Starting from 0 17. Rename sheet quickly 18. Using & Symbol 19. Transform Text Case 20. Autocorrect Function 21. Generate Random Value 22. Input Restrictions 23. Applying Diagonal Borders 24. Adding More than one Row or Column 25. Some Important Shortcut Keys Interchange Columns into Rows In Excel, it’s possible to interchange data from Columns into Rows and vice versa. If you’ve accidentally put data in the wrong format, then you can easily interchange it. It’s pretty simple. To interchange columns into rows in Excel, do the following: Copy the block of Cells you want to interchange. 2. Right-click on the destination cell 3. Choose Paste Special -> Paste Special 4. The Paste Special menu will appear, check the Transpose box and click Ok Congratulations! You’ve interchanged cells successfully. Hiding a Cell There are a lot of tricks in Excel. One of them is hiding cells. It can be convenient to hide certain cells when working in a spreadsheet. Doing so is straightforward. To hide a cell or cells in Excel, follow the steps listed below: Select the cell(s) you want to hide Right-click and select Format Cells Click on the Number tab Set the format as Custom In the Type text bar, type ";;;" and click Ok If you look at the screenshot above, the cell (B3) shows blank in the sheet but when you click on the cell, the value does appear in the cell entry text box. This works where it is text, numbers or even formulas. Freeze Rows or Column Headings This is a simple yet very useful trick to use in Excel. You can freeze the row and column headings. Therefore, no matter how much you scroll you will still see these rows and columns. This trick is rather simple to implement. To freeze a row or column heading in Excel, do the following: Open your spreadsheet 2. Click on the View Tab and select one of the following options: Freeze Panes - Freezes the top row and first columnFreeze Top Row - Freezes the top rowFreeze First Column - Freezes first column Your first row and/or column will be visible as you scroll. Drag Cell to Copy Value to Other Cells If you need to copy the value of a cell to neighboring cells, here is a quick tip. Excel allows you to quickly copy the value of a cell to neighboring cells by dragging the corner of the cell. To copy the value of a cell to neighboring cells in Excel, do the following: Select the Cell you want to copy. Hover over the corner of the cell until the cursor turns into a crosshair. Click and drag the cell across the cells you want to copy to. Release the mouse button and the value of the original cell will be copied into all of the destination cells. Deleting Blank Cells from a Column When working with a column of data, there can be blank values in some of the cells. Sometimes, you might want to delete all the cells with blank values in a given column. This functionality is easily achievable in Excel. To delete blanks cells in a column in Excel, do the following: Select the column that you want to use Click on the Data tab Click on the Filter button. This will add a filter drop-down to the top cell of your column. Uncheck Select All Scroll to the bottom of the list and check (Blanks) The cells with blank values should be the only lines showing and the row numbers should be in a different color. Select the colored row numbers by holding down the Shift key and selecting the first and last row. Go to Home and click on the Delete button. This will remove the rows. Undo your filter by clicking on the drop-down button in the top cell of your column and check Select All. Your column should have all of your values minus the blank cells. Using Flash Fill in Excel Excel is a really intelligent application. One of the ways Excel is intelligent is that it can sense patterns in data entry. This is really useful when you want to fill a bunch of cells that follow a specific pattern. An example of this functionality would be in a contact spreadsheet where you have the full name and email handle and need to populate separate columns for first and last name. We will use this example to show how Flash Fill works. To use the Flash Fill feature in Excel, do the following: Open the spreadsheet you want to use Fill in the first row of your spreadsheet with the information you want to flash fill for the other cells Click on the next cell below the filled-in cell and go to Data. Press the Flash Fill button. This feature can be a great time saver as it minimizes repetitive data entry. Adding Comments to Custom Formulas Excel allows you to create custom formulas to calculate values. Depending on the sophistication of the formula, it may make sense to add some comments to clarify what the formula is doing. Excel supports the ability to add comments to formulas using the N() function. To add comments to a formula in Excel, do the following: Write your formula At the end of your formula, append the formula by typing + N("Comments") where Comments are your actual comments. The value of the cell will remain the result of your formula but now you have an in-formula comment explaining the logic of your formula. Using the SUM Function Adding a list of value up is a core feature of any spreadsheet. I find myself constantly summing a column or row of values. Excel has a wide variety of built-in functions but the most commonly used one, for me, is the SUM function. The SUM function calculates the sum of a list of values. To use the SUM function in Excel, do the following: Click on the cell where you want the sum to display Type =sum( and then select the cells you want to add together and press Enter The sum of the selected numbers should now appear in your cell. Rotate Heading Text While we are all used to entering our column headings as horizontal entries, it can be valuable to have your column heading be vertical. Excel makes it easy to rotate your column headings. To rotate your column headings, do the following: Select the cells you want to rotate Go to the Home tab and click on the Orientation button From the sub-menu, select the Orientation you want Your cells should now have the desired orientation. Add Decimal Points Quickly Working in Excel, you typically will need to use decimal numbers, whether it's currency or simply calculating values with decimal precision. Excel excels (forgive the pun) at formatting values. To format the value to a set level of decimal precision, do the following: Select the cell you want to set the decimal precision for. Right-click on the cell and select Format Cells... Click on Number in the Category section and at the Decimal places text box, type in the number of decimal places the cell should display. Press Ok when done. Your cell should display the number with the number of decimal places you set. Saving Charts as Templates This trick can make you feel like a master of Excel. You can save a combination of layout and colors which you think are pretty important to you, The significance of saving as a template is that you can use the template again and again if you want. To save a chart as a template in Excel, do the following: Select the chart you want to save as a template Right-click on the chart and select Save as Template... Type in the name you want to assign to your template and press Save. Your chart template is now available as a formatting option for your future charts. This is helpful if there is a color scheme you like as an example. Add Calculator in Toolbar Excel gives you several shortcut controls. For example, you can add shortcuts such as calculators, camera or other apps. Shortcuts provide simplified access to the tools you most commonly use. To create a custom shortcut in Excel, do the following: Click on the drop-down arrow at the top of your Excel window Scroll down to the bottom of the menu and select More Commands... Select the command(s) you want to add to your toolbar and press the > arrow button and press Save. Your command will now be available for use in the Quick Access Toolbar. Apply Conditions Excel provides you the options to set certain conditions and rules for your cells. You can do this by adding conditional formatting. It’s really simple to use. To set up conditional formatting in Excel, do the following: Select the cells you want to format and press the Conditional Formatting button. There are a bunch of formatting options you can choose from. For this demo, we will choose New Rule... Pick your style and criteria using the Style and Type controls and press OK when done. Your cells should have the formatting you specified. Sketching Equations This feature of Excel 2016 or later (Windows only) allows you to draw equations. This is very useful if you have a touchscreen laptop or pc. It’s pretty simple to do. To draw equations in Excel for Windows, do the following: Click on the Insert TabSelect EquationSelect Ink Equation After that, a yellow box will appear. You can start drawing in the yellow box. The yellow box automatically detects what you’ve typed in that box. Fast Navigation It’s often tiring trying to go down to the last cell. Rather than having to scroll through a bunch of rows, Excel has made it easy for you. To quickly navigate to the last populated cell in an Excel worksheet, do the following: Open the worksheet Press the keyboard shortcut for your platform from the table below PlatformKeyboard ShortcutWindowCTRL + Down ArrowMacCommand + Down Arrow Adding Values Starting with 0 Let’s say that you want to input numbers that start with zero. An example where you might want to do this is for creating invoice numbers. The problem is, by default, Excel automatically deletes the lead zero(es) from numbers that start with zero. However, you can configure the cells to support lead zeroes. To enable lead zeroes for numbers in Excel, do the following: Select the cell(s) you want to enable lead zeroes for Right Click on the cells and select Format Cells Select Custom and in the Type text box, enter the number of zeros to accommodate the max value you want to support. For example, if you want the ability to support a max of 999 values, you would enter three zeroes. Once you have entered the numbers, click Ok. If you type in a number, it will add the zeroes automatically as shown above. Rename Sheet Quickly Renaming a worksheet in Excel is really easy. Simply do the following: Double-click on the worksheet tab Type in the name you want to assign the worksheet and press Enter. Using the & Symbol The & sign is a pretty useful operator in programming languages. But in Excel, it’s used to concatenate (join) strings together. Consider you got 3 cells. Each has a different string. You want to make one string out of them. Excel supports this using the & symbol. To concatenate text strings in Excel, do the following: In the worksheet that you want to use, click on an open cell Type = and select the first cell. Add the & symbol. This tells Excel that there will be another string being added. If you want a space between the strings you are combining, use " " to add a space. An example of combining three strings with a space between each string would look like the equation below: =A5&" "&B5&" "&C5 A5, B5, and C5 are the cell references for each string. Transform Text Case Excel allows you to convert the case of the text in a cell to the following: Uppercase -> all characters are UPPERCASELowercase -> all characters are lowercaseProper case -> Only the first character of each word is capitalized Excel accomplishes this using the UPPER, LOWER and PROPER functions. To set the text to uppercase in Excel, use the UPPER function. To set the text to lowercase in Excel, use the LOWER function. To set the text to proper case in Excel, use the PROPER function. Autocorrect Function Sometimes you might want to replace one string of text with another throughout your spreadsheet. Excel supports autocorrection. To setup autocorrect in Excel for Mac, do the following: Go to Excel -> Preferences in Main Menu Select AutoCorrect Type in the character or string you want to replace in the Replace text box and the replacement string in the With text box and press Add. Now, when you type the original phrase, it will be automatically replaced with the new phrase. In my example, everytime I type "John Doe", Excel replaces it with "Jane Doe". Generate Random Values You can generate unique values in Excel. To create a random number in Excel, do the following: Click on the cell you want to use to create the random number Type in =RANDBETWEEN(x,y) where the random number will be between x and y. For example, if you wanted a random number between 1 and 100, you would type: =RANDBETWEEN(1,100) Input Restrictions In some cases, you want to validate inputted data to ensure it complies with the expected data type of the cell. Consider an example of a date of birth. It shouldn’t contain any characters. Excel provides the ability to define your own conditions. To create input restrictions in Excel, do the following: Click on the Data tab and select Data Validation In the Allow drop-down menu, choose your allowed input type. If choosing a number, define the range of values you will allow. Then click the Input Message tab. Excel allows you to define an input message that will inform your users of what values are acceptable. Enter the input message and title and click on the Error Alert tab. The Error Alert tab allows you to configure an error message that will be displayed to the user if they attempt to enter an invalid value. Type in your error message and it's title and press Ok. When you click on the cell, your input message will display. If you attempt to type in an invalid value, your custom error will pop-up and instruct the user appropriately. Applying Diagonal Borders Most people know that you can add borders to cells in Excel. However, you can also add diagonal borders as well. To add diagonal borders in Excel, do the following: Select the cells you want to apply diagonal borders to Right-click on the cells and select Format Cells... Go to the Border tab and click on the diagonal border buttons. Press Ok. Your cells will now have diagonal borders. Adding More than One Row or Column A lot of users add rows or columns one row or column at a time. But if you want to add multiple rows or columns, there is an easy way to do it. To add more than one column or row at one time, do the following: Click on the row or column where you want to add and drag until you have the number of row/columns you want to add Right-click and select Insert from the drop-down menu These new rows will be inserted into the desired position. Some Important Keyboard Shortcuts There are some essential keyboard shortcuts you really should know in Excel. I have created the table below that contains some of my favorites: FunctionWindowsKeyboard ShortcutMac Keyboard ShortcutJump from first to last or last to firstCTRL-UP/DOWNCOMMAND-UP/DOWNSave your spreadsheetCTRL-SCOMMAND-SCreate a chartALT-F1OPTION-F1Create a new workbookCTRL-NCOMMAND-NOpen A New WorkbookCTRL-OCOMMAND-OOpen Format Cells menuCTRL-1COMMAND-1Add or remove a filterCTRL-SHIFT-LCOMMAND-SHIFT-LHide selected rowsCTRL-9CONTROL-9 Want More Tips and Tricks? Subscribe to our Newsletter! If you haven’t already subscribed, please subscribe to The Productive Engineer newsletter. It is filled with tips and tricks on how to get the most out of the productivity apps you use every day. We hate spam as much as you do and promise only to send you stuff we think will help you get things done. Newsletter Subscribe to our newsletter and stay updated. Enter your email address to subscribe Provide your email address to subscribe. For e.g abc@xyz.com SUBSCRIBE Terms & Privacy policy Check Out Our YouTube Channel! We have a YouTube channel now and we are working hard to fill it with tips, tricks, how-tos, and tutorials. Click the link below to check it out! link to our YouTube page Resources Page Check out our resources page for the products and services we use every day to get things done or make our lives a little easier at the link below: Link to resources page Article You May Be Interested In Ten Great Tips for Using Todoist Link to Ten Great Tips for Using Todoist A Beginner's Guide to Trello Link to A Beginner's Guide to Trello 7 Ways to Overcome Impostor Syndrome Link to 7 Ways to Overcome Impostor Syndrome How to Organize Todoist Guide How to Organize Todoist Guide