The 10 most useful Excel keyboard shortcuts

The problem with a list of hundreds of shortcut keys is that it is overwhelming. You cannot possibly absorb 233 new shortcut keys and start using them. The following sections cover some of my favorite shortcut keys. Try to incorporate one new shortcut key every week into your Excel routine.

1) Quickly move between worksheets

Ctrl+Page Down jumps to the next worksheet. Ctrl+Page Up jumps to the previous worksheet. Say that your workbook has 12 worksheets named Jan, Feb, Mar, . . . Dec. If you are currently on the Jan worksheet, hold down Ctrl and press Page Down five times to move to Jun.

2) Jump to the bottom of data with Ctrl+Arrow

Provided there are no blank cells in your data, press Ctrl+Down Arrow to move to the last row in the data set. Use Ctrl+Up Arrow to move to the first row in the data set.

Add the Shift key to select from the current cell to the bottom. If you have data in A2:J987654 and your cursor is in A2, you can hold down Ctrl+Shift while pressing the down arrow and then the right arrow to select all the data rows but exclude the headings in row 1.

3) Select the current region with Ctrl+*

Press Ctrl+* to select the current range. The current range is the whole dataset, in all directions from the current cell until Excel hits the edge of the worksheet or a completely blank row and column. On a desktop computer, pressing Ctrl and the asterisk on the numeric keypad does the trick.

4) Jump to the next corner of a selection

You’ve just selected A2:J987654 but you are staring at the bottom-right corner of your data. Press Ctrl+Period to move to the next corner of your data. Because you are at the bottom-right corner, it takes two presses of Ctrl+Period to move to the top-left corner. Although this moves the active cell, it does not undo your selection. Although I always use Ctrl+Period twice, I should probably learn Ctrl+Backspace to bring the active cell back into view. That will be my new trick for next week.

5) Pop open the right-click menu using Shift+F10

When I do my seminars, people always ask why I don’t use the right-click menus. I don’t use them because my hand is not on the mouse! Pressing Shift+F10 opens the right-click menu. Use the up/down arrow keys to move to various menu choices and the right arrow key to open a fly-out menu. When you get to the item you want, press Enter to select it.

6) Cross tasks off your list with Ctrl+5

I love to make lists, and I love to cross stuff off my list. It makes me feel like I’ve gotten stuff done. Select a cell and press Ctrl+5 to apply strikethrough to the cell.

7) Date-stamp or time-stamp using Ctrl+; or Ctrl+:

Here is an easy way to remember this shortcut. What time is it right now? It is 11:21 here. There is a colon in the time. Press Ctrl+Colon to enter the current time in the active cell.

Need the current date? Same keystroke, minus the Shift key. Pressing Ctrl+Semicolon enters the current time.

Note that this is not the same as using =NOW() or =TODAY(). Those functions change over time. These shortcuts mark the time or date that you pressed the key and the value does not change.

8) Repeat the last task with F4

Say that you just selected a cell and did Home, Delete, Delete Cells, Delete Entire Row, OK. You need to delete 24 more rows in various spots throughout your data set.

Select a cell in the next row to delete and press F4, which repeats the last command but on the currently selected cell.

Select a cell in the next row to delete and press F4. Before you know it, all the 24 rows are deleted without you having to click on Home, Delete, Delete Cells, Delete Entire Row, OK 24 times.

The F4 key works with 92% of the commands you will use. Try it. You’ll love it. It’ll be obvious when you try to use one of the unusual commands that cannot be redone with F4.

9) Add dollar signs to a reference with F4

That’s right — two of my favorites in a row use F4. When you are entering a formula and you need to change A1 to $A$1, click F4 while the insertion point is touching A1. You can press F4 again to freeze only the row with A$1. Press F4 again to freeze the column with $A1. Press again to toggle back to A1.

10) Find the one thing that takes you too much time

The shortcuts in this article are the ones I learned over the course of 20 years. They were all for tasks that I had to do repeatedly. In your job, watch for any tasks you are doing over and over, especially things that take several mouse clicks. When you identify one, try to find a shortcut key that will save you time.

When you perform commands with the mouse, do all the steps except the last one. Hover over the command until the tooltip appears. Many times, the tooltip tells you of the keyboard shortcut.

Keyboard Shortcuts to Insert Special Symbols in Excel

The copyright ©, trademark ™, and registered trademark ®, symbols can be inserted quickly in Office applications using the Autocorrect feature. Typing (c), ™, or (r) will accomplish this. The other way to enter special symbols in Excel is by going to Insert > Symbol.

You can actually create your own keyboard shortcuts for inserting other special symbols in Excel such as the micro symbol (µ), delta symbol (∆), degree sign (°), plus-minus sign (±), common superscripts such as ² or ³, common fractions such as ¼, ½, ¾, dot sign (·), partial differential (∂), infinity (∞), etc.

To do this in Excel, Click on the File menu and then select Options from the drop down menu.

When the Excel Options window appears, click on the Proofing option on the left. Then click on the AutoCorrect Options button.

Replace:   With:

• (micro)      µ
• (delta)      ∆
• (deg)        °
• (degree)     °
• (+/-)        ±
• (^2)         ²
• (^3)         ³
• (1/4)        ¼
• (1/2)        ½
• (3/4)        ¾
• (dot)        ·
• (part)       ∂
• (partial)    ∂
• (inf)        ∞
• (infinity)   ∞

Note that not all of the unicode characters will be displayed correctly on other computers.

3 Things to remember before you go for Excel VBA Job Interview

Like giving interview is a skill, taking interview is another great skill. I’ve taken many in the past and given more than I’ve taken and here I’m sharing the content from my experience. Your interview experience may be totally different than this but you must be aware of it and should not miss some common things mentioned here. These are my personal views and have nothing to do any of my past or present employers.

If I will conduct an interview, I’ll not probe your technical knowledge only but also how much logical and reasoning thinking you possess along with some programming etiquette.

First interviewer sees your general attitude towards programming and will try to calm down your blood pressure. While it seems very easy this is the biggest hurdle. He may give you a very easy program to write like sorting an array, coloring the sheet tabs, etc. The objective is not to see your technical knowledge but your programming behavior. Such programs are so obvious and easy that expectations are not about writing the program but to see comments, indentation, using meaningful variable names, declaring variables(which one don’t needs to but considered a great practice), using Option Explicit, Debugging style etc. To know that good you’re able to communicate your logic with other developers. This is very helpful for the team and is considered a good practice with fruits returned in long term.

 Secondly, he will try to test your knowledge of Excel Object Model. They may ask you to sort and filter a range, or clean out duplicate data from a list, consolidate data from multiple files, import data from MS access databases etc . He will look for knowledge of and ability to work with fundamental Excel objects such as Workbook, Worksheet, Range etc; also he will definitely want to see that youneither use “Macro Recorder” nor macro recorder style code using Select and Activate everywhere.

Thirdly he would be interested in your excel knowledge as well as how VBA works. Like pivots, charts, UDF, volatile functions(Can you write code that runs every time the worksheet changes?”) etc. 

 Lastly, the questions would be job specific that if its a consulting firm questions will revolve around statistical models, SAS etc, in case of bank – financial dashboards,KPI etc. and in case of capacity management or databases they’ll revolve around ADO, Basic SQL etc.

Good interviewers put “Knowledge of Excel” above “Knowledge of Excel VBA” as in their opinion, the latter is more straightforward and easier to learn but it’s harder to find people who are good at writing efficient Excel formulas(esp. Array Formulas) as often formulas can yield a far more efficient solution – faster by several orders of magnitude.

Excel VBA is a very vast field but interview questions revolve around common things. Interviewer may ask some stupid or Out-of-the-box question to worry you a little, may give you too less time to write a macro or do anything to worry about. YOU DON’T NEED TO WORRY, it’s ok if you don’t know answers to some questions. Maintain NEVER GIVE UP attitude and YOU’LL SUCCEED.

5 Simple but Important Fomulas In Excel

Here are some easy formula that you might use in excel

1) Anyone knows how to Combine and center the contents of the selected cells in a new larger cell. ??

Simple Answer : Click   Alt + H + M + C 

and see the Magic :) 

Merge & Center Text

2 ) How to Fit data inside cell.

Answer : Alt +  O +  C +  A

3) Attach a copy of current workbook to default email as a attachment.

Simple Answer : Click  Alt + F + D + E or Alt + F + D + E

4 ) How to switch on the Filter ??? 
 
Simple Answer : Click  Alt + T

 

5) How to Change the Orientaion in Excel??? 

Simple Answer : Click  Alt + P  + O

Download Office 2016 IT Pro and Developer Preview version

IT professionals and developers can now get their hands on Office 2016. Microsoft has launched the IT Pro and Developer Preview of the next version of Office.

Now, Office 365 commercial customers will be able to download the IT Pro and Developer Preview of Office 2016, and with it a range of new features.

The complete version of Office 2016 is expected to be released by this year end.

download the IT Pro and Developer Preview of Office 2016

   

Download Office 2016 IT Pro and Developer Preview version from here

The Best Shortcut Keys in Microsoft Excel 2010 - 2013

Tab	Moves one cell to the right in a worksheet
SHIFT+TAB	Moves to the previous cell in a worksheet.
CTRL+TAB	Switches to the next tab in dialog box.
CTRL+SHIFT+TAB	Switches to the previous tab in a dialog box.
CTRL+1	Displays the Format Cells dialog box.
CTRL+SHIFT+L	Use for filtering a column. E.g. If you have to set a couple of filters, and you already have filters placed in your data, instead of clearing each filter which is time-consuming, just turn-off and turn-on the filters, by pressing CTRL+SHIFT+L twice and your work is done.
CTRL+Arrow keys	To select a group of cells. Eg. it is difficult when you have to deal with large data and use the mouse to navigate the database.  So to make your work easier, just rely on CTRL + arrow key combinations all the time to select a group of cells.
ALT+ES – Paste Special > Values	Pasting the value.E.g. When we apply any formula and want only the values to be shown in the cells, we need to use copy and then paste special values. If we need to get data from other workbooks or need to format  existing tables, we can use ALT+ES (press E then leave the key and press S) to paste special value.
F4	Repeat the last command or action. E.g. If the color of text in a particular cell is changed, you can repeat the command by just pressing F4. This key can be used to repeat any action that was last performed provided the file is still open.
F2	Edit a selected cell. You can use F2 to edit a cell or formula in a cell.
Ctrl + F3	Add name for any range. E.g. If you are making a dashboard or Excel model, just press Shift + F3, no matter where you are (formula editing, conditional formatting, data validation etc.) and you get a list of all the defined names. Pick one and press ok to add it to the formula you are typing.
CTRL+K	Add Hyperlink. E.g. If you have more than a one sheet, Hyperlinks make your work book accessible and save your time. You can directly link the pages to one another using so that the sheets are accessible on a single click of the hyperlink.
CTRL+T –	Insert Table. E.g. If you have selected any cell in a range of related data just press CTRL+T to make it a table.
CTRL+S –	Save the sheet.
Ctrl + R	Fills the range to the right of the data
Ctrl + D	Fills range down word
Ctrl+W	Close a particular work book
F9	Refresh the sheets’ formula
Ctrl+PageDown/PageUp	Navigate sheets within the workbook
Ctrl+shift+1	Applies the Number format for two decimal places, thousands separator, and minus sign (-) for negative values.
Alt F11	Opens VBA editor
Ctrl F6	Switches to the next workbook window.
Ctrl ~	Change the cell value as general
Ctrl + spacebar	Select a particular column
Shift + spacebar	Select a particular row
Ctrl + Shift + spacebar	Select entire sheet
ALT + =	Auto sum for a column. E.g. When you have entered some data in a column and at the end of the column you need the summation of the column you can simply use ALT + = to save your time.
Ctrl + B	Applies and removes bold format.
F5	A Go-to window will open which allows you to go to a particular cell based on the reference provided. Applies the border of the cell.
Ctrl + Shift + &

To select cells from any cell to the last cell in the sheet using range in Excel + 2010/2013

In Microsoft Excel, there are times when we require selecting a lot of data at once. To select this data, we have to follow the simple tips given below:

We need to select all the excel cell data value below in cell A7

• Place your mouse at A7 and click to activate it

• Now press and hold Shift + Ctrl and it will select the entire cells below A7 which has a value in it.

Now for selecting all of the excel cells which have value above the Cell A7

• Place your mouse at A7 and click to activate it
• Press and hold Shift + Ctrl and home key at once

• It will only select cells A7 to A1, after this, if we wish to select the cell ranges to the right of A7, then, we need to click at the end of the cell (which has value) of row 7. To achieve this, we need to Press and hold Shift + Ctrl and Home key at once.

• Do not select the header and just click on the excel range horizontally to select the data as required.

If you want to extend the selection below row 7, then you can press the down arrow key along with Ctrl + Shift.

E.g. It is difficult when you have to dealwith large data and use the mouse tonavigate the  database. So to make your work easier just rely on CTRL + arrow key combinations all the time to select a group of cells.

Ctrl  +  Shift  +  &

7 Simple Tricks to Make Smart and Neat Excel Graphs

It’s really tempting to let terrible Excel graphs creep into your reports. Your boss doesn’t care about little things like how graphs look, right? And whatever Excel comes up with as the default is probably fine … right?

Not really. You’re using data to spur action. Maybe you pull data to convince your boss to take certain decissions, give you an extra sliver of budget, or adjust your team’s strategy, among other things. Regardless of what you use data for, you need it to be convincing — and if you display data poorly, the meaning of your data is more likely to get lost.

To make sure you’re making your data as convincing as possible, you should always customize your graphs in Excel. And by customization, we’re not talking about big sweeping changes. Below are some quick tweaks you can make to your graphs convincing, easy-to-read, and beautiful.

Note: I’m using Excel 2010 on Windows. If you’re using another version or operating system, implementing the following tips should be identical.

1) Pick the right graph.

Before you start tweaking design elements, you need to know that your data is displayed in the optimal format. Bar, pie, and line charts all tell different stories about your data — you need to choose the best one to tell the story you want.

Bar graphs and pie graphs help you compare categories. Pie graphs usually compare parts of a whole and bar graphs can compare pretty much anything … which means often you should just use a bar graph. Bar graphs are easier to read and notice incremental differences between categories, so it’s a good go-to. Pie graphs are best used when one of the categories is way larger than the other.

Want to see the difference? Here’s an example of the same data set displayed as a pie graph and a bar graph:

Line charts help you display a changing trend over time. You can track multiple values over that time, but the key to a line chart is the time component.

To turn your hard data into one of these charts in Excel, highlight the data you want to morph into a chart, then select ‘Insert’ > ‘Charts’ (or choose ‘Charts’ in the top navigation if you have a Mac Version). Then choose the chart most appropriate for your data!

2) Sort bar graph data before designing.

If you’re using a bar graph to display your data, this tip can make a big difference. Most bar graphs look like this:

They’re kinda random. You spend just a fraction of a second too long figuring out which ones are outliers. Instead, you should reorder your data points to go from largest to smallest. Here’s what that looks like:

If your bar graph is horizontal, larger values should be at the top. If your bar graph is vertical, order them from left to right. Why? That’s how people read English (if you’re presenting this data in another language where that isn’t true, change up your order to better reflect reading patterns).

To order the graphs in Excel, you’ll need to sort the data from largest to smallest. Click ‘Data,’ choose ‘Sort,’ and select how you’d like to sort everything.

3) Remove background lines.

Graphs allow you to roughly compare data within a set, not dig into it. No one’s looking at your graph to see incremental differences between data points — they want to see general, overarching trends.

To help people focus on those trends, remove the lines in the background of your graph or chart. These lines are superfluous, unhelpful, and distracting — cut them from your graph to help people focus on the big takeaways.

To remove background lines, choose ‘Chart Layout’ then ‘Gridlines,’ then choose ‘None’ under ‘Primary Horizontal Gridlines’ and ‘Vertical’ options.

4) Remove unnecessary styling.

Most standard Excel graphs come pre-styled … and these styles will often get in the way of communicating information. Shadows? Outlines? Rotations? Get rid of them all. They don’t add to the data’s story.

To fix the styling in Excel, right click on the graph and choose ‘Format Chart Area.’ Remove all extra and unnecessary effects on your data:

5) Stay away from 3D effects.

This falls under the previous bullet point, but I wanted to include it as its own point because it’s one of the most overused data visualization effects.

To make data look extra fancy, people will often make bar, line, and pie graphs 3D — but it actually just makes the data harder to read. Because of the way the data is tilted, it gives the reader a skewed sense of what the data actually means. Since you’re using data to tell a broad story, you don’t want to weaken your argument due to poor design. See how different a pie chart looks when it’s in 2D versus 3D?

And if you actually look at the area each section takes up on the screen, you’ll see why it’s easy to misinterpret 3D graphs:

To remove the 3D styling from your graphs, double click on the bars, lines, or pie sections you’d like to change, choose ‘3D Format’ and set ‘Top’ and ‘Bottom’ to ‘None.’

6) Use legends strategically.

If you’re only comparing a few data points, legends can be pretty useless — they tell you information you can read easily on the graph. When you have a ton of X-axis categories or multiple data points per category, legends make sense. Until you have data like that … just delete the legend altogether.

To do this in Excel, you can just double click on the legend text box, then hit ‘Delete’ on your keyboard:

7) Include branded colors.

The colors that come preset in Excel are pretty drab — one quick way to spruce them up is to make them your brand’s colors. It’s a little detail that’ll make your charts look slick and clean.

To make sure you’re always using the proper brand colors, you’ll need to grab the HEX color code of your brand’s colors. Then, plop that code into this HEX –> RBG color converter. In Excel, double click on the parts of your graph you’d like to change the color of. Under ‘Fill’, Select ‘Solid Fill’, choose ‘Color’, select ‘More Colors’ and then ‘Select Custom’ tab.

Then input the RGB numbers you found in the beginning. Then, voila! Perfectly on-brand colors and a gorgeous graph.

What other little Excel graph tips do you have up your sleeve? Share them with us in the comments. 

VLOOKUP Explained!! See This Video Tutorial

VLOOKUP IN EXCEL EXPLAINED

VLOOKUP is a powerful Excel function that allows you to look for a specified value in one column of data inside a table, and then fetch a value from another column in the same row.

An example where VLOOKUP might be useful is if you have a monthly sales report in Excel, and want to find the sales made by a specific salesperson from within a monthly sales report. You would lookup the the person's name in the Salesperson column, and then look in the Sales column to find that person's sales for the month.

In this lesson you'll learn how to use VLOOKUP in your spreadsheets.

Hope you found this tutorial helpful, If you have any query then let us know :)

Must Known Excel Tips and Tricks for Basic to Intermediate Users!

Whether you’re an Excel power user or just reasonably experienced with spreadsheets, there are generally still things you can learn about Microsoft’s program, and points which can be refreshed in your memory. So, we’ve put together this compilation of 14 tips for those of you using Excel 2010, going over some important features – and making sure you’re making the most of them. Also, some of these tips cover essential Intermediate to Advanced-level skills that you should know no matter what version of Excel you use.

Open the Office window on two different views

Here’s a tip that applies to both Word and Excel 2010 in slightly different ways. By splitting the spreadsheet/document window into two separate panes you can view and edit two widely separated parts of a document at the same time. That means that, in Word for example, you can work on the first chapter of your novel in the top pane and the last chapter in the bottom pane, and jump between them simply by clicking the mouse – or by tapping F6 to cycle between the two panes, the ribbon, and the status bar. It’s the same deal in Excel.

Word even lets you change the zoom level in the two panes, as shown in the above image, so that you can view multiple pages in one pane while viewing full-size text in the other. You can split the window by dragging the splitter bar at the very top of the vertical scroll bar, or by opening the View tab on the Ribbon and selecting Split (or Remove Split to restore a single window). Excel has a similar split-window feature but unfortunately doesn’t let you choose different zoom levels in each pane.

---

Get more information from Excel’s status bar

It’s easy to ignore the wealth of data that Office apps provide at a glance on the status bar. By default, Excel’s status bar displays, among other things, the Average, Count, and Sum of the selected cells. But if you right click on the status bar, a menu lets you customise the display. You can add, for example, the Minimum and Maximum of the currently selected items, or the “Numerical Count” – which means “the count of cells with numeric data,” as opposed to “Count,” which means “the count of cells that contain anything.”

---

Use a named range in a formula in Excel

One major convenience of a named range is that you can use it in a formula instead of a set of cell addresses. Press F2 to open the formula bar, start writing or editing your formula, and place the cursor where you would normally use cell addresses. On the Formula tab in the Ruler, click on Use in Formula and either select one range name from the dropdown menu or select multiple names from the Paste Name dialog box shown here.

---

Convert an Excel formula result into static text

Here’s a simple tip that can save a lot of manual labour. I often want to keep the results of formulas or calculations, and I want to discard the original data that went into the results. For example, I often import columns of text data, then use the Excel’s Concatenate function to combine two columns of data (for example, first and last names) into a third column (full names), and then I want to delete the now-redundant material in my original two columns.

If I merely deleted the original two columns, the results in the new column would disappear with it, so I need to convert the new column into static text first. To do this for a single cell, select the column or cells that you want to convert to static text, press F2 to open the formula bar, press F9 to evaluate the formula, and the Enter key. To do this for multiple cells, select the cells you want to convert, press Ctrl-C to copy them to the clipboard, press Ctrl-V to paste, and then, from the dropdown Paste menu, select the first icon under Paste Values to paste the result of the formula in place of the formula itself.

---

Automatically fill a row or column with a custom list of names

Excel automatically fills a row with the names of months or weekdays when you type in the first few items in the row, then select the cells and drag the “fill handle” at the lower right corner of the selection to extend the series. If you have a series of words or names that you frequently enter by hand, create a custom autofill list. Click on File, then Options, and then Advanced. Scroll down almost to the foot of the Advanced menu. Under the General heading, click Edit Custom Lists…, and in the Customs List dialog, click New List and enter your list of words.

---

Friends don’t let friends enter bad data in a worksheet

One guaranteed way to mess up a worksheet is to enter the wrong kind of data in a cell – for example, text instead of numbers, or a decimal instead of a whole number. Prevent yourself – or anyone else who uses your worksheets – from entering the wrong type of data by using Excel’s Data Validation feature. In a table, select the cells that should only contain one kind of data. On the Table Tools tab, click Data Validation and specify the kind of data that can go into the cells. How do you alert the user who tries to enter the wrong data? Read on.

---

Tell your friends exactly what kind of data to enter

The same dialog in which you specify the kind of data that can be entered (see above) has a tab labelled Error Alert. Fill in the title and message that you want to pop up when the wrong kind of data gets entered. You can also select the icon for the message by selecting the Style dropdown.

---

Line up multiple sheets in the same worksheet for inspection

In the above image, note the box at the upper right. How did I get two sheets from the same worksheet to appear on the same screen? Simple. On the View tab, I clicked New Window, then View Side by Side. By default, the Synchronous Scrolling option is turned on, so that you can scroll through both pages by dragging the slider bar in one of them. But you can turn off Synchronous Scrolling if you prefer to scroll through each sheet separately.

---

Conditional formatting with negative numbers in Excel

In Excel 2010 you can apply one of the pre-built colour-coded conditional formatting options to data that includes negative numbers (not only positive numbers, as in Microsoft Office 2007). This can give you quick graphic clues to the way in which profits and losses, for example, fit into a pattern that’s easier to detect graphically than by looking at a column of numbers. You set this up this kind of conditional formatting by clicking Home, Conditional Formatting, Data Bars, and then choosing a colour set in the Gradient Fill gallery. The results are visible in the screen shot above in column P.

---

Display the actual cell values when creating or editing a formula

The previous tip shows how to display formulas in the entire spreadsheet. Here’s how to switch between displaying the cell addresses in a formula and the actual values in each cell. Use any method that displays a formula – for example, when the formula of the current cell is visible in the formula bar, or when you’re creating a formula for the first time, or after pressing Ctrl-tilde to display formulas throughout the worksheet. In the formula you want to find out about, select the cell addresses, and press F9. The highlighted addresses are replaced by the values of all the cells referenced in the formula. Press Esc to return to normal display. The screenshot above shows a formula that normally displays the address D12:O12, but when I selected that address and pressed F9, the actual values appeared.

---

Highlight all cells referenced by a formula

When you’re debugging a worksheet, you can easily navigate through all the cells referenced in a formula. Highlight the cell and press Ctrl-[ (that’s Ctrl-open-square-bracket). Excel highlights all the cells referenced by the formula, and moves the current selection to the first of the referenced cells. Press Enter, and the selection moves to the next referenced cell, and continue to press Enter to move though the rest of the referenced cells. In the screen shot, I was originally in cell D35 and pressed Ctrl-[. This highlighted D12, D26, and D35; and D12 became the current cell.

---

Highlight the formulas that reference the current cell

The previous tip explained how to use Ctrl-[ (Ctrl-open-square-bracket) to see all the cells referenced by a formula. What if you want to do the reverse, and see the formulas that reference a cell? Select the cell, and press Ctrl-] (Ctrl-close-square-bracket). As in the previous tip, the selection moves to the first formula that references the cell. Press Enter repeatedly to navigate to the other formulas that reference the cell. In the screen shot, I was originally in cell D3. I pressed Ctrl-]. This highlighted B3, D12, and D35, and B3 became the current cell.

---

Tidy up your charts

If you’ve ever created two or more charts on a worksheet, you know how tricky it can be to align them and make them all the same size. Here’s the easy way. Click on the first chart to select it, then hold down the Ctrl key and click on the other charts that you want to align with each other.

SEE ALSO:

When all the charts you want to align are selected, right click on any one of them and choose Size and Properties. This opens the Format Shape dialog, and the measurements that you enter in the dialog will be applied to all the selected charts. After making the charts the same size, go to the Drawing Tools tab and click on Format. Use the Align dropdown menu on the ribbon to align the selected charts and to distribute them evenly either horizontally or vertically.

Three tips and tricks to make Excel accounting more efficient

If you’re new to doing your accounting in Microsoft Excel, it can seem a bit daunting at first. After all, there are so many functions you can use to get the job done.
The good news is, once you master all those functions, you’ll be an accounting pro. You’ll be able to do your accounting faster and make it more accurate.
To help get you there, we’re revealing three Excel tips and tricks to get you closer to mastering your company’s accounting. All you have to do is read on and discover what they are…

Here are the three Excel tips and tricks to help turn you into an accounting pro

Tip 1: Here’s how to work in selected cells without cancelling your selection:

• To scroll down in the same column, press Enter;

• To scroll up in the same column, press Shift + Enter;

• To scroll horizontally in the same row (left to right), press Tab;

• To scroll horizontally in the same row (right to left), press Tab +Shift keys simultaneously;

• To increase the selection press Ctrl + (direction arrow key).

• To scroll through all the cells of a selection from top right in a clockwise direction, click Tab;

• To scroll through all the cells of a selection from bottom right in an anti-clockwise direction, click Shift + Tab.

Tip 2: Use these shortcuts to change the size of selected cells:  

• To insert adjacent cells to the right, hold Shift (or F8) and press the right arrow;

• To insert adjacent cells to the left, hold Shift (or F8) and press the left arrow;

• To insert cells above the existing selection, hold down Shift (or F8) and press the up arrow;

• To insert cells below the existing selection, hold down Shift (or F8) and press the down arrow.

Tip 3: Here’s how to introduce validate text

Select the cell you want to validate.

• On the ‘Data’ tab, in the ‘Data Tools’ group, click ‘Data Validation';

• In the ‘Data Validation’ dialog box, click the ‘Settings’ tab;

• Click on the ‘Custom’ box, then select ‘List’ from the drop-down menu; and

• In the ‘Formula’ field, type in = ISTEXT and type the (first cell in the selection). Then click ‘Okay’.

There you have it. Three simple tips that can help you use your Excel accounting faster and more effectively.

Use Excel to download hundreds of images instantly

The problem: Needing to download hundreds of images instantly

We got some 1000+ images on our web portal, and we need to download each of them to local drive so that we can move them to new server all together

The Solution:

We needed to download the entire list of images and get the file name of each image and associate it with the original post it was extracted from. Since I had promised to finish this whole project on Thursday afternoon and it was Friday at 9AM, I also needed this whole process to be fast. Since extracting images from an img src isn’t the kind of thing you need a browser to render code for, so instead of coding IE I set up VBA code that would make requests for images directly through Microsoft’s XML API.

The big picture was something like this:

For URL
  Make API Request for Image from URL
  Determine ImageName from URL
  Save Image to filestructure/ImageName
Next URL

I chose to program this in VBA (since the list I was given for URLs was in Excel) and using MSXML12 for VBA. The code (thanks to VBA-and-Excel.com for most of it) for a single extraction is:

Sub Save_image()
    Dim oHTTP As Object
    Dim sDestFolder As String
    Dim sSrcUrl As String
    Dim sImageFile As String
 
    sDestFolder = "C:\Users\adale\Desktop\Compendium Images\"
    sSrcUrl = ActiveCell.Value
    If Left(sSrcUrl, 2) = "//" Then
        sSrcUrl = "http:" & sSrcUrl
    End If
 
    sImageFile = Right(ActiveCell.Value, Len(ActiveCell.Value) - InStrRev(ActiveCell.Value, "/"))
    Debug.Print sImageFile
    ActiveCell.Offset(0, 2).Value = sImageFile
 
    Set oHTTP = CreateObject("msxml2.XMLHTTP")
    oHTTP.Open "GET", sSrcUrl, False
    oHTTP.send
 
    Set oStream = CreateObject("ADODB.Stream")
    Const adTypeBinary = 1
    Const adSaveCreateOverWrite = 2
    oStream.Type = adTypeBinary
    oStream.Open
 
    oStream.write oHTTP.responseBody
    oStream.savetofile sDestFolder & sImageFile, adSaveCreateOverWrite
 
    Set oStream = Nothing
    Set oHTTP = Nothing
End Sub 

Breakdown:

This code works by selecting a cell with an img URL in it (and an image name two columns to the right) and looking up that image and saving it to my hard drive. In addition to the saving script, there will be a sweeper script needed, but coding a sweeper script for excel is something I’ve covered extensively before.

Sub Save_image()

    Dim oHTTP As Object
    Dim sDestFolder As String
    Dim sSrcUrl As String
    Dim sImageFile As String
Standard object and variable definitons. oHTTP will become out XML API request object.
 sDestFolder = "C:\Users\adale\Desktop\Compendium Images\"
    sSrcUrl = ActiveCell.Value
    If Left(sSrcUrl, 2) = "//" Then
        sSrcUrl = "http:" & sSrcUrl
    End If
 
    sImageFile = Right(ActiveCell.Value, Len(ActiveCell.Value) - InStrRev(ActiveCell.Value, "/"))
    Debug.Print sImageFile
    ActiveCell.Offset(0, 2).Value = sImageFile 

This bit of code defines the save location for the images (“Compendium Images” folder on my Desktop) as well as extracting the file name from the URL and setting that name as the value two columns to the right.

Set oHTTP = CreateObject("msxml2.XMLHTTP")
    oHTTP.Open "GET", sSrcUrl, False
    oHTTP.send
 
    Set oStream = CreateObject("ADODB.Stream")
    Const adTypeBinary = 1
    Const adSaveCreateOverWrite = 2
    oStream.Type = adTypeBinary
    oStream.Open
 
    oStream.write oHTTP.responseBody
    oStream.savetofile sDestFolder & sImageFile, adSaveCreateOverWrite 

This is really the bulk of the code. In the first 3 lines VBA makes the request to the URL specified for the file it is looking for. The second chunk of code is initiating a place to store the API response, and the last two lines take the API response and write it to the specified file. If you are curious about the specific commands given in this section, Google can provide you with more details about the request, the parameters, and the outputs.

Besides the cleanup code at the bottom of the script, there really is nothing else to this code. While there are thousands of potential ways to utilize the MSXML packages, I found that to be a particularly helpful use of them. When all was said and done, I spend 20 minutes coding this and 5 minutes for it to make the request to 600 URLs and save the associated files instead of hours and hours copying and pasting and right clicking.