Search

Excel Tricks
11
Aug

Best Microsoft Excel Keyboard Shortcuts
  • Lynn Fountain, CPA
  • 1678
While using Excel, using keyboard shortcuts is one of the basic excel skills that save lots of time every day. There are more than 500 keyboard shortcuts, but not all of those are important. Here are some important and useful shortcuts. Basic Keys Key Description Control + Up Arrow move to the top end Control + Down Down Arrow move to the down end Control + Right Right Arrow move to the right end Control + Left Left Arrow move to the left end Control + C copy Control + V paste Control + X cut Control + S save Control + P print Control + F4 save as Control + Z undo the last action Control + Y redo the last action Control + A select all Control + Spacebar select entire column Shift + Spacebar select entire row Delete delete F2 edit cell Esc close edit mode F1 help Menu Button right-click menu Formatting Keys Key Description Ctrl + B bold Ctrl + I italic Ctrl + U underline Ctrl + ! open format menu Ctrl + Shift + @ format as time value Ctrl + Shift + # format as a date Ctrl + Shift + $ format as currency Ctrl + Shift + % format as percentage Alt → H → O → I / A adjust cell width to content Alt → H → B → A apply border Alt H F C change font color Alt H H change cell color Alt H A C align text to the center Alt H M C merge cell Alt H W wrap text Alt H F F change font style Alt H F S change font size Alt H L N add conditional formatting Alt H T format as table Alt H J style cell Insert & Layout Keys Key Description Alt N V insert pivot table Alt N T insert table Alt N P insert picture Alt N S H insert shape Alt N S C insert charts Alt N I insert hyperlink Alt N X insert text box Alt N J insert object Alt N U insert symbol Alt N H insert header & footer Alt N Z K open sparklines Alt W V F hide/unhide formula bar Alt W V H hide/unhide heading Alt W V G hide/unhide gridlines Alt W Q C change zoom Alt W F F freeze pane Alt W F I page break view Alt W F C custom view Alt W F P page layout view Alt W A arrange windows Functions and Data Shortcuts Key Description Shift + F3 insert function Alt M R recently used functions Alt M I financial functions Alt M L logical functions Alt M T text functions Alt M E date and time functions Alt M O lookup functions Alt M G math and trig functions Alt M Q more functions Alt + = auto sum Alt M N name manager Alt D F F add filters Alt A V V insert data validation Alt A M remove duplicates Alt A R A refresh all Alt A E open text to column Alt A G group rows and columns Alt A U ungroup rows and columns Alt A B add subtotal Alt A S S open sort Favorite Keyboard Shortcuts Key Description Alt H O R rename a worksheet Alt + F1 insert the default chart
  • Read More
01
Jul

Deliver Your Presentations Like A Boss - 3 Practical Steps
  • David Ringstrom
  • 399
Making impactful presentations does not have to be very complicated. Microsoft Excel provides many options to create powerful presentations that convey your audience effectively. These simple tips will make it possible for you to present better whether you are a beginner, intermediate or advanced MS Excel user. Stick to a theme uniform formatting is the best way to engage your audience. Don’t use random fonts and other characteristics; it can make it difficult for your audience to follow you. By using a simple set of rules can make your presentation more effective. Too much use of “USING.” Here are some guidelines: Use just one font. Bold all headings for groupings of data/formulae. Put comments on your analysis in italics. Shade cells with a color key – e.g., raw data shaded grey, calculations green, results yellow. Keep things coherent Your Worksheet must follow a natural and coherent progression throughout the spreadsheet (left-right, top-bottom). Otherwise, your data will look quite incompetent. For example, you can accomplish a Worksheets flow from inputs (assumptions and raw data) through to calculation, followed by results.  Highlight necessary data To make it easier for your audience to see essential data, you need to make it stand out using some easy steps: Visually soften the non-data elements of graphs and tables. For example, make axes and gridlines the same color but lighter than the data points. Set the ‘Print Area’ so that the critical areas (and only the critical areas) of a spreadsheet are printed. Make sure page boundaries are set sensibly, so each page of printing makes sense in itself. Microsoft Excel offers many ways to make your work smoother and more accessible. Sign up to Excel Accountant for more tips, tricks, shortcuts, and automation techniques to make your Worksheet more productive.
  • Read More
29
Jun

How to Make a Budget in Excel: Our Simple Step-by-Step Guide
  • Fedrica Hanks, Accountant
  • 1987
If you are not frequently using excel, then making a budget in excel is like an unnerving task. Using an Excel budget template, no need to be an accountant for that. This step-by-step guide will help you to create a budget in excel whether your budget is simple or extremely complex. The fastest and easiest way to build an excel budget is by using the library of premade budget templates. simply go to File>New, then search for the term “budget.” Several Excel budget templates will pop up, such as a family budget, personal expense calculator, vacation budget, and more. Customizing a premade template To customize the existing template, simply select where you want to add a box and right-click. Scroll down to “Insert” and choose to add either “Table Columns to the Left” or “Table Rows Above”. With the existing tabs, you can now automatically sync the new information. How to Make a Budget in Excel from Scratch You can create excel budget spreadsheet from the scratch. Step 1 – Open a Blank Workbook – Create “Blank Workbook” in excel. Step 2 – Set up Your Income Tab – after creating blank workbook, create Income and source tab. Step 3 - Step 3: Add Formulas to Automate- Make a cleaner look to your budget spreadsheet by adding border to entire area. Once you like the look of your budget spreadsheet, it’s time to add the formulas that will automatically calculate everything for you. Step 4- Add Your Expenses – After figuring out income section, now it’s time to calculate expenses. You can either do this on the same sheet or start a new sheet. To keep expenses on the same sheet, create a new area under the “Income” section and customize it according to you. Then, use the same column headings – Due Date, Planned, Actual, and Difference. Step 6 - The Final Balance - After creating all the sections you want to track. Now it’s important to know your running balance. Basically, you need not to keep any calculator with you. It’s become easy for you when you are keeping everything on one sheet. Simply create another section at the bottom of the sheet. Then, label one row “Total Spending” and another “Final Balance.” This is an easy way to help you track your spending.
  • Read More
17
Jun

Excel Accounting Latest Tips For 2021
  • Lynn Fountain, CPA
  • 1763
As per the report, 63 percent of U.S. companies rely on Excel, down slightly from a year ago. While the program is used by organizations of all sizes, smaller companies (69 percent) find accounting with Excel especially valuable. Even the American Institute of CPAs has replaced its generic spreadsheet with Excel beginning with the 2018 CPA exam.Excel is the preferred tool for budgeting and planning across accounting and finance functions, according to the Benchmarking report. As one of the CFOs surveyed said, “For anything that is innovative or creative or requires that you bring some gray matter to the table, the spreadsheet cannot be beaten."Here are 13 tips and features to bring more agility, functionality, and usability to your Excel spreadsheets. Share this with your team as you work on upskilling your employees and temporary workers.1. Fill cells quicklyExcel now has the power to finish your tedious info-typing session. Just start typing in the column to the right of your data.Excel will then gather suggestions to fill the rest of your cells after typing a few examples. To accept Microsoft’s suggestions, press enter.2. Format spreadsheets fasterWhen working on a spreadsheet, there are ways to add special formatting to different cells, columns, and rows with less effort.After applying any formatting to the spreadsheet — such as making a cell yellow or adding borders to a group of cells — you can use the format painter function to copy that formatting to other cells. If you double-click on the format painter icon, you can lock the function and single-click on any parts of the spreadsheet that you want to format. When you’re done, hit escape to unlock your cursor.For circumstances when you need to format a large amount of data, Excel offers time-saving shortcuts for many common formatting functions. Experiment with these handy ones:Format numbers to include two decimal places: Ctrl+Shift+1Format as time: Ctrl+Shift+2Format as a date: Ctrl+Shift+3Format as currency: Ctrl+Shift+4Format as a percentage: Ctrl+Shift+5Format in scientific/exponential form: Ctrl+Shift+63. Use Sparklines to display dataSparklines are a built-in feature of Excel that allows you to display small charts inside individual cells. These can be line charts, bar charts or simple win/loss charts. To create a Sparkline chart, select the range of numbers you’d like to include, click the “Insert” menu, then choose one of the chart options. Select a location range, which must be located along a single row or column in the same worksheet as your data range. Sparklines can help you easily display trends in your data in a compact format.4. Splice data easilySlicing allows you to filter data easily.To do so, select any range in a table or PivotTable, (refer to tip #5 for more information about PivotTables), and then go to Insert > “Slicer,” in the top right corner. Then, select the column you want to filter by.5. Manipulate data with pivot tablesWhen you have a large, detailed data set, pivot tables allow you to easily manipulate your data. These tables are interactive and can help you analyze data, detect patterns, and make comparisons. Creating a pivot table is as easy as using the built-in PivotTable and PivotChart Wizard, located in the “Data” drop-down menu. The wizard helps you choose the data to include in your PivotChart and format that information in a meaningful manner.6. Go formula-free Getting a total without an equation might just be one of Excel's most valuable features.To get your total formula, click anywhere inside the Excel table. Then press Ctrl+Shift+T, and Excel will add the total to your total row that you created.7. Move between formulas and resultsTo efficiently switch between the cell data and formula, use the Ctrl+tilde (~) keystroke. This allows you to rapidly check formulas when working in a large spreadsheet.8. Use the status bar without a formulaThe status bar shows counts, sums, and averages, without typing up any formulas.Just select the cells in your table and look to the bottom right of the Excel window. You’ll see instant stats about the cells you selected.9. Hide zero valuesHiding zero values can be helpful within large data sets by allowing you to see data more clearly. To hide zero values, you simply need to change the options in your Excel setup. Navigate to this function by clicking the “File” drop-down menu, and choose “Options.” Then choose “Advanced” from the left-hand menu and uncheck the box for “Show a zero in cells that have zero value.” (Mac users: Go to the “Excel” drop-down menu and choose “Preferences,” then uncheck “Show zero values.”)10. Use Excel templatesTemplates are available for any version of Excel. If you want to create an amortization table using a template, right-click on a blank worksheet and gain access through the “Insert” command. Under the “Spreadsheet Solutions” tab, you will find various templates, including an amortization schedule, which can then be inserted into the spreadsheet. You can also create your own templates by saving a worksheet as an Excel template.11. Chart your data quicklySelect any cells in your data range/ table and then press ALT+F1 on your keyboard. Now you have an instant chart.12. Save time looking for worksheetsIf you access the “File” menu or “Office Button” in Excel — depending on the version you are using — there’s a handy button called “Recent.” Using this function, you can quickly generate a list of the workbooks you recently worked on.In the “Advanced” section of the “Options” menu, you can expand your view and adjust the number of recent documents shown. You can change the number to 50 for better access to recent folders and files.13. Create tables quicklyYou can turn data cells into a table quickly!All you have to do is click within the parameters of your data and press Ctrl+T. Now you have an instant table displaying your data.Now that you have some helpful features to reboot your Excel skills and those of the accounting professionals you manage, you will get more done, and before you know it, you’ll be expanding your team!
  • Read More
11
Jun

Hiding and Unhiding Multiple Sheets without using Macros
  • David Ringstrom, CPA
  • 2249
One of the first “tricks” an Excel user learns is to hide and unhide a sheet. This is an exceptionally useful feature as it allows us to store data in a sheet, such as lists and tables, but keep the user of the workbook from seeing, manipulating, and more importantly, corrupting the information on the hidden sheet. As with most things in Excel, there is more than one way to hide a sheet or multiple sheets.  One of the easiest methods is to select a sheet (or select multiple sheets using standard Windows CTRL and Shift selection techniques), right-click the sheet tab then select “Hide”. You can hide a worksheet and the user would not see it when he/she opens the workbook. However, they can easily unhide the worksheet if they want (as we will see later in this tutorial). But what if you don’t want them to be able to unhide the worksheet(s).Let’s see how to hide a worksheet in Excel so that it can easily be unhidden, or can not be unhidden.
  • Read More
19
Apr

Maximize QuickBooks: Excel Analysis
  • David Ringstrom, CPA
  • 438
When it’s time to crunch numbers in QuickBooks, most users rely on reports within the software, while others turn to analytics programs. In either case, you may still sometimes need to analyze your accounting data in new ways. Pivot tables can make quick work of figuring out patterns or issues with your data that might be hard to discover otherwise. Pivot tables allow you to create instant reports and/or charts by dragging and dropping fields with your mouse.This means you can quickly rearrange even enormous reports into interactive, yet compact summaries.It’s almost a given that small to mid-sized businesses (SMBs) use QuickBooks to handle their accounting. The combination of easy-to-use and power is hard to beat.For some businesses, the built-in reports are all the information they ever need. For others, additional analysis is needed to do things like aggregate based on item type, location, address, or customer. One of our early users wanted to see the total amount of each product type they shipped to each state. Nothing crazy, but not something that QuickBooks just gives you.Doing this sort of analysis in Excel is certainly possible. Generally, it involves some copying and pasting followed by a pivot table. To see a great how-to for doing it with Excel, check out this blog by David Ringstrom:Attend this on-demand webcast "
  • Read More

Connect with Us

Copyright © 2026 Excel Accountant. All Rights Reserved.