Excel novices are often pleasantly surprised to learn how many similarities exist between Microsoft products like Excel, Word, and PowerPoint.
You’re not stuck with regular ol’ text inside of Excel. You can adjust some or all of your spreadsheet’s contents to bold, italic, or underlined numbers and letters.
You can also format your numbers, dates, percentages, and financial information nearly any way you want. For example, if your spreadsheet contains dates, you can select either the Short Date or Long Date format. If your spreadsheet deals with something money-related, you can select either the Currency or Accounting format.
Screencast: Demonstrating Number Format drop-down window.
You’ve also got control over how many decimal places are shown. Pretend you’re dealing with numbers with loooooooots of decimal places. Rather than deleting the extra decimal places by hand, just click on the Increase Decimal or Decrease Decimal buttons and let Excel do the heavy-lifting for you.
Screencast: Long numbers and increasing/decreasing the number of decimal places shown.
Colors are highly customizable in Excel, just like in Word and PowerPoint.
Change your font’s color by selecting the cell(s) you want to re-color and then clicking on the Font Color button on the Home tab. Or, fill in your cells completely by selecting the cell(s) you want to re-color and then clicking on the Fill Color button on the Home tab.
Screenshot: The Font Color and Fill Color buttons.
I primarily use SmartArt within my PowerPoint slides or Word documents, but sometimes the occasional diagram is useful in my Excel file, too. Head over to the Insert tab and click the SmartArt button to see all your options. I typically tweak the colors and fonts used in SmartArt to make my graphics appear more customized and less, well, generic.
Screenshot: SmartArt menu
Screencast: Inserting the Udemy logo into the Excel sheet
Screencast: Inserting a hyperlink
Screenshot with arrows pointing to the various feature
When you’re staring at a spreadsheet for hours on end, a little organization can go a long way.
First, make sure your data is arranged in a tabular format. You’ll want contiguous cells (aka touching cells). Try not to skip any rows or columns if typing in data by hand. If you need more space, try wrapping text or re-sizing the row or column to make it a little larger, rather than skipping a couple columns altogether. This layout will make it easier for you to use functions or pivot tables later on.
Automatically to fit the cell’s contents
When I’ve got more than 30 or so rows of information in my spreadsheet, I have to scroll up and down to see everything. I like to keep my column headers in view even while I’m scrolling down the bottom of the dataset so I can remember what’s what.
Screencast: Freezing the first row
Similarly, when I’ve got lots of columns in my spreadsheet, I like to keep the left-most column in view while I’m scrolling over to the right in my dataset (since the first column usually contains important details like the name of a person or organization).
Screencast: Freezing the first column
Bonus! The best of both worlds is freezing both your rows and columns.
Screencast: Freezing the first row and the first column
CAREFUL, there’s no way to un-do this action.
Excel assigns generic, forgettable names to new sheets, like Sheet 1, Sheet 2, and Sheet 3. To keep myself organized, I rename my sheets. I choose simple names that describe the contents of each sheet, like Dataset, Tables, and Charts. To re-name your sheets, simply right-click on the sheet and select Rename.
Screencast: Right-clicking on “Sheet 1” and renaming to something more memorable.
When I’m sending spreadsheets to colleagues, I want to make sure they spot the most important sheet as soon as they open the file. I’ll turn that crucial sheet bright yellow or bright red to draw extra attention to it.
Screencast: Turning one sheet bright yellow.
For alphabetizing, arranging numbers from greatest to least or least to greatest, or for putting a temporary filter on your information so that you can just pay attention to a subset of your spreadsheet at one time
This technique is familiar to most people I speak with.
Screencast: Demographics file; sort by gender and then by age.
Have you seen the Excel’s Filters option yet? It’s hiding within plain sight on the Home tab. Filters are one of my all-time favorite features of Excel because, for me, they’re even easier to use than sorting, and they save valuable time as I’m wading through so many numbers.
Screencast: Inserting filters in demographics file.
Bonus! Use your new filters to arrange your dataset by colors. Sometimes I use red, yellow, or green colors to indicate whether items are high, medium, or low priorities for me. I can filter my dataset so that the reds appear on top, followed by the yellows, followed by the greens.
Screencast: To-do list with items prioritized and color-coded, and then sorted by color.
I love Excel but that doesn’t mean I want to spend more time than necessary using it. Instead, I use these time-saving strategies to save my mental energy for tasks that are more important. Here are my top __5__ techniques everyone should know.
Sample dataset: Names
Use the buttons to format your dates consistently and in the format of your choosing, e.g., Short Dates or Long Dates.
Sample dataset: Names
Two options: concatenate and &. Use either strategy – it’s your choice. They do the same thing so it’s more of a personal preference than anything else.
Use this term to impress your friends at cocktail parties. Literally means combining things into a chain.
“The and operator,” as it’s called, is nearly identical to the concatenate function.
Define “function” and “formula” and then explain that the difference isn’t too important because so many people use these terms interchangeable anyway
Like I mentioned above, I use Excel’s full menu of formulas to get ideas for formulas I might want to use in industry-specific projects
“Formulas that help you get promoted at your job” or something along these lines
Tell story of how I was tested at LAYC; used pivot tables, solved the practice problems with 5 minutes, and received a job offer later that same day.
Explain purpose: Combining two datasets (example: demographics plus mental health counseling scores)
Show difference between vlookup and hlookup