Article Listing | Excel Accountant

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

02

Aug

Maximizing Screen Space In Excel

Nicholas Branson, CPA
765

It is remarkable how many repetitive actions we carry out in Excel spreadsheets, such as scrolling a worksheet from side-to-side when a couple of columns are off to the side of your screen. Commands on Excel’s View menu enable you to force an area of a worksheet to exactly fit onto your screen. A common scenario is a worksheet with twelve months of data, along with a total column. You might be able to see all the columns at once, or let’s say on a laptop screen you might see the data through November, and then have to scroll to the right to see December and the annual total. One screen-sizing approach involves using the Zoom Slider at the lower right-hand corner of the application window, as shown in Figure 1. I tend to turn this off because I find that I bump this control accidently when aiming for the horizontal scroll bar, sometimes resulting in microscopic print on-screen. To do so, right-click on the feature and then turn it off by way of the Customize Status Bar menu that appears. You can also use Zoom to Selection to make text larger. If you select a range of cells that is smaller than what you can see on screen, Excel will zoom in to make the selection fill your screen. Ostensibly, the Zoom Slider is a valid approach for fitting more information on the screen, but it is easy for this feature to run amok. Instead, the Zoom to Selection command will enable you to maintain better control: Select the cells that you wish to see onscreen, such as column A through N. Activate Excel’s View menu. Click Zoom to Selection. As shown, Excel will shrink everything to exactly fit onscreen. Click the 100% command on Excel’s View menu if you bump the Zoom Slider or wish to reverse the effects of Zoom to Selection. Figure 1: Excel’s Zoom to Selection command enables you to squeeze more information onto your screen.

Read More

28

Jul

Exclusive Excel Shortcut Hacks

David Ringstrom
143

MS Excel is undoubtedly the most popular software used for data analysis and handling. It enables professionals from different backgrounds to effortlessly manage data, big and small. Despite its popularity, however, there's a hidden treasure trove of functions and shortcuts that a large number of users are unaware of. These tips can make it easier to handle work and improve productivity several times over. Here are some simple yet effective functions in Excel: 1. Paste Special People often need to copy the content of one cell and paste it into another. But the data of a cell may have other attributes, such as formatting, value, formula, comments, and many more. To copy only certain characteristics, one can use the 'paste special feature. To use this feature, you need to go to the "Home" tab in the toolbar on the top left. You will find it under Clipboard. Right-click or press Ctrl+Alt+V. The Paste Special box offers various options like Paste Values, Paste Values, and Number Formats & Transpose. Paste Values: If you want the value of a cell and not the entire formula, go for Ctrl-Alt-V-V and hit enter. Do bear in mind, the default action of Excel is to copy a formula.  Paste Values and Number Formats: This transfers the format of the original cell. This feature is beneficial when you want to copy over dates or a currency and not the original formula of the cell. The shortcut for this is Ctrl+Alt+V+U & hit enter. Transpose: This enables you to flip rows and columns around in seconds. You can easily turn a row of numbers vertically or vice-versa by simply copying and then using Paste Special – Transpose. The shortcut for Transpose is Ctrl-Alt-V-E & hit enter. 2. Go To Special This feature allows you to locate and select cells of the same characteristics. You can select cells that have characteristics like comments, formulas and numbers. It will help you to identify errors or highlight content. It is in the Editing section under 'Find & Select.' The shortcut for this is Ctrl+G followed by Alt+S. For Instance, you have a column of cells with some blank cells which you wish to get rid of. First, select that column, then select Go To Special, then Blanks, and you'll see that all the blank cells are highlighted. Now, right-click on one and select Delete. All blank cells would be gone. The shortcut in excel for Go To Special, Blanks is Ctrl-G followed by Alt-S-K and hit the enter button. 3. Flash Fill Function Excel came up with a great feature in 2013. If you have two columns of names (first and last) and you require it to put both columns together. Type manually for the first row, and you will find the rest of it appear on its own. This makes dealing with names a lot easier and quicker. 4. Use of Apostrophe The apostrophe (') tells Excel to treat the data following it as it is and not to pick it up as a formula. If the first character in the cell is +, - and =. Excel treats the cell differently and starts looking for cells to refer to. This can be disorienting. That's where our next Excel hidden gem comes in. The apostrophe (') is Excel's solution to displaying data that Excel would otherwise treat differently. Apostrophe ensures data is displayed just the way it is entered. 5. F2 If you use Excel a lot, you will understand the frustration of looking for your cursor. To give reprieve to us all, Excel provides a nifty hack just to hit F2. This activates the cell you are working in. It will bring you directly to that cell.

Read More

16

Jul

Speed Up Your Work With These Excel Formulas

David Ringstrom
223

Excel has been the most trusted software of accountants ever since it was released in 1987. Despite competition from Google Sheets, it continues to enjoy the title of ‘world’s most popular productivity tool’. Even though it is widely used, there are a lot of its functions that are not fully explored. To use it more efficiently as a tool for budgeting, planning and forecasting operations, you can master some formulas that will save you time and effort. Let’s take a look at this using two functions: IF and VLOOKUP. Conditional IF Formula "IF" (=IF) function of MS Excel allows you to test whether a condition is true or not, and returns different results accordingly. The syntax of this handy little function is as follows: =IF (condition to test, what to do if it’s true, what to do if it’s false) For example, suppose you are an accountant trying to work the US income tax for 2018 for a series of (unmarried) clients. Finding out the tax each client would have to pay becomes quicker and easier with this tool. Here, let’s assume people get taxed only for an income above $9,525 at 12%. You can use this to calculate each client’s income tax liability: “=IF(B2>=9525,B2*12%,0)”. For example, if the income in cell B2 is greater than or equal to $9,525, charge income tax at 12 percent of a person’s income; otherwise, charge $0.But as any professional dealing with tax and accounting would know, things are rarely this straightforward. When there’s more than one possibility, nesting IF functions is quite handy. Suppose now that the income tax regime is a bit more complicated: incomes above $9,525 attract tax at 12 percent, but properties above $38,700 attract tax at a higher rate of 22 percent. You could cope with this by nesting one IF function within another:The formula in cell C2, which has been copied down to the other cells, is: =IF(B2>=38700,22%,IF(B2>=9525,12%,0%)) * B2 What this means:If someone’s income is more than $38,700, take the income tax rate as 22 percent;Otherwise, if the house price is more than $9,525 (but less than $38,700, since we’ve already eliminated this), take the income tax rate as 12%;Otherwise, take the income tax rate as 0 percent.For a nested IF function like this, you have to take the values in order: either start with the highest value and work your way down to the lowest, or work from the lowest value to the highest. As you can see, it will become more complicated as you add more values and conditions. Thus, it is recommended to go for VLOOKUP formula. VLOOKUP Formula Suppose now that the income tax regime is more complicated and is charged as follows  That is: Incomes up to $9,525 are exempt from income tax. Incomes between $9,525 and $38,700 are charged tax at 12 percent. Nested IF Function can solve this but it would become complicated if the thresholds or tax rate values are changed. Instead, using the VLOOKUP function will make things smoother in this context. Let’s see how: =VLOOKUP(The value you’re looking up, the table you’re looking it up against, the column number you’re returning) Using this formula, you could calculate the income tax payable on each of your possible purchases as follows: =VLOOKUP(B11,$A$2:$B$8,2)*B11 This looks up the income of the client ($45,000, as held in cell B11) against the lookup table held in A2:B8, and returns the value of the second column in this table (in this instance, 22 percent), which it then multiplies by the client’s income. There are a few important aspects to notice: It’s vital that the income thresholds we’re looking up against are in the first column of the lookup table and are in ascending order. You must make the reference to the lookup table absolute (that’s what the $ signs are for), to make sure that when you copy the formula down it always refers to the fixed table A2:B8.

Read More

08

Jul

Most-Used Excel Formulas For Financial Auditors Or Accountant

Fedrica Hanks, Accountant
1241

Microsoft Excel is one of the foremost applications for an accountant or financial auditor. Becoming pro in excel for accountants is not an easy task. It takes time and effort. If you are an accountant, you must be familiar with the useful Excel formulas and functions to make the process easy. Here, we are going to list some of the important useful, and most used Excel formulas for financial auditors and accountants. Consider one single cell here as an example. 1. To double the width of all columns Ctrl+ ` 2. Displaying and highlighting formulas – 1) Highlight the cell that have formulas. Click on single cell >> go to Home tab >> find and select >> formulas. 2) Highlight the cell that have no formulas. Click on any single cell >> go to Home tab >> find and select >> go to special >> check the box for ‘Constant’ and ‘Numbers’ and uncheck the rest. 3. Auditing Tool 1) Find all dependencies of cell. Jump to Formulas Tab >> Trace dependents To find the dependent cell, just keep clicking repeatedly on trace dependents. 2) Highlight dependent cell Without displaying arrows -- Ctrl+shift+) 4. Entire row and column references – 1) Add data from two different rows - (use Alt= or =sum) >> =sum(select rows) 2) Add data from two different column - (use Alt= or =sum) >> =sum(select the column) 3) Calculate average of data of entire column (use Alt= or =sum) >> =average(select the column) 4) Calculate median of the data of entire column (use Alt= or =sum) >> =median(select the column) 5) Calculate years till date from the given date =datedif(A1,today(),”Y”) A1 is the column number 6) Update cell values without formulas – 1) Increment or decrement the value from all rows or column without using formulas. If you want to increase the cell values by 4 Type 4 in any blank cell >> now select all the rows and columns where you want to add 4 >> go to “paste special” or Ctrl+Alt+V >> Check “add” >> ok. If you want to increase value by 10% Type multiplier of percentage value (Here 10% i.e 1.1) in any cell >> Copy that value >> now select all the rows and columns where it needs to modify >> go to “paste special” or Ctrl+Alt+V >> Check multiply >> ok You can use the same process of “Paste Special” for subtracting or dividing values Date & Time YEARFRAC() Purpose of this formula to get fraction of a year between two dates. It returns a decimal number. DATEDIF() This function will return the number of days, weeks, or years between two dates. DAYS360() This function is used in accounting calculations to get the number of days between two dates based on a 360 – day year (i.e 12 - 30 days- months). DAY(), MONTH(), YEAR() This function is used to returns the integer day, month, or year of a given date. Present Value & Rate of Return XIRR() This is a financial function that returns the internal rate of return (IRR) that doesn’t have regular period cash flows. This function will help us to understand the rate of return earned on an investment. XNPV() It is also a financial function that is used to calculate the net present value (NPV) of an investment by using a discount rate and a series of cash flow that occur at irregular intervals. IPMT It returns the interest amount of loan payment in a given period, assuming the interest rate and the total amount of payment are constant in all periods. IRR() Internal Rate of Return (IRR) function is used to calculate the investment’s rate of return. ISPMT This function is used to calculate the interest paid during a specific period of investment ACCRINT This function is used to calculate the accrued interest for a security that pays interest on a periodic basis. ACCRINTM This function is used to calculate the accrued interest for a security that pays interest on maturity. CUMIPMT This function is used to calculate accumulative interest paid on a loan between any two payment periods. CUMPRINC This function is used to calculate accumulative principal amount paid on a loan between any two payment periods. DB This function will return the depreciation of an asset for a given time period based on the fixed declining method. DISC This function is used to get the discount rate for securities. It helps you to know the discount rate when you know the other detail of bond. DURATION The function calculates the duration of security (Macaulay Duration) that pays interest on a periodic basis with a par value of $100. EFFECT This function will calculate the annual interest rate with the number of compounding periods per year from nominal annual interest rate. FV This function is used to calculate the future value of an investment assuming periodic, constant payments with a constant interest rate. FVSCHEDULE This function will calculate the future value of an investment assuming a variable interest rate. INTRATE This function is used to calculate the interest rate for a fully invested security. MDURATION It returns the Macaulay modified duration for a security with par value of $100. MIRR It calculates the modified internal rate of return for a series of cash flows that occur at regular intervals. NOMINAL It will return the nominal annual interest rate with the given effective rate and number of compounding years. PMT() This function is used to calculate the total payment for a loan (principal + interest ) which is required to settle a loan or an investment with a constant interest rate over a specific time period. NPER() This function is used to get the payment periods for a loan based on amount, interest rate, and periodic payment amount. NPV This function is used to calculate the net present value (NPV) of an investment using a discount rate and a series of future cash flows. PDURATION It calculates the number of periods required for an investment to reach a specified future value. PPMT It is used to calculate the principal amount of investment or loan of any given period. PRICE It is used to calculate the price of security /bond per $100 face value that pays periodic interest. PRICEDISC It is used to calculate the price of discounted security /bond per $100 face value. PRICEMAT It is used to calculate the price of security /bond that pays interest at maturity per $100 face value. PV This function is used to calculate the present value of an investment or loan. RATE It is used to calculate the periodic interest rate i.e per period of annuity. RECEIVED It is used to get the amount received at maturity for a fully invested security. SLN It is used to calculate depreciation of an asset for one period based on initial asset cast, salvage value, and the number of periods over which the asset is depreciated. SYD It is used to calculate the depreciation of an asset, specifically the sum-of-years’ digits depreciation for a specified period in the lifetime of an asset. TBILLPRICE It is used to calculate the fair market value of Treasury bill/ bond per $100 face value. TBILLEQ It is used to calculate the bond-equivalent yield for a Treasury Bill. TBILLYIELD It is used to calculate the yield on a Treasury Bill. YIELD It is used to calculate the yield on a security that pays periodic interest that is bond yield. YIELDDISC It is used to calculate the annual yield for a discounted security like Treasury bill. YIELDMAT It is used to calculate the annual yield of a security that pays interest at maturity Lookup vlookup/index/match To perform more advanced data lookups, these functions are most important. With the use of these functions, you can do horizontal and vertical lookups, left lookups, 2-way lookups, and lookups based on multiple criteria. sumifs/countifs Based on single criteria with the use of these two functions, you can sum or count cells conditionally. If This function will allow you to make logical comparison and returns one value if true and another value if false. Other - Pivot tables It is used to recognize, sort, summarise, group, count, total or average data store in a more extensive table.

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

Copyright © 2024 Excel Accountant. All Rights Reserved.